JDBC avec Groovy

1. Introduction

Dans cet article, nous allons voir comment interroger des bases de données relationnelles avec JDBC, à l'aide de Groovy idiomatique.

JDBC, bien que de niveau relativement bas, est la base de la plupart des ORM et autres bibliothèques d'accès aux données de haut niveau sur la JVM. Et nous pouvons utiliser JDBC directement dans Groovy, bien sûr; cependant, il a une API plutôt encombrante.

Heureusement pour nous, la bibliothèque standard Groovy s'appuie sur JDBC pour présenter une interface propre, simple mais puissante. Nous allons donc explorer le module Groovy SQL.

Nous allons examiner JDBC en clair Groovy, sans considérer aucun framework tel que Spring, pour lequel nous avons d'autres guides.

2. Configuration JDBC et Groovy

Nous devons inclure le module groovy- sql parmi nos dépendances:

 org.codehaus.groovy groovy 2.4.13   org.codehaus.groovy groovy-sql 2.4.13 

Il n'est pas nécessaire de le lister explicitement si nous utilisons groovy-all:

 org.codehaus.groovy groovy-all 2.4.13 

Nous pouvons trouver la dernière version de groovy, groovy-sql et groovy-all sur Maven Central.

3. Connexion à la base de données

La première chose que nous devons faire pour travailler avec la base de données est de s'y connecter.

Introduisons la classe groovy.sql.Sql , que nous utiliserons pour toutes les opérations sur la base de données avec le module Groovy SQL.

Une instance de Sql représente une base de données sur laquelle nous voulons opérer.

Cependant, une instance de Sql n'est pas une connexion de base de données unique . Nous parlerons des connexions plus tard, ne nous en préoccupons pas maintenant; supposons simplement que tout fonctionne comme par magie.

3.1. Spécification des paramètres de connexion

Tout au long de cet article, nous allons utiliser une base de données HSQL, qui est une base de données relationnelle légère qui est principalement utilisée dans les tests.

Une connexion à une base de données nécessite une URL, un pilote et des informations d'identification d'accès:

Map dbConnParams = [ url: 'jdbc:hsqldb:mem:testDB', user: 'sa', password: '', driver: 'org.hsqldb.jdbc.JDBCDriver']

Ici, nous avons choisi de spécifier ceux qui utilisent une carte , bien que ce ne soit pas le seul choix possible.

On peut alors obtenir une connexion depuis la classe Sql :

def sql = Sql.newInstance(dbConnParams)

Nous verrons comment l'utiliser dans les sections suivantes.

Lorsque nous avons terminé, nous devrions toujours libérer les ressources associées:

sql.close()

3.2. Utilisation d'un DataSource

Il est courant, en particulier dans les programmes exécutés à l'intérieur d'un serveur d'applications, d'utiliser une source de données pour se connecter à la base de données.

De plus, lorsque nous voulons regrouper des connexions ou utiliser JNDI, une source de données est l'option la plus naturelle.

La classe Sql de Groovy accepte très bien les sources de données:

def sql = Sql.newInstance(datasource)

3.3. Gestion automatique des ressources

Se souvenir d'appeler close () lorsque nous en avons terminé avec une instance Sql est fastidieux; les machines se souviennent des choses beaucoup mieux que nous, après tout.

Avec Sql, nous pouvons envelopper notre code dans une fermeture et faire en sorte que Groovy appelle automatiquement close () lorsque le contrôle le quitte, même en cas d'exceptions:

Sql.withInstance(dbConnParams) { Sql sql -> haveFunWith(sql) }

4. Émission de déclarations sur la base de données

Maintenant, nous pouvons passer aux choses intéressantes.

Le moyen le plus simple et non spécialisé d'émettre une instruction sur la base de données est la méthode execute :

sql.execute "create table PROJECT (id integer not null, name varchar(50), url varchar(100))"

In theory it works both for DDL/DML statements and for queries; however, the simple form above does not offer a way to get back query results. We'll leave queries for later.

The execute method has several overloaded versions, but, again, we'll look at the more advanced use cases of this and other methods in later sections.

4.1. Inserting Data

For inserting data in small amounts and in simple scenarios, the execute method discussed earlier is perfectly fine.

However, for cases when we have generated columns (e.g., with sequences or auto-increment) and we want to know the generated values, a dedicated method exists: executeInsert.

As for execute, we'll now look at the most simple method overload available, leaving more complex variants for a later section.

So, suppose we have a table with an auto-increment primary key (identity in HSQLDB parlance):

sql.execute "create table PROJECT (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))"

Let's insert a row in the table and save the result in a variable:

def ids = sql.executeInsert """ INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials') """

executeInsert behaves exactly like execute, but what does it return?

It turns out that the return value is a matrix: its rows are the inserted rows (remember that a single statement can cause multiple rows to be inserted) and its columns are the generated values.

It sounds complicated, but in our case, which is by far the most common one, there is a single row and a single generated value:

assertEquals(0, ids[0][0])

A subsequent insertion would return a generated value of 1:

ids = sql.executeInsert """ INSERT INTO PROJECT (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring') """ assertEquals(1, ids[0][0])

4.2. Updating and Deleting Data

Similarly, a dedicated method for data modification and deletion exists: executeUpdate.

Again, this differs from execute only in its return value, and we'll only look at its simplest form.

The return value, in this case, is an integer, the number of affected rows:

def count = sql.executeUpdate("UPDATE PROJECT SET URL = '//' + URL") assertEquals(2, count)

5. Querying the Database

Things start getting Groovy when we query the database.

Dealing with the JDBC ResultSet class is not exactly fun. Luckily for us, Groovy offers a nice abstraction over all of that.

5.1. Iterating Over Query Results

While loops are so old style… we're all into closures nowadays.

And Groovy is here to suit our tastes:

sql.eachRow("SELECT * FROM PROJECT") { GroovyResultSet rs -> haveFunWith(rs) }

The eachRow method issues our query against the database and calls a closure over each row.

As we can see, a row is represented by an instance of GroovyResultSet, which is an extension of plain old ResultSet with a few added goodies. Read on to find more about it.

5.2. Accessing Result Sets

In addition to all of the ResultSet methods, GroovyResultSet offers a few convenient utilities.

Mainly, it exposes named properties matching column names:

sql.eachRow("SELECT * FROM PROJECT") { rs -> assertNotNull(rs.name) assertNotNull(rs.URL) }

Note how property names are case-insensitive.

GroovyResultSet also offers access to columns using a zero-based index:

sql.eachRow("SELECT * FROM PROJECT") { rs -> assertNotNull(rs[0]) assertNotNull(rs[1]) assertNotNull(rs[2]) }

5.3. Pagination

We can easily page the results, i.e., load only a subset starting from some offset up to some maximum number of rows. This is a common concern in web applications, for example.

eachRow and related methods have overloads accepting an offset and a maximum number of returned rows:

def offset = 1 def maxResults = 1 def rows = sql.rows('SELECT * FROM PROJECT ORDER BY NAME', offset, maxResults) assertEquals(1, rows.size()) assertEquals('REST with Spring', rows[0].name)

Here, the rows method returns a list of rows rather than iterating over them like eachRow.

6. Parameterized Queries and Statements

More often than not, queries and statements are not fully fixed at compile time; they usually have a static part and a dynamic part, in the form of parameters.

If you're thinking about string concatenation, stop now and go read about SQL injection!

We mentioned earlier that the methods that we've seen in previous sections have many overloads for various scenarios.

Let's introduce those overloads that deal with parameters in SQL queries and statements.

6.1. Strings With Placeholders

In style similar to plain JDBC, we can use positional parameters:

sql.execute( 'INSERT INTO PROJECT (NAME, URL) VALUES (?, ?)', 'tutorials', 'github.com/eugenp/tutorials')

or we can use named parameters with a map:

sql.execute( 'INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)', [name: 'REST with Spring', url: 'github.com/eugenp/REST-With-Spring'])

This works for execute, executeUpdate, rows and eachRow. executeInsert supports parameters, too, but its signature is a little bit different and trickier.

6.2. Groovy Strings

We can also opt for a Groovier style using GStrings with placeholders.

All the methods we've seen don't substitute placeholders in GStrings the usual way; rather, they insert them as JDBC parameters, ensuring the SQL syntax is correctly preserved, with no need to quote or escape anything and thus no risk of injection.

This is perfectly fine, safe and Groovy:

def name = 'REST with Spring' def url = 'github.com/eugenp/REST-With-Spring' sql.execute "INSERT INTO PROJECT (NAME, URL) VALUES (${name}, ${url})"

7. Transactions and Connections

So far we've skipped over a very important concern: transactions.

In fact, we haven't talked at all about how Groovy's Sql manages connections, either.

7.1. Short-Lived Connections

In the examples presented so far, each and every query or statement was sent to the database using a new, dedicated connection. Sql closes the connection as soon as the operation terminates.

Of course, if we're using a connection pool, the impact on performance might be small.

Still, if we want to issue multiple DML statements and queries as a single, atomic operation, we need a transaction.

Also, for a transaction to be possible in the first place, we need a connection that spans multiple statements and queries.

7.2. Transactions With a Cached Connection

Groovy SQL does not allow us to create or access transactions explicitly.

Instead, we use the withTransaction method with a closure:

sql.withTransaction { sql.execute """ INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials') """ sql.execute """ INSERT INTO PROJECT (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring') """ }

Inside the closure, a single database connection is used for all queries and statements.

Furthermore, the transaction is automatically committed when the closure terminates, unless it exits early due to an exception.

However, we can also manually commit or rollback the current transaction with methods in the Sql class:

sql.withTransaction { sql.execute """ INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials') """ sql.commit() sql.execute """ INSERT INTO PROJECT (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring') """ sql.rollback() }

7.3. Cached Connections Without a Transaction

Finally, to reuse a database connection without the transaction semantics described above, we use cacheConnection:

sql.cacheConnection { sql.execute """ INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials') """ throw new Exception('This does not roll back') }

8. Conclusions and Further Reading

Dans cet article, nous avons examiné le module Groovy SQL et comment il améliore et simplifie JDBC avec des fermetures et des chaînes Groovy.

Nous pouvons alors conclure en toute sécurité que l'ancien JDBC a l'air un peu plus moderne avec une pincée de Groovy!

Nous n'avons pas parlé de toutes les fonctionnalités de Groovy SQL; par exemple, nous avons omis le traitement par lots, les procédures stockées, les métadonnées et d'autres choses.

Pour plus d'informations, consultez la documentation Groovy.

L'implémentation de tous ces exemples et extraits de code se trouve dans le projet GitHub - il s'agit d'un projet Maven, il devrait donc être facile à importer et à exécuter tel quel.