Una guida al wrapper JDBC sql2o

1. Introduzione

In questo tutorial, daremo uno sguardo a Sql2o, una piccola e veloce libreria per l'accesso al database relazionale in Java idiomatico.

Vale la pena ricordare che anche se Sql2o funziona mappando i risultati delle query su POJO (semplici vecchi oggetti Java), non è una soluzione ORM completa come Hibernate.

2. Installazione di Sql2o

Sql2o è un singolo file jar che possiamo aggiungere facilmente alle dipendenze del nostro progetto:

 org.sql2o sql2o 1.6.0 

Useremo anche HSQL, il database incorporato, nei nostri esempi; per poterlo seguire, possiamo includerlo anche:

 org.hsqldb hsqldb 2.4.0 test 

Maven Central ospita l'ultima versione di sql2o e HSQLDB.

3. Connessione al database

Per stabilire una connessione, partiamo da un'istanza della classe Sql2o :

Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");

Qui stiamo specificando l'URL di connessione, il nome utente e la password come parametri del costruttore.

L' oggetto Sql2o è thread-safe e possiamo condividerlo attraverso l'applicazione.

3.1. Utilizzando un DataSource

Nella maggior parte delle applicazioni, vorremo utilizzare un DataSource invece di una connessione DriverManager non elaborata , forse per sfruttare un pool di connessioni o per specificare parametri di connessione aggiuntivi. Non preoccuparti, Sql2o ci copre:

Sql2o sql2o = new Sql2o(datasource);

3.2. Lavorare con le connessioni

La semplice istanza di un oggetto Sql2o non stabilisce alcuna connessione al database.

Invece, usiamo il metodo open per ottenere un oggetto Connection (nota che non è una connessione JDBC ). Poiché Connection è AutoCloseable, possiamo racchiuderlo in un blocco try-with-resources:

try (Connection connection = sql2o.open()) { // use the connection }

4. Inserimento e aggiornamento delle dichiarazioni

Ora creiamo un database e inseriamo alcuni dati. Durante il tutorial, useremo una semplice tabella chiamata project:

connection.createQuery( "create table project " + "(id integer identity, name varchar(50), url varchar(100))").executeUpdate();

executeUpdate restituisce l' oggetto Connection in modo da poter concatenare più chiamate. Quindi, se vogliamo conoscere il numero di righe interessate, usiamo getResult:

assertEquals(0, connection.getResult());

Applicheremo il modello che abbiamo appena visto - createQuery ed executeUpdate - per tutte le istruzioni DDL, INSERT e UPDATE.

4.1. Ottenere valori chiave generati

In alcuni casi, tuttavia, potremmo voler recuperare i valori chiave generati. Questi sono i valori delle colonne chiave che vengono calcolati automaticamente (come quando si utilizza l'incremento automatico su determinati database).

Lo facciamo in due fasi. Innanzitutto, con un parametro aggiuntivo per createQuery:

Query query = connection.createQuery( "insert into project (name, url) " + "values ('tutorials', 'github.com/eugenp/tutorials')", true);

Quindi, invocando getKey sulla connessione:

assertEquals(0, query.executeUpdate().getKey());

Se le chiavi sono più di una, usiamo invece getKeys , che restituisce un array:

assertEquals(1, query.executeUpdate().getKeys()[0]);

5. Estrazione dei dati dal database

Andiamo ora al nocciolo della questione: le query SELECT e la mappatura dei set di risultati agli oggetti Java.

Per prima cosa, dobbiamo definire una classe POJO con getter e setter per rappresentare la nostra tabella dei progetti:

public class Project { long id; private String name; private String url; //Standard getters and setters }

Quindi, come prima, scriveremo la nostra query:

Query query = connection.createQuery("select * from project order by id");

Tuttavia, questa volta useremo un nuovo metodo, executeAndFetch:

List list = query.executeAndFetch(Project.class);

Come possiamo vedere, il metodo prende come parametro la classe dei risultati, a cui Sql2o mapperà le righe del set di risultati non elaborati provenienti dal database.

5.1. Mappatura delle colonne

Sql2o mappa le colonne alle proprietà JavaBean in base al nome, senza distinzione tra maiuscole e minuscole.

Tuttavia, le convenzioni di denominazione differiscono tra Java e database relazionali. Supponiamo di aggiungere una proprietà della data di creazione ai nostri progetti:

public class Project { long id; private String name; private String url; private Date creationDate; //Standard getters and setters }

In the database schema, most probably we'll call the same property creation_date.

Of course, we can alias it in our queries:

Query query = connection.createQuery( "select name, url, creation_date as creationDate from project");

However, it's tedious and we lose the possibility to use select *.

Another option is to instruct Sql2o to map creation_date to creationDate. That is, we can tell the query about the mapping:

connection.createQuery("select * from project") .addColumnMapping("creation_date", "creationDate");

This is nice if we use creationDate sparingly, in a handful of queries; however, when used extensively in a larger project, it becomes tedious and error-prone to tell the same fact over and over.

Fortunately, we can also specify mappings globally:

Map mappings = new HashMap(); mappings.put("CREATION_DATE", "creationDate"); sql2o.setDefaultColumnMappings(mappings);

Of course, this will cause every instance of creation_date to be mapped to creationDate, so that's another reason for striving to keep names consistent across the definitions of our data.

5.2. Scalar Results

Sometimes, we want to extract a single scalar result from a query. For example, when we need to count the number of records.

In those cases, defining a class and iterating over a list that we know to contain a single element is overkill. Thus, Sql2o includes the executeScalar method:

Query query = connection.createQuery( "select count(*) from project"); assertEquals(2, query.executeScalar(Integer.class));

Here, we're specifying the return type to be Integer. However, that's optional and we can let the underlying JDBC driver decide.

5.3. Complex Results

Sometimes instead, complex queries (such as for reporting) may not easily map onto a Java object. We might also decide that we don't want to code a Java class to use only in a single query.

Thus, Sql2o also allows a lower-level, dynamic mapping to tabular data structures. We get access to that using the executeAndFetchTable method:

Query query = connection.createQuery( "select * from project order by id"); Table table = query.executeAndFetchTable();

Then, we can extract a list of maps:

List list = table.asList(); assertEquals("tutorials", list.get(0).get("name"));

Alternatively, we can map the data onto a list of Row objects, that are mappings from column names to values, akin to ResultSets:

List rows = table.rows(); assertEquals("tutorials", rows.get(0).getString("name"));

6. Binding Query Parameters

Many SQL queries have a fixed structure with a few parameterized portions. We might naively write those partially dynamic queries with string concatenation.

However, Sql2o allows parameterized queries, so that:

  • We avoid SQL injection attacks
  • We allow the database to cache often-used queries and gain in performance
  • Finally, we are spared from the need to encode complex types such as dates and times

So, we can use named parameters with Sql2o to achieve all of the above. We introduce parameters with a colon and we bind them with the addParameter method:

Query query = connection.createQuery( "insert into project (name, url) values (:name, :url)") .addParameter("name", "REST with Spring") .addParameter("url", "github.com/eugenp/REST-With-Spring"); assertEquals(1, query.executeUpdate().getResult());

6.1. Binding From a POJO

Sql2o offers an alternative way of binding parameters: that is, by using POJOs as the source. This technique is particularly suitable when a query has many parameters and they all refer to the same entity. So, let's introduce the bind method:

Project project = new Project(); project.setName("REST with Spring"); project.setUrl("github.com/eugenp/REST-With-Spring"); connection.createQuery( "insert into project (name, url) values (:name, :url)") .bind(project) .executeUpdate(); assertEquals(1, connection.getResult());

7. Transactions and Batch Queries

With a transaction, we can issue multiple SQL statements as a single operation that is atomic. That is, either it succeeds or it fails in bulk, with no intermediate results. In fact, transactions are one of the key features of relational databases.

In order to open a transaction, we use the beginTransaction method instead of the open method that we've used so far:

try (Connection connection = sql2o.beginTransaction()) { // here, the transaction is active }

When execution leaves the block, Sql2o automatically rolls back the transaction if it's still active.

7.1. Manual Commit and Rollback

However, we can explicitly commit or rollback the transaction with the appropriate methods:

try (Connection connection = sql2o.beginTransaction()) { boolean transactionSuccessful = false; // perform some operations if(transactionSuccessful) { connection.commit(); } else { connection.rollback(); } }

Note that both commit and rollback end the transaction. Subsequent statements will run without a transaction, thus they won't be automatically rolled back at the end of the block.

However, we can commit or rollback the transaction without ending it:

try (Connection connection = sql2o.beginTransaction()) { List list = connection.createQuery("select * from project") .executeAndFetchTable() .asList(); assertEquals(0, list.size()); // insert or update some data connection.rollback(false); // perform some other insert or update queries } // implicit rollback try (Connection connection = sql2o.beginTransaction()) { List list = connection.createQuery("select * from project") .executeAndFetchTable() .asList(); assertEquals(0, list.size()); }

7.2. Batch Operations

When we need to issue the same statement many times with different parameters, running them in a batch provides a great performance benefit.

Fortunately, by combining two of the techniques that we've described so far – parameterized queries and transactions – it's easy enough to run them in batch:

  • First, we create the query only once
  • Then, we bind the parameters and call addToBatch for each instance of the query
  • Finally, we call executeBatch:
try (Connection connection = sql2o.beginTransaction()) { Query query = connection.createQuery( "insert into project (name, url) " + "values (:name, :url)"); for (int i = 0; i < 1000; i++) { query.addParameter("name", "tutorials" + i); query.addParameter("url", "//github.com/eugenp/tutorials" + i); query.addToBatch(); } query.executeBatch(); connection.commit(); } try (Connection connection = sql2o.beginTransaction()) { assertEquals( 1000L, connection.createQuery("select count(*) from project").executeScalar()); }

7.3. Lazy Fetch

Conversely, when a single query returns a great number of results, converting them all and storing them in a list is heavy on memory.

So, Sql2o supports a lazy mode, where rows are returned and mapped one at a time:

Query query = connection.createQuery("select * from project"); try (ResultSetIterable projects = query.executeAndFetchLazy(Project.class)) { for(Project p : projects) { // do something with the project } }

Note that ResultSetIterable is AutoCloseable and is meant to be used with try-with-resources to close the underlying ResultSet when finished.

8. Conclusions

In this tutorial, we've presented an overview of the Sql2o library and its most common usage patterns. Further information can be found in the Sql20 wiki on GitHub.

Inoltre, l'implementazione di tutti questi esempi e frammenti di codice può essere trovata nel progetto GitHub, che è un progetto Maven, quindi dovrebbe essere facile da importare ed eseguire così com'è.