# Connectors java: JDBC

### Introducció

JDBC és una API de Java emprada per desenvolupar aplicacions que treballen amb bases de dades relacionals.

L'API JDBC té dues parts principalment:

* L’ ”application interface”, la interfície amb la qual s’interactua amb els programadors.
* Els JDBC drivers, els quals implementen la interfície.
* JDBC API es composa de dos paquets:
* java.sql
* javax.sql

### Arquitectures JDBC

De forma simple podem parlar de dos tipus d'arquitectures:

Two-Tier: l’aplicació java parla directament amb l’origen de dades (DBMS)


Three-Tier: les peticions són enviades a un middleware (middle tier server) i aquest al seu torn es comunica amb l’origen de dades (DBMS).


### Desenvolupant aplicacions amb JDBC

JDBC permet escriure aplicacions java les quals tenen les següents activitats:

* Part 1: Connexió a un origen de dades, com és el cas d’una base de dades.
* Part 2: Creació dels objectes statement i execució de les queries contra l’origen de dades (select, insert, update, …).
* Part 3: Recuperar (retrieve) i procesar els resultats (Resultset) rebuts des de l’origen de dades com a resposta a les queries fetes.


### Connector: establir connexió

El format de la URL JDBC és:

`jdbc:<drivertype>:[<username>/<password>]@<database_specificier>`

En el cas concret de mariadb, el valor del drivertype és: `<drivertype>=mariadb`

Alguns exemples:

* Exemple sense usuari i password, el driver és mysql i el database specifier és “localhost:3306/hr”:

`jdbc:mysql://localhost:3306/hr`

* Exemple amb usuari “juan”, connexió a un servidor qualsevol i base de dades “hr” amb driver oracle:

`jdbc:oracle:juan@servidor:8080:hr`

* Exemple de connexió contra base de dades Microsoft SQL Server:

`jdbc:odbc:DSN_nomBBDD`

### CreateStatement. Execució de la query

Un cop ja disposem de l'objecte de connexió (a partir del getConnection) utilitzarem el mètode createStatement() que pertany a la interface java.sql.Connection. Aquest mètode retorna un objecte Statement.

Fixeu-vos en aquest esquelet:

try(Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query)) {
…
} catch (SQLException e) {
System.out.println(e);
}//end try catch

Identifiquem algunes parts:

* Connection con crea la connexió a la base de dades utilitzant l’url, usuari i password declarat a les variables url, user i password.
* Statement st pren la connexió i configura la interfície per tal de poder passar la instrucció a la base de dades.
* Result rs utilitza l’statement per passar la query a la base de dades i retornat el resultat d’aquesta query a la variable rs. Fixem-nos que el mètode executeQuery() ens tornarà un objecte resultset que contindrà emmagatzemada la informació de la sentència SQL.

### CRUD

* CRUD és l’acrònim de Create, Read, Update and Delete
* CRUD són el seguit d’operacions que puc fer modificant les instruccions sql.

Al repositori següent disposem d'un CRUD (incomplet) sobre la base de dades HR, el qual un cop l'observem proposarem una sèrie d'exercicis:

[CRUD JDBC HR](https://github.com/atalens1/M06-UF2-JDBC-HR.git)

Aquesta petita aplicació:
* Crea la base de dades, les taules i hi afegeix dades.
* Inserta nous empleats.
* Mostra tots els empleats.
* Mostra empleats per id.
* Mostra empleats en un rang de salari.

### Consultes preparades (prepared statement)

La classe preparedStatement és una subclasse d’Statement la qual permet passar arguments a sentències SQL precompilades

Un exemple el teniu al mètode següent que podreu trobar al codi del CRUD:

    public void ReadDepartamentsId(Connection connection, String TableName, int id) 
    throws ConnectException, SQLException {

        String query = "SELECT * FROM " + TableName + " WHERE department_id = ?";

        try (PreparedStatement prepstat = connection.prepareStatement(query)) {

            prepstat.setInt(1, id);
            ResultSet rset = prepstat.executeQuery();

            int colNum = getColumnNames(rset);

            //Si el nombre de columnes és >0 procedim a llegir i mostrar els registres
            if (colNum > 0) {

                recorrerRegistres(rset,colNum);

            }
        }
    }

Fixeu-vos que:

* Instanciem un objecte PreparedStatement a partir del mètode prepareStatement de l'objecte de connexió. Li passem com a paràmetre la query que volem executar, on aquells paràmetres que volem passar els identificarem amb un "?".
* Depenent del tipus del camp (enter, string, etc.) agafarem l'objecte PreparedStatement i establirem el valor del paràmetre fent el mètode set corresponent.
* A l'exemple, el camp del where "id" és de tipus enter, per tant el que fem `prepstat.setInt(1, id)` . L'1 és l'índex (útil per quan hi ha més paràmetres involucrats) i la id en aquest cas és el nom del paràmetre, en aquest cas un paràmetre que ens estan passant als arguments del mètode. És el valor per el qual es substituirà l’interrogant.

### Exercicis breus:

1. Completeu el CRUD anterior per incorporar la modificació i l'esborrament d'un empleat.
2. Per què l'ús de preparedStatement és útil per prevenir atacs de SQL injection.

### Transaccions: commit i rollback

Recordeu que en el llenguatge SQL les transaccions permeten gestionar les operacions realitzades en una base de dades. Les transaccions es consideren unitàries. És a dir, les operacions que componen la transacció s’han d’executar totes o cap.

Per defecte, les connexions JDBC consideren que cada objecte Statement és en si mateix una transacció. Abans de cada execució es demana l’inici d’una transacció i al final, si l’execució té èxit, s’envia un commit i si no té èxit, un rollback. Per això diem que la connexió actua en mode autocommit.

Els Statements poden treballar sense automatitzar el commit després de cada execució. Caldrà canviar la connexió de mode. Per canviar-la, invocarem el mètode setAutoCommit passant-li per paràmetre el valor false.

Cal dir que no tots els statements necessiten activar o desactivar l'autocommit. Sols aquells que manipulin explícitament les dades de les taules com és el cas de l'INSERT, l'UPDATE o el DELETE. Per tant, per fer una SELECT no cal fer res ni considerar commit, rollback o l'autocommit.

En resum, quan vulguem desactivar l'autocommit, caldrà agafar l'objecte de connexió i aplicar el mètode **setAutoCommit()**. Per exemple, en el mètode d'inserció del nostre CRUD s'aplicaria així:

public void InsertEmployee(Connection connection, String TableName, Employees employee) 
    throws ConnectException, SQLException {

        String query = "INSERT INTO " + TableName 
                    + " (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_INT, HIRE_DATE,"
                    + "JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS)"
                    + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
                    
//Recuperem valor inicial de l'autocommit
        boolean autocommitvalue = connection.getAutoCommit();     

//En aquest punt desactivem el commit automàtic.
        connection.setAutoCommit(false);
                    
                try (PreparedStatement prepstat = connection.prepareStatement(query)) {

            prepstat.setInt(1, employee.getEmployeeId());
            prepstat.setString(2, employee.getFirstName());
            prepstat.setString(3, employee.getLastName());
            prepstat.setString(4, employee.getEmail());
            prepstat.setString(5, employee.getPhoneInt());
            prepstat.setString(6, employee.getHireDate());
            prepstat.setString(7, employee.getJobId());
            prepstat.setFloat(8, employee.getSalary());
            prepstat.setFloat(9, employee.getCommissionPct());
            prepstat.setInt(10, employee.getManagerId());
            prepstat.setInt(11, employee.getDepartmentId());
            prepstat.setString(12, employee.getBonus());

            prepstat.executeUpdate();
//Fem el commit            
            connection.commit();

            System.out.println("Empleat afegit amb èxit");
            
//deixem l'autocommit com estava
            connection.setAutoCommit(autocommitvalue);
            
            } catch (SQLException sqle) {
//fem rollback si no va be
                connection.rollback();
            }

}