Рубрики
Без рубрики

Руководство по оболочке sql2o JDBC

Узнайте, как взаимодействовать с sql2o – библиотекой Java, которая позволяет манипулировать базами данных с помощью идиоматического языка.

Автор оригинала: Alessio Stalla.

1. введение

В этом уроке мы рассмотрим Sql2o , небольшую и быструю библиотеку для доступа к реляционным базам данных в идиоматической Java.

Стоит отметить, что, хотя Sql2o работает, сопоставляя результаты запросов с POJOs (обычными старыми объектами Java), это не полное решение ORM, такое как Hibernate.

2. Настройка Sql2o

Sql2o-это один файл jar, который мы можем легко добавить в зависимости вашего проекта:


    org.sql2o
    sql2o
    1.6.0

Мы также будем использовать SQL, встроенную базу данных, в наших примерах; чтобы продолжить, мы также можем включить ее:


    org.hsqldb
    hsqldb
    2.4.0
    test

Maven Central размещает последнюю версию sql2o и HSQLDB .

3. Подключение к базе данных

Чтобы установить соединение, мы начинаем с экземпляра Sql2o класс:

Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");

Здесь мы указываем URL-адрес подключения, имя пользователя и пароль в качестве параметров конструктора.

Объект Sql2o потокобезопасен, и мы можем совместно использовать его в приложении.

3.1. Использование источника данных

В большинстве приложений мы хотим использовать источник данных вместо необработанного DriverManager соединения, возможно, для использования пула соединений или для указания дополнительных параметров соединения. Не волнуйтесь, Sql2o нас прикроет:

Sql2o sql2o = new Sql2o(datasource);

3.2. Работа С Подключениями

Простое создание экземпляра объекта Sql2o не устанавливает никакого соединения с базой данных.

Вместо этого мы используем метод open для получения Соединения объекта (обратите внимание, что это не JDBC Соединение ). Поскольку Соединение является автоклавируемым, мы можем завернуть его в try-with-resources блок:

try (Connection connection = sql2o.open()) {
    // use the connection
}

4. Вставка и обновление инструкций

Теперь давайте создадим базу данных и поместим в нее некоторые данные. На протяжении всего урока мы будем использовать простую таблицу под названием project:

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/eugenp/tutorials')", true);

Затем вызов get Key в соединении:

assertEquals(0, query.executeUpdate().getKey());

Если ключей больше одного, вместо этого мы используем get Keys , который возвращает массив:

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");

Однако на этот раз мы будем использовать новый метод execute И Fetch:

List 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
}

В схеме базы данных, скорее всего, мы вызовем одно и то же свойство creation_date.

Конечно, мы можем использовать псевдоним в наших запросах:

Query query = connection.createQuery(
    "select name, url, creation_date as creationDate from project");

Однако это утомительно, и мы теряем возможность использовать select *.

Другой вариант-поручить Sql2o сопоставить creation_date с датой создания. То есть мы можем сообщить запросу о сопоставлении:

connection.createQuery("select * from project")
    .addColumnMapping("creation_date", "creationDate");

Это хорошо, если мы используем дату создания экономно, в нескольких запросах; однако при интенсивном использовании в более крупном проекте становится утомительным и подверженным ошибкам повторять один и тот же факт снова и снова.

К счастью, мы также можем указать сопоставления глобально:

Map mappings = new HashMap<>();
mappings.put("CREATION_DATE", "creationDate");
sql2o.setDefaultColumnMappings(mappings);

Конечно, это приведет к тому , что каждый экземпляр creation_data будет сопоставлен с датой создания , так что это еще одна причина для стремления сохранить имена согласованными в определениях наших данных.

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 также допускает динамическое сопоставление более низкого уровня с табличными структурами данных. Мы получаем доступ к этому с помощью метода execute And Fetch Table :

Query query = connection.createQuery(
    "select * from project order by id");
Table table = query.executeAndFetchTable();

Затем мы можем извлечь список карт:

List> list = table.asList();
assertEquals("tutorials", list.get(0).get("name"));

В качестве альтернативы мы можем сопоставить данные со списком объектов Row , которые являются сопоставлениями имен столбцов со значениями, аналогичными ResultSet s:

List 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/eugenp/REST-With-Spring");
assertEquals(1, query.executeUpdate().getResult());

6.1. Привязка Из POJO

Sql2o предлагает альтернативный способ привязки параметров: то есть с помощью POJOs в качестве источника. Этот метод особенно подходит, когда запрос имеет много параметров, и все они ссылаются на одну и ту же сущность. Итак, давайте представим метод bind :

Project project = new Project();
project.setName("REST with Spring");
project.setUrl("github.com/eugenp/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();
    }
}

Обратите внимание, что оба commit и 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/eugenp/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 projects = query.executeAndFetchLazy(Project.class)) {
    for(Project p : projects) {
        // do something with the project
    }
}

Обратите внимание, что Result Set Iterable является автоклавируемым и предназначен для использования с try-with-resources для закрытия базового ResultSet по завершении.

8. Выводы

В этом руководстве мы представили обзор библиотеки Sql2o и ее наиболее распространенных шаблонов использования. Дополнительную информацию можно найти в вики Sql 20 на GitHub.

Кроме того, реализацию всех этих примеров и фрагментов кода можно найти в проекте GitHub , который является проектом Maven, поэтому его должно быть легко импортировать и запускать как есть.

Читайте ещё по теме: