Введение в Jooq со Spring

1. Обзор

В этой статье будут представлены объектно-ориентированные запросы Jooq — Jooq — и простой способ его настройки в сотрудничестве с Spring Framework.

Большинство Java-приложений в той или иной степени сохраняют SQL и получают доступ к этому уровню с помощью инструментов более высокого уровня, таких как JPA. И хотя это полезно, в некоторых случаях вам действительно нужен более тонкий инструмент с более тонкими нюансами, чтобы получить ваши данные или фактически воспользоваться всеми преимуществами, которые может предложить базовая БД.

Jooq избегает некоторых типичных шаблонов ORM и генерирует код, который позволяет нам создавать безопасные для типов запросы и получать полный контроль над сгенерированным SQL с помощью чистого и мощного плавного API.

Эта статья посвящена Spring MVC. В нашей статье Spring Boot Support for jOOQ описывается, как использовать jOOQ в Spring Boot.

2. Зависимости Maven

Следующие зависимости необходимы для запуска кода в этом руководстве.

2.1. jOOQ

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.14.15</version>
</dependency>

2.2. Весна

Для нашего примера требуется несколько зависимостей Spring; однако для простоты нам просто нужно явно включить два из них в файл POM:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.2.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.2.RELEASE</version>
</dependency>

2.3. База данных

Чтобы упростить наш пример, мы будем использовать встроенную базу данных H2:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.191</version>
</dependency>

3. Генерация кода

3.1. Структура базы данных

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

Для простоты мы создадим только три таблицы: book для книг, author для авторов и еще одну таблицу с именем author_book для представления отношения «многие ко многим» между авторами и книгами. В таблице авторов есть три столбца: id , first_name и last_name. Таблица book содержит только столбец title и первичный ключ id .

Следующие SQL-запросы, хранящиеся в файле ресурсов intro_schema.sql , будут выполняться к базе данных, которую мы уже настроили ранее, чтобы создать необходимые таблицы и заполнить их образцами данных:

DROP TABLE IF EXISTS author_book, author, book;

CREATE TABLE author (
  id             INT          NOT NULL PRIMARY KEY,
  first_name     VARCHAR(50),
  last_name      VARCHAR(50)  NOT NULL
);

CREATE TABLE book (
  id             INT          NOT NULL PRIMARY KEY,
  title          VARCHAR(100) NOT NULL
);

CREATE TABLE author_book (
  author_id      INT          NOT NULL,
  book_id        INT          NOT NULL,
  
  PRIMARY KEY (author_id, book_id),
  CONSTRAINT fk_ab_author     FOREIGN KEY (author_id)  REFERENCES author (id)  
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_ab_book       FOREIGN KEY (book_id)    REFERENCES book   (id)
);

INSERT INTO author VALUES 
  (1, 'Kathy', 'Sierra'), 
  (2, 'Bert', 'Bates'), 
  (3, 'Bryan', 'Basham');

INSERT INTO book VALUES 
  (1, 'Head First Java'), 
  (2, 'Head First Servlets and JSP'),
  (3, 'OCA/OCP Java SE 7 Programmer');

INSERT INTO author_book VALUES (1, 1), (1, 3), (2, 1);

3.2. Свойства Плагин Maven

Мы будем использовать три разных плагина Maven для генерации кода Jooq. Первый из них — плагин Properties Maven.

Этот плагин используется для чтения данных конфигурации из файла ресурсов. Это не требуется, так как данные могут быть добавлены непосредственно в POM, но рекомендуется управлять свойствами извне.

В этом разделе мы определим свойства для соединений с базой данных, включая класс драйвера JDBC, URL-адрес базы данных, имя пользователя и пароль, в файле с именем intro_config.properties . Экстернализация этих свойств упрощает переключение базы данных или просто изменение данных конфигурации.

Цель read-project-properties этого подключаемого модуля должна быть связана с ранней фазой, чтобы данные конфигурации могли быть подготовлены для использования другими подключаемыми модулями. В этом случае он привязан к фазе инициализации :

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>properties-maven-plugin</artifactId>
    <version>1.0.0</version>
    <executions>
        <execution>
            <phase>initialize</phase>
            <goals>
                <goal>read-project-properties</goal>
            </goals>
            <configuration>
                <files>
                    <file>src/main/resources/intro_config.properties</file>
                </files>
            </configuration>
        </execution>
    </executions>
</plugin>

3.3. Плагин SQL Maven

Плагин SQL Maven используется для выполнения операторов SQL для создания и заполнения таблиц базы данных. Он будет использовать свойства, извлеченные из файла intro_config.properties подключаемым модулем Properties Maven, и принимать операторы SQL из ресурса intro_schema.sql .

Плагин SQL Maven настроен следующим образом:

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>
    <version>1.5</version>
    <executions>
        <execution>
            <phase>initialize</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <driver>${db.driver}</driver>
                <url>${db.url}</url>
                <username>${db.username}</username>
                <password>${db.password}</password>
                <srcFiles>
                    <srcFile>src/main/resources/intro_schema.sql</srcFile>
                </srcFiles>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.191</version>
        </dependency>
    </dependencies>
</plugin>

Обратите внимание, что этот подключаемый модуль должен быть размещен позже, чем подключаемый модуль Properties Maven в файле POM, поскольку цели их выполнения привязаны к одной и той же фазе, и Maven будет выполнять их в том порядке, в котором они перечислены.

3.4. Плагин jOOQ Codegen

Плагин Jooq Codegen генерирует код Java из структуры таблицы базы данных. Его цель генерации должна быть привязана к фазе генерации источников , чтобы обеспечить правильный порядок выполнения. Метаданные плагина выглядят следующим образом:

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>
                <jdbc>
                    <driver>${db.driver}</driver>
                    <url>${db.url}</url>
                    <user>${db.username}</user>
                    <password>${db.password}</password>
                </jdbc>
                <generator>
                    <target>
                        <packageName>com.foreach.jooq.introduction.db</packageName>
                        <directory>src/main/java</directory>
                    </target>
                </generator>
            </configuration>
        </execution>
    </executions>
</plugin>

3.5. Генерация кода

Чтобы завершить процесс генерации исходного кода, нам нужно запустить фазу Maven generate-sources . В Eclipse мы можем сделать это, щелкнув проект правой кнопкой мыши и выбрав Run As -> Maven generate-sources . После выполнения команды генерируются исходные файлы, соответствующие таблицам author , book , author_book (и ряду других для вспомогательных классов).

Давайте углубимся в классы таблиц, чтобы увидеть, что создал Jooq. У каждого класса есть статическое поле с тем же именем, что и у класса, за исключением того, что все буквы в имени заглавные. Ниже приведены фрагменты кода, взятые из определений сгенерированных классов:

Класс автора :

public class Author extends TableImpl<AuthorRecord> {
    public static final Author AUTHOR = new Author();

    // other class members
}

Класс книги :

public class Book extends TableImpl<BookRecord> {
    public static final Book BOOK = new Book();

    // other class members
}

Класс AuthorBook :

public class AuthorBook extends TableImpl<AuthorBookRecord> {
    public static final AuthorBook AUTHOR_BOOK = new AuthorBook();

    // other class members
}

Экземпляры, на которые ссылаются эти статические поля, будут служить объектами доступа к данным для представления соответствующих таблиц при работе с другими слоями в проекте.

4. Конфигурация пружины

4.1. Преобразование исключений jOOQ в Spring

Чтобы исключения, выдаваемые при выполнении Jooq, согласовывались с поддержкой Spring для доступа к базе данных, нам нужно преобразовать их в подтипы класса DataAccessException .

Давайте определим реализацию интерфейса ExecuteListener для преобразования исключений:

public class ExceptionTranslator extends DefaultExecuteListener {
    public void exception(ExecuteContext context) {
        SQLDialect dialect = context.configuration().dialect();
        SQLExceptionTranslator translator 
          = new SQLErrorCodeSQLExceptionTranslator(dialect.name());
        context.exception(translator
          .translate("Access database using Jooq", context.sql(), context.sqlException()));
    }
}

Этот класс будет использоваться контекстом приложения Spring.

4.2. Настройка Spring

В этом разделе будут рассмотрены шаги по определению PersistenceContext , который содержит метаданные и bean-компоненты, которые будут использоваться в контексте приложения Spring.

Начнем с применения необходимых аннотаций к классу:

• @Configuration : сделать так, чтобы класс распознавался как контейнер для bean-компонентов.
• @ComponentScan : Настройте директивы сканирования, включая параметр value для объявления массива имен пакетов для поиска компонентов. В этом руководстве искомый пакет создается плагином Jooq Codegen Maven.
• @EnableTransactionManagement : разрешить Spring управлять транзакциями.
• @PropertySource : укажите расположение загружаемых файлов свойств. Значение в этой статье указывает на файл, содержащий данные конфигурации и диалект базы данных, который оказывается тем же файлом, который упоминался в подразделе 4.1.

@Configuration
@ComponentScan({"com.foreach.Jooq.introduction.db.public_.tables"})
@EnableTransactionManagement
@PropertySource("classpath:intro_config.properties")
public class PersistenceContext {
    // Other declarations
}

Затем используйте объект Environment для получения данных конфигурации, которые затем используются для настройки bean- компонента DataSource :

@Autowired
private Environment environment;

@Bean
public DataSource dataSource() {
    JdbcDataSource dataSource = new JdbcDataSource();

    dataSource.setUrl(environment.getRequiredProperty("db.url"));
    dataSource.setUser(environment.getRequiredProperty("db.username"));
    dataSource.setPassword(environment.getRequiredProperty("db.password"));

return dataSource; 
}

Теперь мы определяем несколько bean-компонентов для работы с операциями доступа к базе данных:

@Bean
public TransactionAwareDataSourceProxy transactionAwareDataSource() {
    return new TransactionAwareDataSourceProxy(dataSource());
}

@Bean
public DataSourceTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource());
}

@Bean
public DataSourceConnectionProvider connectionProvider() {
    return new DataSourceConnectionProvider(transactionAwareDataSource());
}

@Bean
public ExceptionTranslator exceptionTransformer() {
    return new ExceptionTranslator();
}
    
@Bean
public DefaultDSLContext dsl() {
    return new DefaultDSLContext(configuration());
}

Наконец, мы предоставляем реализацию конфигурации Jooq и объявляем ее как компонент Spring, который будет использоваться классом DSLContext :

@Bean
public DefaultConfiguration configuration() {
    DefaultConfiguration JooqConfiguration = new DefaultConfiguration();
    jooqConfiguration.set(connectionProvider());
    jooqConfiguration.set(new DefaultExecuteListenerProvider(exceptionTransformer()));

    String sqlDialectName = environment.getRequiredProperty("jooq.sql.dialect");
    SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);
    jooqConfiguration.set(dialect);

    return jooqConfiguration;
}

5. Использование jOOQ со Spring

В этом разделе демонстрируется использование Jooq в общих запросах доступа к базе данных. Существует два теста, один для фиксации и один для отката, для каждого типа операции «записи», включая вставку, обновление и удаление данных. Использование операции «чтение» показано при выборе данных для проверки запросов «записи».

Мы начнем с объявления автоматически подключаемого объекта DSLContext и экземпляров сгенерированных Jooq классов, которые будут использоваться всеми методами тестирования:

@Autowired
private DSLContext dsl;

Author author = Author.AUTHOR;
Book book = Book.BOOK;
AuthorBook authorBook = AuthorBook.AUTHOR_BOOK;

5.1. Вставка данных

Первым шагом является вставка данных в таблицы:

dsl.insertInto(author)
  .set(author.ID, 4)
  .set(author.FIRST_NAME, "Herbert")
  .set(author.LAST_NAME, "Schildt")
  .execute();
dsl.insertInto(book)
  .set(book.ID, 4)
  .set(book.TITLE, "A Beginner's Guide")
  .execute();
dsl.insertInto(authorBook)
  .set(authorBook.AUTHOR_ID, 4)
  .set(authorBook.BOOK_ID, 4)
  .execute();

Запрос SELECT для извлечения данных:

Result<Record3<Integer, String, Integer>> result = dsl
  .select(author.ID, author.LAST_NAME, DSL.count())
  .from(author)
  .join(authorBook)
  .on(author.ID.equal(authorBook.AUTHOR_ID))
  .join(book)
  .on(authorBook.BOOK_ID.equal(book.ID))
  .groupBy(author.LAST_NAME)
  .fetch();

Приведенный выше запрос выдает следующий результат:

+----+---------+-----+
|  ID|LAST_NAME|count|
+----+---------+-----+
|   1|Sierra   |    2|
|   2|Bates    |    1|
|   4|Schildt  |    1|
+----+---------+-----+

Результат подтверждается Assert API:

assertEquals(3, result.size());
assertEquals("Sierra", result.getValue(0, author.LAST_NAME));
assertEquals(Integer.valueOf(2), result.getValue(0, DSL.count()));
assertEquals("Schildt", result.getValue(2, author.LAST_NAME));
assertEquals(Integer.valueOf(1), result.getValue(2, DSL.count()));

Когда происходит сбой из-за недопустимого запроса, генерируется исключение, и транзакция откатывается. В следующем примере запрос INSERT нарушает ограничение внешнего ключа, что приводит к исключению:

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenInserting_thenFail() {
    dsl.insertInto(authorBook)
      .set(authorBook.AUTHOR_ID, 4)
      .set(authorBook.BOOK_ID, 5)
      .execute();
}

5.2. Обновление данных

Теперь обновим существующие данные:

dsl.update(author)
  .set(author.LAST_NAME, "ForEach")
  .where(author.ID.equal(3))
  .execute();
dsl.update(book)
  .set(book.TITLE, "Building your REST API with Spring")
  .where(book.ID.equal(3))
  .execute();
dsl.insertInto(authorBook)
  .set(authorBook.AUTHOR_ID, 3)
  .set(authorBook.BOOK_ID, 3)
  .execute();

Получите необходимые данные:

Result<Record3<Integer, String, String>> result = dsl
  .select(author.ID, author.LAST_NAME, book.TITLE)
  .from(author)
  .join(authorBook)
  .on(author.ID.equal(authorBook.AUTHOR_ID))
  .join(book)
  .on(authorBook.BOOK_ID.equal(book.ID))
  .where(author.ID.equal(3))
  .fetch();

Вывод должен быть:

+----+---------+----------------------------------+
|  ID|LAST_NAME|TITLE                             |
+----+---------+----------------------------------+
|   3|ForEach |Building your REST API with Spring|
+----+---------+----------------------------------+

Следующий тест проверит, что Jooq работает должным образом:

assertEquals(1, result.size());
assertEquals(Integer.valueOf(3), result.getValue(0, author.ID));
assertEquals("ForEach", result.getValue(0, author.LAST_NAME));
assertEquals("Building your REST API with Spring", result.getValue(0, book.TITLE));

В случае сбоя выбрасывается исключение и транзакция откатывается, что мы подтверждаем тестом:

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenUpdating_thenFail() {
    dsl.update(authorBook)
      .set(authorBook.AUTHOR_ID, 4)
      .set(authorBook.BOOK_ID, 5)
      .execute();
}

5.3. Удаление данных

Следующий метод удаляет некоторые данные:

dsl.delete(author)
  .where(author.ID.lt(3))
  .execute();

Вот запрос для чтения затронутой таблицы:

Result<Record3<Integer, String, String>> result = dsl
  .select(author.ID, author.FIRST_NAME, author.LAST_NAME)
  .from(author)
  .fetch();

Вывод запроса:

+----+----------+---------+
|  ID|FIRST_NAME|LAST_NAME|
+----+----------+---------+
|   3|Bryan     |Basham   |
+----+----------+---------+

Следующий тест проверяет удаление:

assertEquals(1, result.size());
assertEquals("Bryan", result.getValue(0, author.FIRST_NAME));
assertEquals("Basham", result.getValue(0, author.LAST_NAME));

С другой стороны, если запрос недействителен, он выдаст исключение, и транзакция откатится. Следующий тест докажет, что:

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenDeleting_thenFail() {
    dsl.delete(book)
      .where(book.ID.equal(1))
      .execute();
}

6. Заключение

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

Реализацию всех этих примеров и фрагментов кода можно найти в проекте на GitHub .