JDBC mit Groovy

1. Einleitung

In diesem Artikel wird erläutert, wie relationale Datenbanken mit JDBC mithilfe von idiomatischem Groovy abgefragt werden.

JDBC ist zwar relativ niedrig, bildet jedoch die Grundlage für die meisten ORMs und andere Datenzugriffsbibliotheken auf hoher Ebene in der JVM. Und wir können JDBC natürlich direkt in Groovy verwenden. Es hat jedoch eine ziemlich umständliche API.

Zum Glück baut die Groovy-Standardbibliothek auf JDBC auf und bietet eine saubere, einfache und dennoch leistungsstarke Benutzeroberfläche. Wir werden also das Groovy SQL-Modul untersuchen.

Wir werden JDBC in einfachem Groovy betrachten, ohne ein Framework wie Spring zu berücksichtigen, für das wir andere Anleitungen haben.

2. JDBC und Groovy Setup

Wir müssen das groovy- sql-Modul in unsere Abhängigkeiten aufnehmen:

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

Es ist nicht notwendig, es explizit aufzulisten, wenn wir groovy-all verwenden:

 org.codehaus.groovy groovy-all 2.4.13 

Wir können die neueste Version von groovy, groovy-sql und groovy-all auf Maven Central finden.

3. Herstellen einer Verbindung zur Datenbank

Das erste, was wir tun müssen, um mit der Datenbank zu arbeiten, ist eine Verbindung zu ihr herzustellen.

Lassen Sie uns die groovy.sql.Sql- Klasse vorstellen , die wir für alle Operationen in der Datenbank mit dem Groovy SQL-Modul verwenden.

Eine Instanz von SQL repräsentiert eine Datenbank, mit der wir arbeiten möchten.

Eine SQL- Instanz ist jedoch keine einzelne Datenbankverbindung . Wir werden später über Verbindungen sprechen, machen wir uns jetzt keine Sorgen um sie. Nehmen wir einfach an, dass alles magisch funktioniert.

3.1. Verbindungsparameter angeben

In diesem Artikel verwenden wir eine HSQL-Datenbank, eine einfache relationale Datenbank, die hauptsächlich in Tests verwendet wird.

Eine Datenbankverbindung benötigt eine URL, einen Treiber und Zugangsdaten:

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

Hier haben wir uns entschieden, diejenigen zu verwenden, die eine Karte verwenden , obwohl dies nicht die einzig mögliche Wahl ist.

Wir können dann eine Verbindung von der SQL- Klasse erhalten:

def sql = Sql.newInstance(dbConnParams)

In den folgenden Abschnitten erfahren Sie, wie Sie es verwenden.

Wenn wir fertig sind, sollten wir immer alle zugehörigen Ressourcen freigeben:

sql.close()

3.2. Verwenden einer DataSource

Insbesondere in Programmen, die auf einem Anwendungsserver ausgeführt werden, wird häufig eine Datenquelle verwendet, um eine Verbindung zur Datenbank herzustellen.

Wenn wir Verbindungen bündeln oder JNDI verwenden möchten, ist eine Datenquelle die natürlichste Option.

Die SQL- Klasse von Groovy akzeptiert Datenquellen einwandfrei:

def sql = Sql.newInstance(datasource)

3.3. Automatische Ressourcenverwaltung

Es ist mühsam, sich daran zu erinnern, close () aufzurufen, wenn wir mit einer SQL- Instanz fertig sind. Maschinen erinnern sich schließlich viel besser an Dinge als wir.

Mit Sql können wir unseren Code in einen Abschluss einschließen und Groovy automatisch close () aufrufen lassen, wenn die Steuerung ihn verlässt, auch in Ausnahmen:

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

4. Anweisungen gegen die Datenbank ausgeben

Jetzt können wir zu den interessanten Dingen übergehen.

Die einfachste und nicht spezialisierte Möglichkeit, eine Anweisung für die Datenbank auszugeben, ist die Ausführungsmethode :

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

Theoretisch funktioniert es sowohl für DDL / DML-Anweisungen als auch für Abfragen. Das obige einfache Formular bietet jedoch keine Möglichkeit, Abfrageergebnisse zurückzugewinnen. Wir werden Fragen für später hinterlassen.

Die Methode execute hat mehrere überladene Versionen, aber wir werden uns in späteren Abschnitten noch einmal mit den fortgeschritteneren Anwendungsfällen dieser und anderer Methoden befassen.

4.1. Daten einfügen

Zum Einfügen von Daten in kleinen Mengen und in einfachen Szenarien ist die zuvor beschriebene Ausführungsmethode vollkommen in Ordnung.

Für Fälle, in denen wir Spalten generiert haben (z. B. mit Sequenzen oder automatischem Inkrementieren) und die generierten Werte kennen möchten, gibt es eine dedizierte Methode: 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

In diesem Artikel haben wir uns mit dem Groovy SQL-Modul befasst und wie es JDBC mit Closures und Groovy-Strings erweitert und vereinfacht.

Wir können dann sicher schließen, dass schlichtes altes JDBC mit einer Prise Groovy ein bisschen moderner aussieht!

Wir haben nicht über jede einzelne Funktion von Groovy SQL gesprochen. Beispielsweise haben wir die Stapelverarbeitung, gespeicherte Prozeduren, Metadaten und andere Dinge weggelassen.

Weitere Informationen finden Sie in der Groovy-Dokumentation.

Die Implementierung all dieser Beispiele und Codefragmente finden Sie im GitHub-Projekt - dies ist ein Maven-Projekt, daher sollte es einfach zu importieren und auszuführen sein.