1. Statements and CRUD operations

En aquesta secció estudiarem l'objectiu principal de treballar amb bases de dades: manipular dades. Sempre treballarem amb la mateixa plantilla:

1. Connectar a la base de dades.
2. Preparar la consulta.
3. Executar la consulta.
4. Processament de les dades, si és necessari.

El punt 1 s'ha estudiat a les seccions anteriors.

Per crear les consultes, haurem d'utilitzar les següents classes/interfaces:

• Statement \(\rightarrow\) Es fa servir de manera general i és útil quan volem realitzar sentències SQL estàtiques, ja que no accepta paràmetres. Creem un Statement amb el mètode createStatement() de la classe Connection.
• PreparedStatement \(\rightarrow\) Es fa servir quan volem llançar diverses consultes i, a més, es permet realitzar sentències dinàmiques. Creem un PreparedStatement amb el mètode prepareStatement() de la classe Connection.
• CallableStatement \(\rightarrow\) Es fa servir per accedir a procediments emmagatzemats a la base de dades i també accepta paràmetres d'entrada. Creem un CallableStatement amb el mètode prepareCall() de la classe Connection.

Quan la sentència està preparada, la podem executar amb:

• executeQuery \(\rightarrow\) Executa sentències que esperem que retornin dades (consultes SELECT). El valor de retorn d'aquesta execució és, com ja suposareu, un ResultSet.
• executeUpdate \(\rightarrow\) Executa sentències que no s'espera que retornin dades, però que s'utilitzaran per modificar la base de dades connectada (consultes INSERT, DELETE, UPDATE i CREATE TABLE).

1.1. Create (Insert)

Anem a veure un exemple senzill de declaració INSERT.

ConnexioDB conDB=new ConnexioDB("BDJocs");
Connection con=conDB.getConnexio();

String SQL="INSERT INTO Joc VALUES (1, 'Double Dragon', 'Dos germans bessons experts en arts marcials s`han de fer camí en un escenari urbà on  membres de bandes rivals volen deixar-los fora de combat.', 1);";

Statement st=con.createStatement();

int affectedRows=st.executeUpdate(SQL);

System.out.println(affectedRows+ "row has been inserted");

Com podeu veure, és molt fàcil d'entendre el codi. Creem una declaració buida i després executem l'SQL "per se". L'execució retorna el nombre de files inserides. Veurem millors maneres d'inserir dades, utilitzant scripts.

Info

Aquest codi és una versió reduïda, ja que quan treballem amb bases de dades poden aparèixer SQLException. Suposem que el procés de connexió ja s'ha realitzat.

1.2. Read (Select)

La lectura és el procés més important que farem, ja que només amb el procés d'inici de sessió dins d'una aplicació estem recuperant informació d'una base de dades. En tots els casos, hem d'escriure la sentència (SQL), executar-la i finalment processar les dades retornades. Depenent de com preparem la sentència, podem distingir entre:

1. Sentències fixes
2. Sentències variables
3. Sentències preparades

Veurem amb detall ambdues maneres. Utilitzarem per això l'script Instituto.sql i la base de dades.

1.2.1. Sentències fixes

Aquestes sentències, com el seu nom indica, són sentències fixes o constants. L'SQL és fixa i no té cap variable.

// The query
String SQL="Select * from Persona";
// The statement
Statement st=con.createStatement();
// The execution
ResultSet rst=st.executeQuery(SQL);

// processing
while(rst.next()){
    System.out.print(ConsoleColors.BLUE_BRIGHT+ "Person: "+ ConsoleColors.RESET);
    /*
    System.out.println(
            rst.getString(3)+ ", "+
            rst.getString(2)+ " "+
            rst.getInt(4));
    */
    System.out.println(
            rst.getString("apellidos")+ ", "+
            rst.getString("nombre")+ " "+
            rst.getInt("edad"));
}

rst.close();

En el processament de la informació, ResultSet té:

• type getType(int columnIndex) \(\rightarrow\) mètode sobrecarregat que retorna el tipus de dades donat, utilitzant l'índex de columna del ResultSet. Recorda que la primera columna és 1 en comptes de 0. El tipus pot ser Int, String, Double, etc. si coneixes el tipus. Per a columnes desconegudes, pots utilitzar Object com a tipus genèric.
• type getType(String columnName) $\rightarrow` mateix que el mètode anterior, però accedint a la columna amb el nom que hem seleccionat a la consulta o el nom a la taula.

1.2.2. Sentències variables

Imagina que vols recuperar noms que continguen la cadena Ma dins seu.

String SQL="Select * from Persona where nombre like '%Ma%'";

En aquest cas, aquesta consulta està codificada directament, i si vols canviar la part del text, has de modificar el teu codi. Per evitar la codificació directa, podem escriure:

ConnexioDB conDB=new ConnexioDB("Instituto");

Connection con=conDB.getConnexio();

// hardcoded String
// String SQL="Select * from Persona where nombre like '%Ma%'";

String nombre=Utilitats.leerTextoC("Give me part of the name: ");
// The query
String SQL="Select * from Persona where nombre like '%" + nombre + "%'";
// The statement
Statement st=con.createStatement();
// The execution
ResultSet rst=st.executeQuery(SQL);

// processing
while(rst.next()){
    System.out.print(ConsoleColors.BLUE_BRIGHT+ "People with " +nombre+": "+ ConsoleColors.RESET);
    System.out.println(
            rst.getString("apellidos")+ ", "+
            rst.getString("nombre")+ " "+
            rst.getInt("edad"));
}

rst.close();

Com podem veure, les dades ara estan en variables, però la construcció de l'SQL és més complexa. Cal tenir en compte que els textos han d'estar entre cometes i els números no, el que facilita cometre errors. Però pot ser pitjor, aquest tipus de codi pot incórrer en problemes d'injecció SQL, com veiem a l'exemple següent:

String idPersona=Read.readText("Tell me the id to consult: ");
String SQL = "Select * from Person where idPersona="+idPersona;

• Si l'usuari introdueix 4 \(\rightarrow\) Es mostrarà la persona amb ID igual a 4
• Si l'usuari introdueix 4 o 1=1 \(\rightarrow\) Es mostraran totes les persones

Cal evitar aquest tipus de consultes en les declaracions de validació d'usuari, per a les quals farem servir les sentències preparades i, òbviament, ser molt cautelosos en la verificació de les entrades.

1.2.3. Sentències preparades

Per evitar el problema de l'injecció SQL, sempre que tinguem paràmetres a la nostra consulta, farem servir sentències preparades. En les sentències preparades, on hem de fer servir un marcador, en lloc de compondre-la amb concatenacions dins de la cadena, l'indicarem amb un interrogant (?), un caràcter anomenat placeholder.

A continuació, haurem d'assignar valors a aquests placeholders, utilitzant els mètodes setType(int pos) on Type és el tipus de dades que assignarem i pos és la posició del placeholder, començant per 1. Veurem l'exemple:

ConnexioDB conDB=new ConnexioDB("Instituto");

Connection con=conDB.getConnexio();

String ID=Utilitats.leerTextoC("Give me an id: ");
// The query
String SQL="Select * from Persona where idPersona = ?" ;
// The statement
PreparedStatement pst=con.prepareStatement(SQL);
// fill placeholders

pst.setString(1, ID);

// The execution
ResultSet rst=pst.executeQuery();

// processing
while(rst.next()){
    System.out.print(ConsoleColors.BLUE_BRIGHT+ "People with " +ID+": "+ ConsoleColors.RESET);
    System.out.println(
            rst.getString("apellidos")+ ", "+
            rst.getString("nombre")+ " "+
            rst.getInt("edad"));
}

rst.close();

Consell

Pots combinar ResultSet amb ResultSetMetaData per obtenir els noms de les columnes i els tipus de dades emmagatzemats a la base de dades. Trobaràs un exemple a la plataforma.

1.3. Actualització (Update) i Esborrat (Delete)

L'actualització i l'esborrat d'una fila es consideren com a actualitzacions de la base de dades, ja que modifiquen l'estat de la base de dades. De fet, també considerem la inserció com una actualització de la base de dades. Totes dues tasques s'incloent en el mètode executeUpdate(). Anem a estudiar-ho a través d'exemples:

1.3.1. Exemple d'esborrat

Anem a esborrar files d'una taula de persones entre les edats donades:

ConnexioDB conDB = new ConnexioDB("Instituto");
Connection con = conDB.getConnexio();

// give the age's bounds
int minAge = Utilitats.leerEnteroC("Give me minimal age: ");
int maxAge = Utilitats.leerEnteroC("Give me maximun age: ");

// The query
String SQL = "Delete from Persona where edad between ? and ?";

// The statement
PreparedStatement pst = con.prepareStatement(SQL);

// fill placeholders            
pst.setInt(1, minAge);
pst.setInt(2, maxAge);

// show the query after resolve placeholders
System.out.println(pst);

// The execution
int deletedtedRows = pst.executeUpdate();

// how many roas affecte
System.out.println(deletedtedRows + " has been deleted.");

Perill

Eliminar dades és una operació molt delicada. Tingues cura de:

• No oblidis la clàusula WHERE quan elimines, ja que eliminaràs totes les dades de la taula.
• Si vols eliminar tota la taula, incloent l'estructura (definició + taula), has d'utilitzar DROP TABLE en compte de DELETE.
• Si intentes eliminar una fila que està referenciada per una clau externa, obtindràs l'excepció SQLIntegrityConstraintViolationException i un missatge com No es pot eliminar o actualitzar una fila pare: falla una restricció de clau externa.

1.3.2. Exemple d'Actualització

Anem a afegir anys a les persones amb l'ID donat:

ConnexioDB conDB = new ConnexioDB("Instituto");

Connection con = conDB.getConnexio();

// give the age's bounds
int difAge = Utilitats.leerEnteroC("Give me number oy years: ");
int idMin = Utilitats.leerEnteroC("Give me minimum id: ");

// The query
String SQL = "Update Persona set edad=edad+ ? where idPersona > ?";

// The statement
PreparedStatement pst = con.prepareStatement(SQL);

// fill placeholders            
pst.setInt(1, difAge);
pst.setInt(2, idMin);

System.out.println(pst);
// The execution
int updatedRows = pst.executeUpdate();

System.out.println(updatedRows + " has been updated.");

Warning

Recorda que:

• La clàusula UPDATE no necessita FROM perquè indiquem directament el nom de la taula.
• Apareix la clàusula SET, amb parells camp=valor_actualitzat, per assignar el nou valor als camps seleccionats.
• Si oblidem la clàusula FROM, s'actualitzaran totes les files.

1.4. Scripts

Un script, que normalment s'ha creat en un fitxer extern, és un conjunt d'instruccions SQL executades en ordre de dalt a baix. Podem prendre com a estratègia llegir el fitxer línia per línia i executar-lo una per una, però JDBC permet executar un conjunt d'instruccions en bloc. Per fer això, el primer que hem de fer és habilitar múltiples execucions afegint un paràmetre a la connexió, que és allowMultiQueries=true.

A continuació, hem de carregar el fitxer i compondre una cadena amb tot el script. Per normalitzar-lo i fer-lo totalment portàtil, hem de tenir cura amb els salts de línia, ja que depenent del sistema és una combinació \n o \r\n. Podem llegir línia per línia i guardar-ho en un StringBuilder, afegint System.getProperty("line.separator") com a separadors.

Després només necessitarem crear una declaració amb aquesta cadena i executar-la amb executeUpdate(). Ho veurem a continuació:

ConnexioDB conDB = new ConnexioDB("Instituto");

Connection con = conDB.getConnexio();

File script = new File("sql/EsquemaCine.sql");

BufferedReader bfr = bfr = new BufferedReader(new FileReader(script));

String line = null;
StringBuilder sb = new StringBuilder();

// Obtenemos el salto de linea del sistema subyacente
String breakLine = System.getProperty("line.separator");

while ((line = bfr.readLine()) != null) {
    sb.append(line);
    sb.append(breakLine);
}

String query = sb.toString();   // generemos el Script en un String
Statement stm = con.createStatement();
int result = stm.executeUpdate(query);
System.out.println("Script ejecutado con salida " + result);

Consell

Recordes les classes Files i Paths? Reescriu l'últim exemple per obtenir un codi més net.

1.5. Transaccions

Si volem protegir la integritat de les dades, així com evitar situacions de bloqueig inesperades en aplicacions multithread, hem de protegir les nostres operacions, especialment aquelles que modifiquen les dades mitjançant l'ús de transaccions.

Una transacció defineix un entorn d'execució en què les operacions de desament es mantenen emmagatzemades a la memòria fins que la transacció es completi. Si en un moment determinat alguna cosa falla, l'estat es torna al punt inicial de la mateixa (punt inicial) o a algun punt de marca intermedi. Per defecte, obrir una connexió inicia una transacció:

• Cada execució a la connexió genera una transacció per si mateixa.
• Si volem desactivar aquesta opció perquè la transacció abasti diverses execucions, hem de marcar-ho mitjançant Connection.setAutoCommit(false);.
• Per acceptar definitivament la transacció ho farem mitjançant Connection.commit();
• Per cancel·lar la transacció Connection.rollback();

1.6. ResultSets actualitzables

Els mètodes de treball revisats a les seccions anteriors, especialment quan s'actualitzen o esborren files, funcionen directament a la base de dades. Això significa que esborreu o actualitzeu una fila sense carregar-la prèviament a la memòria. Què passa si voleu mostrar les dades a l'usuari i després l'usuari decideix esborrar o actualitzar la fila? En aquest cas, és millor utilitzar ResultSets actualitzables.

Hem estudiat ResultSet com una col·lecció de files i l'hem utilitzat només per a la lectura. També podem utilitzar-lo per editar i esborrar dades. Per fer-ho, hem de obrir el ResultSet d'una manera diferent de la que hem vist fins ara. I, per últim, però no menys important, dependrà de la base de dades la disponibilitat de crear aquest tipus de ResultSets.

Per aconseguir-ho, no depèn del ResultSet ni de com es va crear l'Statement. Hem d'utilitzar aquest constructor:

public abstract Statement createStatement(
    int arg0,   // resultSetType           
    int arg1,   // resultSetConcurrency
    int arg2)   // resultSetHoldability
    throws SQLException

Com podeu veure, hi ha 3 arguments per indicar quin tipus de ResultSet donem al final. Aquests tres arguments poden ser:

• resultSetType \(\rightarrow\) aquestes són opcions sobre com moure's i passar per les files del ResultSet:
  • TYPE_FORWARD_ONLY \(\rightarrow\) opció per defecte. El ResultSet només es pot passar una vegada.
  • TYPE_SCROLL_INSENSITIVE \(\rightarrow\) Permet rebobinar i saltar a una posició absoluta o relativa.
  • TYPE_SCROLL_SENSITIVE \(\rightarrow\) Igual que abans, però permet veure els canvis realitzats a la base de dades.
• ResultSetConcurrency \(\rightarrow\) aquestes són opcions sobre si es pot actualitzar el ResultSet o no:
  • CONCUR_READ_ONLY \(\rightarrow\) opció per defecte. Només lectura. Si volem actualitzar alguna cosa, només podem utilitzar les clàusules DELETE o UPDATE.
  • CONCUR_UPDATABLE \(\rightarrow\) Les files del ResultSet es poden actualitzar o esborrar.
• ResultSetHoldability aquestes opcions són sobre el comportament en tancar el ResultSet:
  • HOLD_CURSORS_OVER_COMMIT \(\rightarrow\) El ResultSet es manté obert després de confirmar la transacció.
  • CLOSE_CURSORS_AT_COMMIT \(\rightarrow\) El ResultSet es tanca després de confirmar la transacció. Millora el rendiment.

En el següent exemple, podem preguntar al nostre DBMS si aquests tipus de ResultSet estan suportats:

ConnexioDB conDB = new ConnexioDB("Instituto");
Connection con = conDB.getConnexio();
DatabaseMetaData dbmd = con.getMetaData();

System.out.println("TYPE_FORWARD_ONLY: " + dbmd.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
System.out.println("TYPE_SCROLL_INSENSITIVE: " + dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
System.out.println("TYPE_SCROLL_SENSITIVE: " + dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
System.out.println("CONCUR_READ_ONLY: " + dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
System.out.println("CONCUR_UPDATABLE: " + dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));

1.6.1. Moviment del ResultSet

Com podem veure, no només podem moure el cursor cap endavant:

• next, previous, first i last \(\rightarrow\) com el seu nom indica, es mouen cap endavant, cap enrere, al principi i a l'última fila. Retorna true si el cursor apunta a una fila i false en cas contrari.
• beforeFirst i afterLast \(\rightarrow\) el cursor apuntarà fora de les files, abans de la primera o després de l'última fila.
• relative(int n) \(\rightarrow\) el cursor es mou n files (cap endavant o cap enrere, depenent del signe).
• absolute(int n) \(\rightarrow\) el cursor apunta a la fila n.

1.6.2. Eliminació de files en un ResultSet

Després de col·locar el cursor a la fila que es vol eliminar, es pot eliminar del ResultSet (i de la base de dades) amb el mètode deleteRow(). Quan s'elimina una fila, el cursor apuntarà a la fila anterior a la que s'ha eliminat.

1.6.3. Actualització de files en un ResultSet

Després de col·locar el cursor a la fila desitjada, s'ha de:

1. Actualitzar les columnes desitjades, utilitzant el mètode updateType(int column, type newValue), on la columna i-èssima (o amb el seu nom) s'assigna el nou valor del tipus donat.
2. Un cop s'hagin modificat totes les columnes desitjades, es guarden els canvis amb updateRow().

Consell

No podem actualitzar una fila sencera, hem de actualitzar columna per columna i després actualitzar la fila.

1.6.4. Inserció de files en un ResultSet

Si volem inserir una nova fila en un ResultSet, hem de:

1. Generar una nova fila buida, i això s'aconsegueix amb el mètode moveToInsertRow().
2. En aquesta fila, apliquem el mètode updateType(int column, type newValue) a totes les columnes a les quals volem assignar un valor, i finalment
3. Procedim a inserir la nova fila amb insertRow().

Important

• Aquestes operacions d'actualització, eliminació i inserció només es poden realitzar en consultes que provenen d'una taula sense agrupacions.
• Per evitar complexitat en els nostres programes, val la pena avaluar la conveniència de traduir les actualitzacions del ResultSet a SQL pur i executar-les directament a les bases de dades mitjançant noves sentències.