Java è un ottimo linguaggio per tutte quelle applicazioni industriali in cui il programmatore si trova a fronteggiare compiti mondani, come l’inserimento in database di copiose masse di dati, una situazione in cui è una buona politica utilizzare le cosiddette preparerd statements. Cosa sono, quali sono i loro vantaggi e come fare a superare qualche piccolo ostacolo sintattico.
Parliamoci
Gli inserimenti in un database nel linguaggio Sql (Structured Query Language), avvengono attraverso istruzioni, come questa:
insert into dischi (titolo, artista, genere, anno, etichetta, registrazione)
values (‘Giant Steps’, ‘John Coltrane’, ‘Jazz’, 1960, ‘Atlantic’, ‘19591202′);
L’istruzione la riportiamo in minuscolo, perché la quasi totalità dei database non fa distinzione fra minuscolo e maiuscolo nella sintassi del linguaggio. Anche se, di solito, la letteratura mostra le istruzioni in maiuscolo, in questo articolo useremo preferibilmente il minuscolo, più compatto e meno assertivo.
Se vogliamo usare Sql in un programma Java, i passi sono molto semplici. In primo luogo si compongono i parametri della connessione al database, in una url che contiene il tipo di server, il suo indirizzo e il nome del database, più altri parametri specifici della connessione indispensabili per la scelta e la programmazione di un driver per il colloquio col database
String url = “jdbc:msql://192.168.1.1:1114/Demo”;
La url la usiamo per creare una connessione, aggiungendo anche le credenziali per il login, come user e password
Connection conn = DriverManager.getConnection(url,”user”,”password”);
Dopo avere ottenuto una connessione, possiamo creare uno statement, che ci servirà per inviare comandi al database
Statement st = conn.createStatement();
Ed ecco, infine, il modo più semplice di inserire dei dati in una tabella
st.executeUpdate(“INSERT INTO dischi ” +
“VALUES (‘Giant Steps’, ‘John Coltrane’, ‘Jazz’, 1960, ‘Atlantic’, ‘19591202′)”);
Inserire più di un record
Capita di inserire un record alla volta quando si esegue una transazione dal web, che spesso riguarda un singolo ordine o una singola richiesta, ma ci sono anche casi in cui dobbiamo importare un quantitativo di dati più cospicuo. In questi casi possiamo procedere in modi diversi. Nel caso più semplice, possiamo utilizzare il fatto che lo statement insert consente di specificare più di un record di dati alla volta, come nell’esempio che segue:
INSERT INTO USERS VALUES
(2, ‘Michael’, ‘Blythe’),
(3, ‘Linda’, ‘Mitchell’),
(4, ‘Jillian’, ‘Carson’),
(5, ‘Garrett’, ‘Vargas’);
Le diverse tuple di dati, racchiuse da parentesi e separate da virgole, daranno origine a quattro righe nel database, come mostrano questi esempi, in cui mostriamo il colloquio con sqlite, un database microscopico
sqlite> select * from users;
id|name|surname
2|Michael|Blythe
3|Linda|Mitchell
4|Jillian|Carson
5|Garrett|Vargas
sqlite> select id, name from users where id in (2,4);
id|name
2|Michael
4|Jillian
Dato che abbiamo nominato sqlite, chi desidera avere a disposizione un database per prove, con il minimo di impegno può scaricare il pacchetto completo dei tool, che pesa 1,5 Mbyte, cliccando qui. Sqlite tools comprende il programma che permette di compiere interrogazioni da riga di comando. La sintassi è semplicissima, diciamo che il nostro database si deve chiamare test.db, la sessione inizia con la riga di comando
sqlite3 test.db
Per smanettare con Java e Eclipse, possiamo seguire il tutorial su StackOverflow, raggiungibile dal link bit.ly/sqlite_eclipse. Le istruzioni sono brevi e semplicissime e c’è una classe di esempio, con cui possiamo essere in azione in pochi secondi. Non altrettanto semplici sono le istruzioni nel wiki di Eclipse, che sconsigliamo decisamente.
Inserire da programma
Se ci troviamo a inserire un grande numero di righe attraverso un programma, l’idea di creare una singola insert con un lungo corredo di values è folle: dovremmo assemblare una stringa grande come il complesso dei dati da inserire e caricare il database con l’interpretazione di un’istruzione molto pesante. Naturalmente, si tratta di un’idea presentata solo per amor di discussione.
L’approccio migliore è un ciclo, in cui confezioniamo stringhe da mandare al database, come in questo esempio:
int ids[] = { 1, 2, 3, 4};
String nomi[] = { “Marco”, “Matteo”, “Luca”, “Giovanni” };
for (int i = 0; i < ids.length; i++) {
statement.executeUpdate(“INSERT INTO person values(‘ ” + ids[i] + “‘, ‘” + names[i] + “‘)”);
}
Questo esempio è il modo più semplice di operare, ma non è il modo migliore, difatti non è adottato dalla maggioranza degli sviluppatori.
Il metodo preferito, prevede l’impiego di quello che si chiama uno statement preparato, in originale un prepared statement.
Prepared statements
I prepared statements sono istruzioni Sql precompilate, con segnaposto per inserire i parametri, per esempio
INSERT INTO person values( ?, ? );
Il nostro loop di inserimento, con un prepared statement avrebbe questo aspetto:
int ids[] = { 6, 7, 8, 9 };
String nomi[] = { “Marco”, “Matteo”, “Luca”, “Giovanni” };
for (int i = 0; i < ids.length; i++) {
statement.setInt(i + 1, ids[i]);
statement.setString(i + 1, nomi[i]);
statement.addBatch();
}
statement.executeBatch();
Di sicuro non ci abbiamo guadagnato in brevità , quindi dobbiamo dare una risposta alla legittima domanda su dove stia il vantaggio. E i vantaggi ci sono e di diverse specie.
In primo luogo, anche se Sql è un linguaggio semplice e facile da leggere, i database lo interpretano e lo traducono in interfacce applicative a livello più basso. Esiste anche il modo di operare direttamente a questo livello, risparmiando risorse di elaborazione, grazie al fatto che le istruzioni eseguite da un server sono conservate in cache e possono essere ripetute rapidamente.
Il colloquio fra client e server avviene con un protocollo binario più efficiente.
Quando si dialoga col database a un livello più basso, un ipotetico testo maligno iniettato nei campi non viene interpretato ed eseguito come se fosse un comando Sql. Così, ci mettiamo automaticamente al riparo dagli attacchi conosciuti come Sql Injection, ottenendo un requisito di sicurezza indispensabile per ogni applicazione esposta sul web.
Un prepared statement permette di maneggiare con più disinvoltura tipi ostici da utilizzare in Sql, come i blob.
Infine, una specializzazione di PreparedStatement, chiamata CallableStatement, permette di interfacciare in modo molto diretto stored procedure create sul database. Grazie a questa classe possiamo utilizzare al meglio la possibilità di esportare costrutti di livello più alto dal database, in un modo così lineare da utilizzare per il codice client, quasi come interfacce applicative locali.
Un difetto, però, c’è
Sì, se vi è parso di vedere un potenziale pericolo in quei segnaposto tutti uguali, rappresentati dai punti interrogativi, avete ragione: il pericolo c’è.
Se c’è da valorizzare una tabella con 36 colonne, mettere in fila i valori e contare i 36 punti interrogativi non è divertente.
Inoltre, se osserviamo il frammento di codice più sopra, osserviamo che la colonna su cui operare è identificata da un indice, un indice 1-based. Questo è semplice, ma può metterci in difficoltà . Per esempio
statement.setString(17, codiceFiscale)
potrebbe essere giusto o sbagliato, per assicurarcene, dobbiamo contare la lista dei parametri e trovare il diciassettesimo.
Infine, se aggiungiamo un parametro alla lista, dobbiamo rinumerare tutti gli inserimenti, a meno di aggiungere in fondo, ma il disordine compulsivo ci vieta di mettere un parametro in un posto diverso dall’ordine naturale nella tabella, così per perfezionismo e per non confondere chi legge il codice.
Di tanti altri problemi non ci dobbiamo preoccupare: l’ambiente applicativo si preoccupa per noi di verificare che tutti i parametri siano stati valorizzati, che non si sia tentato di valorizzare un parametro fuori range, che ogni parametro sia stato valorizzato con un tipo compatibile.
Vediamo come possiamo fare per indirizzare i parametri per nome, invece che per numero e avere uno statement con il numero giusto di punti interrogativi in modo sicuro.
Una soluzione
Per prima cosa, creiamo una classe astratta, per rappresentare la query con un prepared statement. Vogliamo che questa classe sia in grado di sintetizzare una stringa di interrogazione Sql con il numero corretto di parametri, vogliamo avere la possibilità di passare la lista di parametri in una variabile comoda, come una List e vogliamo avere un modo per trovare automaticamente l’indice di un parametro nella lista.
Cominciamo, quindi, a impostare il costruttore. Nel costruttore, ci aspettiamo di ricevere una connessione già aperta. Questo ci aiuta se vogliamo fare inserimenti su più tabelle in sequenza.
public abstract class AbstractAdder {
protected Connection connection;
public AbstractAdder(Connection connection) {
super();
this.connection = connection;
}
}
Naturalmente, fra le variabili di classe, ci mettiamo un logger, perché conviene sempre avere un log di errori da esaminare quando qualcosa va storto. Ci serve, ovviamente una variabile privata, per conservare la connessione.
Inoltre, ci serve una List, per conservare l’elenco dei parametri, e una variabile privata per ospitare il testo della query sintetizzata.
Per sintetizzare la query, non ci basta l’elenco dei parametri, occorre anche il nome della tabella.
Di conseguenza, il prologo della nostra classe è questo:
public abstract class AbstractAdder {
protected static Logger logger = Logger.getLogger(“it.pcprof.dbsample”);
protected Connection connection;
protected String SQL;
protected String tableName;
protected PreparedStatement pstmt;
protected List<String> fields;
}
Ora, immaginiamo un metodo per sintetizzare la query e preparare lo statement. La query si forma con l’istruzione Sql “insert into”, seguita dal nome della tabella, da un elenco di colonne, separate da virgole, dalla parola chiave “fields” e da una lista di punti interrogativi in numero corrispondente al numero delle colonne.
Creare una stringa concatenando il contenuto di una lista e interpolando un separatore, è semplicissimo: basta usare il metodo statico String.join(separatore, lista), quindi nel nostro caso
String.join(“, “, fields)
Ora, se la lista di argomenti è un dato, la lista di punti interrogativi deve essere sintetizzata. Per fortuna, anche questo è semplice in Java: possiamo ricorrere al metodo statico nCopies della classe Collections, che costruisce una lista con il numero richiesto di copie di un dato oggetto. Quindi, creiamo una lista di punti interrogativi, nel numero giusto, come segue
Collections.nCopies(fields.size(), “?”)
e li concateniamo separandoli con virgole in modo simile a prima
String.join(“, “, Collections.nCopies(fields.size(), “?”))
A questo punto possiamo mostrare il codice completo della funzione che crea la stringa Sql e lo statement preparato
public void prepare() throws SQLException {
// prepareStatement();
SQL = “insert into ” + tableName + ” ( ” + String.join(“, “, fields) + ” ) values ( “
+ String.join(“, “, Collections.nCopies(fields.size(), “?”)) + ” )”;
logger.info(SQL);
pstmt = connection.prepareStatement(SQL);
}
Adesso, vogliamo trasformare il codice che imposta i parametri da qualcosa come
statement.setString(5, order.getProductID());
a un formato più leggibile, come
statement.setString(fieldNo(“ProductID”), order.getProductID());
assumendo di avere un oggetto order che contiene l’informazione da inserire in database.
Per convertire il nome di una colonna in un indice numerico, ci serviamo di indexOf, un metodo della List.
Se indexOf ritorna -1, vuol dire che abbiamo cercato qualcosa che non esiste nella lista, quindi ci conviene segnalarlo immediatamente, con una exception, diversamente restituiamo l’indice, dopo averlo corretto ribasandolo a uno. Ecco il codice completo
public int fieldNo(String fieldName) throws IllegalArgumentException {
int index = fields.indexOf(fieldName);
if (index >= 0)
return index + 1;
else
throw new
IllegalArgumentException(“Field ” + fieldName + ” not present in query string”);
}
Nei listati, mostriamo come derivare da questa classe base una classe che inserisce una singola riga nel database. Naturalmente, se avessimo una collezione di dati, il blocco try catch e l’istruzione addBatch dovrebbero trovarsi in un loop, che non mostriamo per semplicità .