JDBC с Groovy

1. Въведение

В тази статия ще разгледаме как да търсим релационни бази данни с JDBC, използвайки идиоматичния Groovy.

JDBC, макар и относително ниско ниво, е основата на повечето ORM и други библиотеки за достъп до данни на високо ниво в JVM. И можем да използваме JDBC директно в Groovy, разбира се; той обаче има доста тромав API.

За наше щастие, стандартната библиотека Groovy се основава на JDBC, за да представи интерфейс, който е изчистен, опростен, но същевременно мощен. И така, ще проучим Groovy SQL модула.

Ще разгледаме JDBC в обикновения Groovy, без да разглеждаме каквато и да е рамка като Spring, за която имаме други ръководства.

2. Настройка на JDBC и Groovy

Трябва да включим модула groovy- sql сред нашите зависимости:

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

Не е необходимо да го посочвате изрично, ако използваме groovy-all:

 org.codehaus.groovy groovy-all 2.4.13 

Можем да намерим най-новата версия на groovy, groovy-sql и groovy-all в Maven Central.

3. Свързване с базата данни

Първото нещо, което трябва да направим, за да работим с базата данни, е да се свържем с нея.

Нека представим класа groovy.sql.Sql , който ще използваме за всички операции в базата данни с модула Groovy SQL.

Екземпляр на Sql представлява база данни, върху която искаме да работим.

Въпреки това, като пример за Sql не е единичен връзка с базата данни . Ще говорим за връзките по-късно, нека не се тревожим за тях сега; нека просто приемем, че всичко магически работи.

3.1. Задаване на параметри на свързване

В тази статия ще използваме база данни HSQL, която е лека релационна DB, която се използва най-вече при тестове.

Връзката с база данни се нуждае от URL, драйвер и идентификационни данни за достъп:

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

Тук избрахме да посочим тези, които използват карта , въпреки че това не е единственият възможен избор.

След това можем да получим връзка от класа Sql :

def sql = Sql.newInstance(dbConnParams)

Ще видим как да го използваме в следващите раздели.

Когато приключим, винаги трябва да пускаме всички свързани ресурси:

sql.close()

3.2. Използване на DataSource

Обикновено, особено при програми, работещи в сървъра на приложения, се използва източник на данни за свързване с базата данни.

Също така, когато искаме да обединим връзки или да използваме JNDI, източникът на данни е най-естественият вариант.

Класът на Sql на Groovy отлично приема източници на данни:

def sql = Sql.newInstance(datasource)

3.3. Автоматично управление на ресурсите

Спомнянето да извикаме close (), когато приключим с екземпляр на Sql е досадно; машини в крайна сметка запомнят нещата много по-добре от нас.

С Sql можем да увием кода си в затваряне и да имаме автоматично повикване Groovy close (), когато контролът го напусне, дори в случай на изключения:

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

4. Издаване на изявления срещу базата данни

Сега можем да преминем към интересните неща.

Най-простият и неспециализиран начин за издаване на изявление срещу базата данни е методът за изпълнение :

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

На теория работи както за DDL / DML изрази, така и за заявки; обаче простият формуляр по-горе не предлага начин за връщане на резултатите от заявката. Ще оставим заявките за по-късно.

Методът за изпълнение има няколко претоварени версии, но отново ще разгледаме по-напредналите случаи на използване на този и други методи в следващите раздели.

4.1. Вмъкване на данни

За вмъкване на данни в малки количества и в прости сценарии, методът за изпълнение , обсъден по-рано, е напълно добре.

Въпреки това, за случаите, когато сме генерирали колони (например с последователности или автоматично увеличаване) и искаме да знаем генерираните стойности, съществува специален метод: executeInsert .

Що се отнася до изпълнението , сега ще разгледаме най-простия метод за претоварване, оставяйки по-сложни варианти за по-късен раздел.

И така, да предположим, че имаме таблица с първичен ключ с автоматично увеличаване (идентичност на езика HSQLDB):

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

Нека да вмъкнем ред в таблицата и да запишем резултата в променлива:

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

executeInsert се държи точно като изпълнение , но какво връща?

Оказва се, че връщаната стойност е матрица: нейните редове са вмъкнатите редове (не забравяйте, че един оператор може да доведе до вмъкване на множество редове), а неговите колони са генерираните стойности.

Звучи сложно, но в нашия случай, който е най-често срещаният, има един ред и една генерирана стойност:

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

Последващо вмъкване ще върне генерирана стойност 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. Актуализиране и изтриване на данни

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

В тази статия разгледахме модула Groovy SQL и как той подобрява и опростява JDBC със затваряния и низове на Groovy.

След това можем спокойно да заключим, че обикновеният стар JDBC изглежда малко по-модерен с поръсване на Groovy!

Не сме говорили за всяка една функция на Groovy SQL; например, пропуснахме групова обработка, съхранени процедури, метаданни и други неща.

За допълнителна информация вижте документацията на Groovy.

Внедряването на всички тези примери и кодови фрагменти може да се намери в проекта GitHub - това е проект на Maven, така че трябва да е лесно да се импортира и да се изпълнява както е.