Una guida a Apache Commons DbUtils

1. Panoramica

Apache Commons DbUtils è una piccola libreria che rende molto più semplice lavorare con JDBC.

In questo articolo, implementeremo esempi per mostrare le sue caratteristiche e capacità.

2. Configurazione

2.1. Dipendenze di Maven

Per prima cosa, dobbiamo aggiungere le dipendenze commons-dbutils e h2 al nostro pom.xml :

 commons-dbutils commons-dbutils 1.6   com.h2database h2 1.4.196 

È possibile trovare l'ultima versione di commons-dbutils e h2 su Maven Central.

2.2. Database di test

Con le nostre dipendenze in atto, creiamo uno script per creare le tabelle e i record che useremo:

CREATE TABLE employee( id int NOT NULL PRIMARY KEY auto_increment, firstname varchar(255), lastname varchar(255), salary double, hireddate date, ); CREATE TABLE email( id int NOT NULL PRIMARY KEY auto_increment, employeeid int, address varchar(255) ); INSERT INTO employee (firstname,lastname,salary,hireddate) VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy')); // ... INSERT INTO email (employeeid,address) VALUES (1, '[email protected]'); // ...

Tutti i casi di test di esempio in questo articolo utilizzeranno una connessione appena creata a un database in memoria H2:

public class DbUtilsUnitTest { private Connection connection; @Before public void setupDB() throws Exception { Class.forName("org.h2.Driver"); String db = "jdbc:h2:mem:;INIT=runscript from 'classpath:/employees.sql'"; connection = DriverManager.getConnection(db); } @After public void closeBD() { DbUtils.closeQuietly(connection); } // ... }

2.3. POJO

Infine, avremo bisogno di due semplici classi:

public class Employee { private Integer id; private String firstName; private String lastName; private Double salary; private Date hiredDate; // standard constructors, getters, and setters } public class Email { private Integer id; private Integer employeeId; private String address; // standard constructors, getters, and setters }

3. Introduzione

La libreria DbUtils fornisce la classe QueryRunner come punto di ingresso principale per la maggior parte delle funzionalità disponibili.

Questa classe funziona ricevendo una connessione al database, un'istruzione SQL da eseguire e un elenco facoltativo di parametri per fornire valori per i segnaposto della query.

Come vedremo in seguito, alcuni metodi ricevono anche un'implementazione di ResultSetHandler , che è responsabile della trasformazione delle istanze di ResultSet negli oggetti che la nostra applicazione si aspetta.

Ovviamente, la libreria fornisce già diverse implementazioni che gestiscono le trasformazioni più comuni, come elenchi, mappe e JavaBeans.

4. Query sui dati

Ora che conosciamo le basi, siamo pronti per interrogare il nostro database.

Cominciamo con un rapido esempio di come ottenere tutti i record nel database come un elenco di mappe utilizzando un MapListHandler :

@Test public void givenResultHandler_whenExecutingQuery_thenExpectedList() throws SQLException { MapListHandler beanListHandler = new MapListHandler(); QueryRunner runner = new QueryRunner(); List list = runner.query(connection, "SELECT * FROM employee", beanListHandler); assertEquals(list.size(), 5); assertEquals(list.get(0).get("firstname"), "John"); assertEquals(list.get(4).get("firstname"), "Christian"); }

Di seguito, ecco un esempio che utilizza un BeanListHandler per trasformare i risultati in istanze Employee :

@Test public void givenResultHandler_whenExecutingQuery_thenEmployeeList() throws SQLException { BeanListHandler beanListHandler = new BeanListHandler(Employee.class); QueryRunner runner = new QueryRunner(); List employeeList = runner.query(connection, "SELECT * FROM employee", beanListHandler); assertEquals(employeeList.size(), 5); assertEquals(employeeList.get(0).getFirstName(), "John"); assertEquals(employeeList.get(4).getFirstName(), "Christian"); }

Per le query che restituiscono un singolo valore, possiamo utilizzare ScalarHandler :

@Test public void givenResultHandler_whenExecutingQuery_thenExpectedScalar() throws SQLException { ScalarHandler scalarHandler = new ScalarHandler(); QueryRunner runner = new QueryRunner(); String query = "SELECT COUNT(*) FROM employee"; long count = runner.query(connection, query, scalarHandler); assertEquals(count, 5); }

Per conoscere tutte le implementazioni di ResultSerHandler , è possibile fare riferimento alla documentazione di ResultSetHandler .

4.1. Gestori personalizzati

Possiamo anche creare un gestore personalizzato da passare ai metodi di QueryRunner quando abbiamo bisogno di un maggiore controllo su come i risultati verranno trasformati in oggetti.

Questo può essere fatto implementando l' interfaccia ResultSetHandler o estendendo una delle implementazioni esistenti fornite dalla libreria.

Vediamo come appare il secondo approccio. Innanzitutto, aggiungiamo un altro campo alla nostra classe Employee :

public class Employee { private List emails; // ... }

Ora, creiamo una classe che estende il tipo BeanListHandler e imposta l'elenco di posta elettronica per ogni dipendente:

public class EmployeeHandler extends BeanListHandler { private Connection connection; public EmployeeHandler(Connection con) { super(Employee.class); this.connection = con; } @Override public List handle(ResultSet rs) throws SQLException { List employees = super.handle(rs); QueryRunner runner = new QueryRunner(); BeanListHandler handler = new BeanListHandler(Email.class); String query = "SELECT * FROM email WHERE employeeid = ?"; for (Employee employee : employees) { List emails = runner.query(connection, query, handler, employee.getId()); employee.setEmails(emails); } return employees; } }

Si noti che ci aspettiamo un oggetto Connection nel costruttore in modo da poter eseguire le query per ottenere le e-mail.

Infine, testiamo il nostro codice per vedere se tutto funziona come previsto:

@Test public void givenResultHandler_whenExecutingQuery_thenEmailsSetted() throws SQLException { EmployeeHandler employeeHandler = new EmployeeHandler(connection); QueryRunner runner = new QueryRunner(); List employees = runner.query(connection, "SELECT * FROM employee", employeeHandler); assertEquals(employees.get(0).getEmails().size(), 2); assertEquals(employees.get(2).getEmails().size(), 3); }

4.2. Processori di riga personalizzati

Nei nostri esempi, i nomi delle colonne della tabella dei dipendenti corrispondono ai nomi dei campi della nostra classe Employee (la corrispondenza non fa distinzione tra maiuscole e minuscole). Tuttavia, non è sempre così, ad esempio quando i nomi delle colonne utilizzano trattini bassi per separare le parole composte.

In queste situazioni, possiamo sfruttare l' interfaccia RowProcessor e le sue implementazioni per mappare i nomi delle colonne ai campi appropriati nelle nostre classi.

Vediamo come appare. Per prima cosa, creiamo un'altra tabella e inseriamo alcuni record:

CREATE TABLE employee_legacy ( id int NOT NULL PRIMARY KEY auto_increment, first_name varchar(255), last_name varchar(255), salary double, hired_date date, ); INSERT INTO employee_legacy (first_name,last_name,salary,hired_date) VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy')); // ...

Ora, modifichiamo la nostra classe EmployeeHandler :

public class EmployeeHandler extends BeanListHandler { // ... public EmployeeHandler(Connection con) { super(Employee.class, new BasicRowProcessor(new BeanProcessor(getColumnsToFieldsMap()))); // ... } public static Map getColumnsToFieldsMap() { Map columnsToFieldsMap = new HashMap(); columnsToFieldsMap.put("FIRST_NAME", "firstName"); columnsToFieldsMap.put("LAST_NAME", "lastName"); columnsToFieldsMap.put("HIRED_DATE", "hiredDate"); return columnsToFieldsMap; } // ... }

Si noti che stiamo utilizzando un BeanProcessor per eseguire la mappatura effettiva delle colonne sui campi e solo per quelli che devono essere indirizzati.

Finally, let's test everything is ok:

@Test public void givenResultHandler_whenExecutingQuery_thenAllPropertiesSetted() throws SQLException { EmployeeHandler employeeHandler = new EmployeeHandler(connection); QueryRunner runner = new QueryRunner(); String query = "SELECT * FROM employee_legacy"; List employees = runner.query(connection, query, employeeHandler); assertEquals((int) employees.get(0).getId(), 1); assertEquals(employees.get(0).getFirstName(), "John"); }

5. Inserting Records

The QueryRunner class provides two approaches to creating records in a database.

The first one is to use the update() method and pass the SQL statement and an optional list of replacement parameters. The method returns the number of inserted records:

@Test public void whenInserting_thenInserted() throws SQLException { QueryRunner runner = new QueryRunner(); String insertSQL = "INSERT INTO employee (firstname,lastname,salary, hireddate) " + "VALUES (?, ?, ?, ?)"; int numRowsInserted = runner.update( connection, insertSQL, "Leia", "Kane", 60000.60, new Date()); assertEquals(numRowsInserted, 1); }

The second one is to use the insert() method that, in addition to the SQL statement and replacement parameters, needs a ResultSetHandler to transform the resulting auto-generated keys. The return value will be what the handler returns:

@Test public void givenHandler_whenInserting_thenExpectedId() throws SQLException { ScalarHandler scalarHandler = new ScalarHandler(); QueryRunner runner = new QueryRunner(); String insertSQL = "INSERT INTO employee (firstname,lastname,salary, hireddate) " + "VALUES (?, ?, ?, ?)"; int newId = runner.insert( connection, insertSQL, scalarHandler, "Jenny", "Medici", 60000.60, new Date()); assertEquals(newId, 6); }

6. Updating and Deleting

The update() method of the QueryRunner class can also be used to modify and erase records from our database.

Its usage is trivial. Here's an example of how to update an employee's salary:

@Test public void givenSalary_whenUpdating_thenUpdated() throws SQLException { double salary = 35000; QueryRunner runner = new QueryRunner(); String updateSQL = "UPDATE employee SET salary = salary * 1.1 WHERE salary <= ?"; int numRowsUpdated = runner.update(connection, updateSQL, salary); assertEquals(numRowsUpdated, 3); }

And here's another to delete an employee with the given id:

@Test public void whenDeletingRecord_thenDeleted() throws SQLException { QueryRunner runner = new QueryRunner(); String deleteSQL = "DELETE FROM employee WHERE id = ?"; int numRowsDeleted = runner.update(connection, deleteSQL, 3); assertEquals(numRowsDeleted, 1); }

7. Asynchronous Operations

DbUtils provides the AsyncQueryRunner class to execute operations asynchronously. The methods on this class have a correspondence with those of QueryRunner class, except that they return a Future instance.

Here's an example to obtain all employees in the database, waiting up to 10 seconds to get the results:

@Test public void givenAsyncRunner_whenExecutingQuery_thenExpectedList() throws Exception { AsyncQueryRunner runner = new AsyncQueryRunner(Executors.newCachedThreadPool()); EmployeeHandler employeeHandler = new EmployeeHandler(connection); String query = "SELECT * FROM employee"; Future
    
      future = runner.query(connection, query, employeeHandler); List employeeList = future.get(10, TimeUnit.SECONDS); assertEquals(employeeList.size(), 5); }
    

8. Conclusion

In this tutorial, we explored the most notable features of the Apache Commons DbUtils library.

Abbiamo interrogato i dati e li abbiamo trasformati in diversi tipi di oggetti, inserito record ottenendo le chiavi primarie generate e aggiornato ed eliminato i dati in base a un determinato criterio. Abbiamo anche sfruttato la classe AsyncQueryRunner per eseguire in modo asincrono un'operazione di query.

E, come sempre, il codice sorgente completo di questo articolo può essere trovato su Github.