Автор оригинала: Alessio Stalla.
1. введение
В этой статье мы рассмотрим, как запрашивать реляционные базы данных с помощью JDBC , используя идиоматический Groovy.
JDBC, хотя и является относительно низкоуровневым, является основой большинства форм и других библиотек доступа к данным высокого уровня в JVM. И мы, конечно, можем использовать JDBC непосредственно в Groovy; однако у него довольно громоздкий API.
К счастью для нас, стандартная библиотека Groovy основана на JDBC и представляет собой чистый, простой и в то же время мощный интерфейс. Итак, мы будем изучать модуль Groovy SQL.
Мы собираемся взглянуть на JDBC в простом Groovy, не рассматривая никаких рамок, таких как Spring, для которых у нас есть другие руководства .
2. JDBC и заводная настройка
Мы должны включить модуль 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. Указание Параметров Подключения
В этой статье мы будем использовать базу данных SQL, которая представляет собой легкую реляционную базу данных, которая в основном используется в тестах.
Для подключения к базе данных требуется 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. Использование источника данных
Обычно, особенно в программах, запущенных внутри сервера приложений, для подключения к базе данных используется источник данных.
Кроме того, когда мы хотим объединить соединения или использовать JNDI, источник данных является наиболее естественным вариантом.
Класс Groovy Sql отлично принимает источники данных:
def sql = Sql.newInstance(datasource)
3.3. Автоматическое Управление Ресурсами
Не забывать вызывать close () , когда мы закончим с экземпляром Sql , утомительно; в конце концов, машины запоминают вещи намного лучше, чем мы.
С помощью Sql мы можем обернуть наш код в закрытие и автоматически вызвать 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/eugenp/tutorials')
"""выполнить вставку ведет себя точно так же , как выполнить , но что он возвращает?
Оказывается, возвращаемое значение представляет собой матрицу: ее строки-это вставленные строки (помните, что один оператор может привести к вставке нескольких строк), а ее столбцы-сгенерированные значения.
Это звучит сложно, но в нашем случае, который на сегодняшний день является наиболее распространенным, есть одна строка и одно генерируемое значение:
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. Обновление и удаление данных
Аналогично, существует специальный метод для изменения и удаления данных: executeUpdate .
Опять же, это отличается от execute только возвращаемым значением, и мы рассмотрим только его простейшую форму.
Возвращаемое значение в данном случае представляет собой целое число, количество затронутых строк:
def count = sql.executeUpdate("UPDATE PROJECT SET URL = 'https://' + URL")
assertEquals(2, count)5. Запрос к базе данных
Все начинает налаживаться, когда мы запрашиваем базу данных.
Работа с классом JDBC ResultSet – это не совсем весело. К счастью для нас, Groovy предлагает хорошую абстракцию по всему этому.
5.1. Повторение Результатов Запроса
В то время как петли так старомодны… в наши дни мы все занимаемся закрытиями.
И Groovy здесь, чтобы удовлетворить ваши вкусы:
sql.eachRow("SELECT * FROM PROJECT") { GroovyResultSet rs ->
haveFunWith(rs)
}Метод eachRow выдает наш запрос к базе данных и вызывает закрытие каждой строки.
Как мы видим, строка представлена экземпляром GroovyResultSet , который является расширением старого простого набора результатов с несколькими добавленными преимуществами. Читайте дальше, чтобы узнать об этом больше.
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. Разбиение на страницы
Мы можем легко просматривать результаты на странице, т. Е. Загружать только подмножество, начиная с некоторого смещения до некоторого максимального количества строк. Это распространенная проблема, например, в веб-приложениях.
каждая строка и связанные методы имеют перегрузки, принимающие смещение и максимальное количество возвращаемых строк:
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 возвращает список строк, а не перебирает их, как каждая строка .
6. Параметризованные запросы и утверждения
Чаще всего запросы и операторы не полностью фиксируются во время компиляции; обычно они имеют статическую часть и динамическую часть в виде параметров.
Если вы думаете о конкатенации строк, остановитесь сейчас и прочитайте об инъекции SQL!
Ранее мы упоминали, что методы, которые мы видели в предыдущих разделах, имеют много перегрузок для различных сценариев.
Давайте представим те перегрузки, которые связаны с параметрами в SQL – запросах и операторах.
6.1. Строки С Заполнителями
В стиле, аналогичном обычному JDBC, мы можем использовать позиционные параметры:
sql.execute(
'INSERT INTO PROJECT (NAME, URL) VALUES (?, ?)',
'tutorials', 'github.com/eugenp/tutorials')или мы можем использовать именованные параметры с картой:
sql.execute(
'INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)',
[name: 'REST with Spring', url: 'github.com/eugenp/REST-With-Spring'])Это работает для выполнить , Выполнить обновление , строк и каждой строки . выполнить вставку также поддерживает параметры, но его подпись немного отличается и сложнее.
6.2. Заводные Струны
Мы также можем выбрать более строгий стиль, используя строки с заполнителями.
Все методы, которые мы видели, не заменяют заполнители в GStrings обычным способом; скорее, они вставляют их в качестве параметров JDBC, обеспечивая правильное сохранение синтаксиса SQL, без необходимости цитировать или экранировать что-либо и, следовательно, без риска инъекции.
Это совершенно нормально, безопасно и классно:
def name = 'REST with Spring'
def url = 'github.com/eugenp/REST-With-Spring'
sql.execute "INSERT INTO PROJECT (NAME, URL) VALUES (${name}, ${url})"7. Транзакции и подключения
До сих пор мы пропустили очень важную проблему: транзакции.
На самом деле, мы вообще не говорили о том, как Sql Groovy управляет подключениями.
7.1. Кратковременные Соединения
В примерах, представленных до сих пор, каждый запрос или инструкция были отправлены в базу данных с использованием нового, выделенного соединения. Sql закрывает соединение, как только операция завершается.
Конечно, если мы используем пул соединений, влияние на производительность может быть небольшим.
Тем не менее, если мы хотим выдавать несколько операторов и запросов DML как одну атомарную операцию , нам нужна транзакция.
Кроме того, для того, чтобы транзакция была возможна в первую очередь, нам нужно соединение, охватывающее несколько операторов и запросов.
7.2. Транзакции С Кэшированным Подключением
Groovy SQL не позволяет нам явно создавать транзакции или получать к ним доступ.
Вместо этого мы используем метод withTransaction с закрытием:
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')
"""
}Внутри закрытия для всех запросов и инструкций используется одно соединение с базой данных.
Кроме того, транзакция автоматически фиксируется при завершении закрытия, если только она не завершается досрочно из-за исключения.
Однако мы также можем вручную зафиксировать или откатить текущую транзакцию с помощью методов в классе Sql :
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. Кэшированные соединения Без Транзакции
Наконец, для повторного использования соединения с базой данных без описанной выше семантики транзакций мы используем Соединение с кэшем :
sql.cacheConnection {
sql.execute """
INSERT INTO PROJECT (NAME, URL)
VALUES ('tutorials', 'github.com/eugenp/tutorials')
"""
throw new Exception('This does not roll back')
}8. Выводы и дальнейшее чтение
В этой статье мы рассмотрели модуль SQL Groovy и то, как он улучшает и упрощает JDBC с помощью замыканий и строк Groovy.
Тогда мы можем с уверенностью заключить, что обычный старый JDBC выглядит немного более современным с примесью заводного!
Мы не говорили о каждой отдельной функции Groovy SQL; например, мы опустили пакетную обработку , хранимые процедуры, метаданные и другие вещи.
Для получения дополнительной информации см. Документацию Groovy .
Реализацию всех этих примеров и фрагментов кода можно найти в проекте GitHub – это проект Maven, поэтому его должно быть легко импортировать и запускать как есть.