1. Введение
В этой статье мы рассмотрим, как выполнять запросы к реляционным базам данных с помощью JDBC , используя идиоматический Groovy.
JDBC, хотя и является относительно низкоуровневым, является основой большинства ORM и других высокоуровневых библиотек доступа к данным на JVM. И мы, конечно, можем использовать JDBC непосредственно в Groovy; однако у него довольно громоздкий API.
К счастью для нас, стандартная библиотека Groovy основана на JDBC и представляет собой чистый, простой, но мощный интерфейс. Итак, мы будем изучать модуль Groovy SQL.
Мы рассмотрим JDBC в простом Groovy, не принимая во внимание какой-либо фреймворк, такой как Spring, для которого у нас есть другие руководства .
2. Настройка JDBC и Groovy
Мы должны включить модуль groovy - sql в число наших зависимостей:
<dependency>
<groupId>org.codehaus.groovy</groupId>
<artifactId>groovy</artifactId>
<version>2.4.13</version>
</dependency>
<dependency>
<groupId>org.codehaus.groovy</groupId>
<artifactId>groovy-sql</artifactId>
<version>2.4.13</version>
</dependency>
Нет необходимости указывать его явно, если мы используем groovy-all:
<dependency>
<groupId>org.codehaus.groovy</groupId>
<artifactId>groovy-all</artifactId>
<version>2.4.13</version>
</dependency>
Мы можем найти последнюю версию groovy , groovy-sql и groovy-all на Maven Central. ``
3. Подключение к базе данных
Первое, что нам нужно сделать для работы с базой данных, это подключиться к ней.
Давайте представим класс groovy.sql.Sql , который мы будем использовать для всех операций с базой данных с модулем Groovy SQL.
Экземпляр Sql представляет собой базу данных, с которой мы хотим работать.
Однако экземпляр Sql не является отдельным подключением к базе данных . Мы поговорим о соединениях позже, давайте не будем сейчас о них беспокоиться; давайте просто предположим, что все волшебным образом работает.
3.1. Указание параметров подключения
На протяжении всей этой статьи мы будем использовать базу данных HSQL, которая представляет собой облегченную реляционную базу данных, которая в основном используется в тестах.
Для подключения к базе данных требуется URL-адрес, драйвер и учетные данные для доступа:
Map dbConnParams = [
url: 'jdbc:hsqldb:mem:testDB',
user: 'sa',
password: '',
driver: 'org.hsqldb.jdbc.JDBCDriver']
Здесь мы решили указать тех, кто использует Map , хотя это не единственный возможный вариант.
Затем мы можем получить соединение из класса Sql :
def sql = Sql.newInstance(dbConnParams)
Мы увидим, как его использовать в следующих разделах.
Когда мы закончим, мы всегда должны освобождать любые связанные ресурсы:
sql.close()
3.2. Использование источника данных
Обычно, особенно в программах, работающих внутри сервера приложений, для подключения к базе данных используется источник данных.
Кроме того, когда мы хотим объединить соединения или использовать JNDI, источник данных является наиболее естественным вариантом.
Класс Groovy Sql отлично принимает источники данных:
def sql = Sql.newInstance(datasource)
3.3. Автоматическое управление ресурсами
Утомительно помнить о вызове close() , когда мы закончим с экземпляром Sql ; В конце концов, машины помнят вещи намного лучше, чем мы.
С помощью Sql мы можем обернуть наш код в замыкание и заставить Groovy автоматически вызывать close() , когда управление покидает его, даже в случае исключений:
Sql.withInstance(dbConnParams) {
Sql sql -> haveFunWith(sql)
}
4. Выдача заявлений по базе данных
Теперь можно переходить к интересному.
Самый простой и неспециализированный способ выполнить запрос к базе данных — это метод execute :
sql.execute "create table PROJECT (id integer not null, name varchar(50), url varchar(100))"
Теоретически это работает как для операторов DDL/DML, так и для запросов; однако приведенная выше простая форма не позволяет получить результаты запроса. Мы оставим вопросы на потом.
У метода execute есть несколько перегруженных версий, но, опять же, мы рассмотрим более сложные варианты использования этого и других методов в следующих разделах.
4.1. Вставка данных
Для вставки данных в небольших количествах и в простых сценариях рассмотренный ранее метод execute отлично подходит.
Однако для случаев, когда мы сгенерировали столбцы (например, с последовательностями или автоинкрементом) и хотим узнать сгенерированные значения, существует специальный метод: executeInsert .
Что касается execute , сейчас мы рассмотрим самую простую доступную перегрузку метода, оставив более сложные варианты для следующего раздела.
Итак, предположим, что у нас есть таблица с автоинкрементным первичным ключом (идентификацией на языке 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/foreach/tutorials')
"""
executeInsert ведет себя точно так же, как execute , но что он возвращает?
Оказывается, возвращаемое значение является матрицей: ее строки — это вставленные строки (помните, что один оператор может привести к вставке нескольких строк), а ее столбцы — это сгенерированные значения.
Звучит сложно, но в нашем случае, который является наиболее распространенным, есть одна строка и одно сгенерированное значение:
assertEquals(0, ids[0][0])
Последующая вставка вернет сгенерированное значение 1:
ids = sql.executeInsert """
INSERT INTO PROJECT (NAME, URL)
VALUES ('REST with Spring', 'github.com/foreach/REST-With-Spring')
"""
assertEquals(1, ids[0][0])
4.2. Обновление и удаление данных
Точно так же существует специальный метод для модификации и удаления данных: executeUpdate .
Опять же, это отличается от execute только возвращаемым значением, и мы рассмотрим только его простейшую форму.
Возвращаемое значение в этом случае представляет собой целое число, количество затронутых строк:
def count = sql.executeUpdate("UPDATE PROJECT SET URL = 'https://' + URL")
assertEquals(2, count)
5. Запрос к базе данных
Вещи начинают становиться Groovy, когда мы запрашиваем базу данных.
Работать с классом JDBC ResultSet не совсем весело. К счастью для нас, Groovy предлагает хорошую абстракцию всего этого.
5.1. Перебор результатов запроса
Хотя циклы — это такой старый стиль… в наши дни мы все пользуемся замыканиями.
И Groovy здесь, чтобы удовлетворить наши вкусы:
sql.eachRow("SELECT * FROM PROJECT") { GroovyResultSet rs ->
haveFunWith(rs)
}
Метод eachRow отправляет запрос к базе данных и вызывает замыкание для каждой строки.
Как мы видим, строка представлена экземпляром GroovyResultSet , который является расширением старого простого ResultSet с несколькими дополнительными преимуществами. Читайте дальше, чтобы узнать больше об этом.
5.2. Доступ к наборам результатов
В дополнение ко всем методам ResultSet GroovyResultSet предлагает несколько удобных утилит.
В основном, он предоставляет именованные свойства, соответствующие именам столбцов:
sql.eachRow("SELECT * FROM PROJECT") { rs ->
assertNotNull(rs.name)
assertNotNull(rs.URL)
}
Обратите внимание, что имена свойств нечувствительны к регистру.
GroovyResultSet также предлагает доступ к столбцам с использованием индекса с отсчетом от нуля:
sql.eachRow("SELECT * FROM PROJECT") { rs ->
assertNotNull(rs[0])
assertNotNull(rs[1])
assertNotNull(rs[2])
}
5.3. Пагинация
Мы можем легко развернуть результаты, т. е. загрузить только подмножество, начиная с некоторого смещения до некоторого максимального количества строк. Это распространенная проблема, например, в веб-приложениях.
eachRow и связанные методы имеют перегрузки, принимающие смещение и максимальное количество возвращаемых строк:
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)
Здесь метод rows возвращает список строк, а не перебирает их, как eachRow .
6. Параметризованные запросы и операторы
Чаще всего запросы и операторы не полностью фиксируются во время компиляции; они обычно имеют статическую часть и динамическую часть в виде параметров.
Если вы думаете о конкатенации строк, остановитесь сейчас и прочитайте о SQL-инъекциях!
Ранее мы упоминали, что методы, рассмотренные в предыдущих разделах, имеют множество перегрузок для различных сценариев.
Давайте представим те перегрузки, которые имеют дело с параметрами в запросах и операторах SQL.
6.1. Строки с заполнителями
В стиле простого JDBC мы можем использовать позиционные параметры:
sql.execute(
'INSERT INTO PROJECT (NAME, URL) VALUES (?, ?)',
'tutorials', 'github.com/foreach/tutorials')
или мы можем использовать именованные параметры с картой:
sql.execute(
'INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)',
[name: 'REST with Spring', url: 'github.com/foreach/REST-With-Spring'])
Это работает для execute , executeUpdate , rows и eachRow . executeInsert также поддерживает параметры, но его сигнатура немного отличается и сложнее.
6.2. Заводные строки
Мы также можем выбрать стиль Groovier, используя GStrings с заполнителями.
Все методы, которые мы видели, не заменяют заполнители в GString обычным способом; скорее, они вставляют их как параметры JDBC, обеспечивая правильное сохранение синтаксиса SQL, без необходимости заключать в кавычки или экранировать что-либо и, следовательно, без риска внедрения.
Это прекрасно, безопасно и Groovy:
def name = 'REST with Spring'
def url = 'github.com/foreach/REST-With-Spring'
sql.execute "INSERT INTO PROJECT (NAME, URL) VALUES (${name}, ${url})"
7. Транзакции и связи
До сих пор мы пропустили очень важную проблему: транзакции.
На самом деле, мы вообще не говорили о том, как Groovy Sql управляет соединениями.
7.1. Недолговечные связи
В представленных до сих пор примерах каждый запрос или оператор отправлялся в базу данных с использованием нового выделенного соединения. Sql закрывает соединение, как только операция завершается.
Конечно, если мы используем пул соединений, влияние на производительность может быть небольшим.
Тем не менее, если мы хотим выполнять несколько операторов и запросов DML как единую атомарную операцию , нам нужна транзакция.
Кроме того, чтобы транзакция была возможной, нам в первую очередь необходимо соединение, охватывающее несколько операторов и запросов.
7.2. Транзакции с кэшированным соединением
Groovy SQL не позволяет нам явно создавать или получать доступ к транзакциям.
Вместо этого мы используем метод withTransaction с замыканием:
sql.withTransaction {
sql.execute """
INSERT INTO PROJECT (NAME, URL)
VALUES ('tutorials', 'github.com/foreach/tutorials')
"""
sql.execute """
INSERT INTO PROJECT (NAME, URL)
VALUES ('REST with Spring', 'github.com/foreach/REST-With-Spring')
"""
}
Внутри замыкания для всех запросов и инструкций используется одно соединение с базой данных.
Кроме того, транзакция автоматически фиксируется при завершении закрытия, если только она не завершается досрочно из-за исключения.
Однако мы также можем вручную зафиксировать или откатить текущую транзакцию с помощью методов класса Sql :
sql.withTransaction {
sql.execute """
INSERT INTO PROJECT (NAME, URL)
VALUES ('tutorials', 'github.com/foreach/tutorials')
"""
sql.commit()
sql.execute """
INSERT INTO PROJECT (NAME, URL)
VALUES ('REST with Spring', 'github.com/foreach/REST-With-Spring')
"""
sql.rollback()
}
7.3. Кэшированные соединения без транзакции
Наконец, чтобы повторно использовать соединение с базой данных без семантики транзакций, описанной выше, мы используем cacheConnection :
sql.cacheConnection {
sql.execute """
INSERT INTO PROJECT (NAME, URL)
VALUES ('tutorials', 'github.com/foreach/tutorials')
"""
throw new Exception('This does not roll back')
}
8. Выводы и дальнейшее чтение
В этой статье мы рассмотрели модуль Groovy SQL и то, как он расширяет и упрощает JDBC с помощью замыканий и строк Groovy.
Затем мы можем с уверенностью заключить, что старый добрый JDBC выглядит немного современнее с капелькой Groovy!
Мы не говорили обо всех функциях Groovy SQL; например, мы исключили пакетную обработку , хранимые процедуры, метаданные и другие вещи.
Дополнительные сведения см. в документации по Groovy .
Реализацию всех этих примеров и фрагментов кода можно найти в проекте GitHub — это проект Maven, поэтому его легко импортировать и запускать как есть.