1. Введение
В этом руководстве мы рассмотрим Sql2o , небольшую и быструю библиотеку для доступа к реляционным базам данных в идиоматической Java.
Стоит отметить, что хотя Sql2o работает путем сопоставления результатов запроса с POJO (простыми старыми объектами Java), это не полное решение ORM, такое как Hibernate.
2. Настройка SQL2O
Sql2o — это один jar-файл, который мы можем легко добавить в зависимости нашего проекта:
<dependency>
<groupId>org.sql2o</groupId>
<artifactId>sql2o</artifactId>
<version>1.6.0</version>
</dependency>
В наших примерах мы также будем использовать встроенную базу данных HSQL; чтобы следовать, мы также можем включить его:
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.4.0</version>
<scope>test</scope>
</dependency>
Maven Central размещает последнюю версию sql2o и HSQLDB .
3. Подключение к базе данных
Чтобы установить соединение, мы начинаем с экземпляра класса Sql2o :
Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
Здесь мы указываем URL-адрес подключения, имя пользователя и пароль в качестве параметров конструктора.
Объект Sql2o является потокобезопасным, и мы можем совместно использовать его в приложении.
3.1. Использование источника данных
В большинстве приложений мы будем использовать DataSource вместо необработанного соединения DriverManager , возможно, для использования пула соединений или для указания дополнительных параметров соединения. Не беспокойтесь, Sql2o поможет нам:
Sql2o sql2o = new Sql2o(datasource);
3.2. Работа с соединениями
Простое создание экземпляра объекта Sql2o не устанавливает никакого соединения с базой данных.
Вместо этого мы используем метод open для получения объекта Connection (обратите внимание, что это не JDBC Connection ). Так как Connection является AutoCloseable, мы можем обернуть его в блок try-with-resources :
try (Connection connection = sql2o.open()) {
// use the connection
}
4. Вставьте и обновите операторы
Теперь давайте создадим базу данных и поместим в нее некоторые данные. На протяжении всего руководства мы будем использовать простую таблицу под названием проект:
connection.createQuery(
"create table project "
+ "(id integer identity, name varchar(50), url varchar(100))").executeUpdate();
executeUpdate возвращает объект Connection , чтобы мы могли сцепить несколько вызовов. Затем, если мы хотим узнать количество затронутых строк, мы используем getResult:
assertEquals(0, connection.getResult());
Мы будем применять шаблон, который мы только что видели — createQuery и executeUpdate — для всех операторов DDL, INSERT и UPDATE.
4.1. Получение сгенерированных ключевых значений
Однако в некоторых случаях мы можем захотеть вернуть сгенерированные значения ключей. Это значения ключевых столбцов, которые вычисляются автоматически (например, при использовании автоинкремента в определенных базах данных).
Делаем это в два этапа. Во-первых, с дополнительным параметром для createQuery:
Query query = connection.createQuery(
"insert into project (name, url) "
+ "values ('tutorials', 'github.com/foreach/tutorials')", true);
Затем, вызывая getKey для соединения:
assertEquals(0, query.executeUpdate().getKey());
Если ключей больше одного, вместо этого мы используем getKeys , который возвращает массив:
assertEquals(1, query.executeUpdate().getKeys()[0]);
5. Извлечение данных из базы данных
Давайте теперь перейдем к сути дела: запросы SELECT и сопоставление наборов результатов с объектами Java.
Во-первых, мы должны определить класс POJO с геттерами и сеттерами для представления нашей таблицы проектов:
public class Project {
long id;
private String name;
private String url;
//Standard getters and setters
}
Затем, как и прежде, мы напишем наш запрос:
Query query = connection.createQuery("select * from project order by id");
Однако на этот раз мы будем использовать новый метод executeAndFetch:
List<Project> list = query.executeAndFetch(Project.class);
Как мы видим, метод принимает класс результатов в качестве параметра, которому Sql2o сопоставляет строки необработанного набора результатов, поступающего из базы данных.
5.1. Сопоставление столбцов
Sql2o сопоставляет столбцы со свойствами JavaBean по имени без учета регистра.
Однако соглашения об именах различаются между Java и реляционными базами данных. Предположим, что мы добавляем в наши проекты свойство даты создания:
public class Project {
long id;
private String name;
private String url;
private Date creationDate;
//Standard getters and setters
}
В схеме базы данных, скорее всего, мы назовем то же самое свойство create_date.
Конечно, мы можем использовать его в наших запросах:
Query query = connection.createQuery(
"select name, url, creation_date as creationDate from project");
Однако это утомительно, и мы теряем возможность использовать select *.
Другой вариант — дать Sql2o указание сопоставить дату создания с датой создания. То есть мы можем сказать запросу о отображении:
connection.createQuery("select * from project")
.addColumnMapping("creation_date", "creationDate");
Это хорошо, если мы используем createDate экономно, в нескольких запросах; однако при интенсивном использовании в более крупном проекте становится утомительным и подверженным ошибкам повторять один и тот же факт снова и снова.
К счастью, мы также можем указать сопоставления глобально:
Map<String, String> mappings = new HashMap<>();
mappings.put("CREATION_DATE", "creationDate");
sql2o.setDefaultColumnMappings(mappings);
Конечно, это приведет к тому, что каждый экземпляр create_date будет сопоставлен с createDate , так что это еще одна причина стремиться к тому, чтобы имена согласовывались между определениями наших данных.
5.2. Скалярные результаты
Иногда нам нужно извлечь из запроса один скалярный результат. Например, когда нам нужно подсчитать количество записей.
В этих случаях определение класса и повторение списка, который, как мы знаем, содержит один элемент, является излишним. Таким образом, Sql2o включает метод executeScalar :
Query query = connection.createQuery(
"select count(*) from project");
assertEquals(2, query.executeScalar(Integer.class));
Здесь мы указываем тип возвращаемого значения Integer . Однако это необязательно, и мы можем позволить базовому драйверу JDBC принять решение.
5.3. Комплексные результаты
Вместо этого иногда сложные запросы (например, для отчетов) не могут быть легко отображены на объект Java. Мы также можем решить, что не хотим кодировать класс Java для использования только в одном запросе.
Таким образом, Sql2o также допускает динамическое сопоставление более низкого уровня с табличными структурами данных. Мы получаем доступ к этому с помощью метода executeAndFetchTable :
Query query = connection.createQuery(
"select * from project order by id");
Table table = query.executeAndFetchTable();
Затем мы можем извлечь список карт:
List<Map<String, Object>> list = table.asList();
assertEquals("tutorials", list.get(0).get("name"));
В качестве альтернативы мы можем сопоставить данные со списком объектов Row , которые представляют собой сопоставления имен столбцов со значениями, подобно ResultSet s:
List<Row> rows = table.rows();
assertEquals("tutorials", rows.get(0).getString("name"));
6. Привязка параметров запроса
Многие SQL-запросы имеют фиксированную структуру с несколькими параметризованными частями. Мы могли бы наивно написать эти частично динамические запросы с конкатенацией строк.
Однако Sql2o допускает параметризованные запросы, так что:
- Мы избегаем атак SQL-инъекций
- Мы позволяем базе данных кэшировать часто используемые запросы и повышать производительность.
- Наконец, мы избавлены от необходимости кодировать сложные типы, такие как даты и время.
Итак, мы можем использовать именованные параметры с Sql2o для достижения всего вышеперечисленного. Мы вводим параметры через двоеточие и связываем их с помощью метода addParameter :
Query query = connection.createQuery(
"insert into project (name, url) values (:name, :url)")
.addParameter("name", "REST with Spring")
.addParameter("url", "github.com/foreach/REST-With-Spring");
assertEquals(1, query.executeUpdate().getResult());
6.1. Привязка из POJO
Sql2o предлагает альтернативный способ привязки параметров: то есть с использованием POJO в качестве источника. Этот метод особенно удобен, когда запрос имеет много параметров, и все они относятся к одному и тому же объекту. Итак, давайте представим метод привязки :
Project project = new Project();
project.setName("REST with Spring");
project.setUrl("github.com/foreach/REST-With-Spring");
connection.createQuery(
"insert into project (name, url) values (:name, :url)")
.bind(project)
.executeUpdate();
assertEquals(1, connection.getResult());
7. Транзакции и пакетные запросы
С транзакцией мы можем выполнять несколько операторов SQL как одну операцию, которая является атомарной. То есть либо успешно, либо массово, без промежуточных результатов. Фактически транзакции являются одной из ключевых особенностей реляционных баз данных.
Чтобы открыть транзакцию, мы используем метод beginTransaction вместо метода open , который мы использовали до сих пор:
try (Connection connection = sql2o.beginTransaction()) {
// here, the transaction is active
}
Когда выполнение покидает блок, Sql2o автоматически откатывает транзакцию , если она все еще активна.
7.1. Ручная фиксация и откат
Однако мы можем явно зафиксировать или откатить транзакцию с помощью соответствующих методов:
try (Connection connection = sql2o.beginTransaction()) {
boolean transactionSuccessful = false;
// perform some operations
if(transactionSuccessful) {
connection.commit();
} else {
connection.rollback();
}
}
Обратите внимание, что как фиксация , так и откат завершают транзакцию. Последующие операторы будут выполняться без транзакции, поэтому они не будут автоматически откатываться в конце блока.
Однако мы можем зафиксировать или откатить транзакцию, не завершая ее:
try (Connection connection = sql2o.beginTransaction()) {
List list = connection.createQuery("select * from project")
.executeAndFetchTable()
.asList();
assertEquals(0, list.size());
// insert or update some data
connection.rollback(false);
// perform some other insert or update queries
}
// implicit rollback
try (Connection connection = sql2o.beginTransaction()) {
List list = connection.createQuery("select * from project")
.executeAndFetchTable()
.asList();
assertEquals(0, list.size());
}
7.2. Пакетные операции
Когда нам нужно много раз выполнить один и тот же оператор с разными параметрами, запуск их в пакете дает большое преимущество в производительности.
К счастью, благодаря объединению двух методов, которые мы описали до сих пор, — параметризованных запросов и транзакций — их достаточно просто запускать в пакетном режиме:
- Во-первых, мы создаем запрос только один раз
- Затем мы связываем параметры и вызываем
addToBatchдля каждого экземпляра запроса. - Наконец, мы вызываем
executeBatch:
try (Connection connection = sql2o.beginTransaction()) {
Query query = connection.createQuery(
"insert into project (name, url) " +
"values (:name, :url)");
for (int i = 0; i < 1000; i++) {
query.addParameter("name", "tutorials" + i);
query.addParameter("url", "https://github.com/foreach/tutorials" + i);
query.addToBatch();
}
query.executeBatch();
connection.commit();
}
try (Connection connection = sql2o.beginTransaction()) {
assertEquals(
1000L,
connection.createQuery("select count(*) from project").executeScalar());
}
7.3. Ленивая выборка
И наоборот, когда один запрос возвращает большое количество результатов, преобразование их всех и сохранение их в списке занимает много памяти.
Итак, Sql2o поддерживает ленивый режим, когда строки возвращаются и отображаются по одной:
Query query = connection.createQuery("select * from project");
try (ResultSetIterable<Project> projects = query.executeAndFetchLazy(Project.class)) {
for(Project p : projects) {
// do something with the project
}
}
Обратите внимание, что ResultSetIterable является AutoCloseable и предназначен для использования с try-with-resources для закрытия базового ResultSet по завершении.
8. Выводы
В этом руководстве мы представили обзор библиотеки Sql2o и наиболее распространенных шаблонов ее использования. Дополнительную информацию можно найти в вики Sql20 на GitHub.
Кроме того, реализацию всех этих примеров и фрагментов кода можно найти в проекте GitHub , который является проектом Maven, поэтому его должно быть легко импортировать и запускать как есть.