Статьи

Работа с jOOQ DSL

Эта статья является частью нашего академического курса под названием jOOQ — тип безопасного запроса в БД .

jOOQ — хороший выбор в приложении Java, где важны SQL и конкретная реляционная база данных. Это альтернатива, когда JPA / Hibernate слишком много абстрагирует, JDBC слишком мало. Он показывает, как современный предметно-ориентированный язык может значительно повысить производительность труда разработчиков, внедряя SQL в Java.

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

Примеры, показанные в этом разделе, также доступны из пакета org.jooq.academy.section1 .

1. Идея создания jOOQ DSL

jOOQ — это DSL (предметно-ориентированный язык), который имитирует как стандартный, так и специфический для поставщика синтаксис SQL в Java API. Идея этого API проста для понимания:

  • Будучи внутренним DSL, компилятор Java может проверять ваши запросы SQL на синтаксическую корректность (например, правильный порядок ключевых слов SQL)
  • Имея таблицы и столбцы в качестве сгенерированных объектов Java, компилятор также может проверять правильность метаданных (например, правильные имена и типы столбцов)

Другими словами, когда вы хотите выразить запрос SQL следующим образом:

1
2
3
SELECT   author.first_name, author.last_name
FROM     author
ORDER BY author.id

… тогда вы можете сразу же написать тот же запрос с JOOQ

1
2
3
select  (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.from   (AUTHOR)
.orderBy(AUTHOR.ID);

Как это работает, и как jOOQ и ваш Java-компилятор знают, что означает « select », или что означает « AUTHOR.FIRST_NAME »?

2. Выполнение первого запроса

В приведенном выше примере были сделаны несколько простых предположений, и эти предположения будут сделаны на протяжении всего курса:

Другими словами, в каждом классе, который использует jOOQ, в идеале вы просто добавляете эти два оператора импорта:

1
2
import static org.jooq.example.db.h2.Tables.*;
import static org.jooq.impl.DSL.*;

Это сделает приведенный выше код скомпилированным. Но такой оператор Select ничего не делает, он просто сидит и может быть выведен на консоль:

1
2
3
4
5
System.out.println(
    select  (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .from   (AUTHOR)
    .orderBy(AUTHOR.ID)
);

Выше будет напечатано:

1
select "PUBLIC"."AUTHOR"."FIRST_NAME", "PUBLIC"."AUTHOR"."LAST_NAME" from "PUBLIC"."AUTHOR" order by "PUBLIC"."AUTHOR"."ID" asc

Выполнить такой запрос очень просто. Все, что нам нужно сделать, это предоставить ему соединение JDBC, а затем вызвать для него fetch() :

1
2
3
4
5
DSL.using(connection)
   .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch();

Обратите внимание, что вместо того, чтобы повторять DSL.using(...) все время, вы, конечно, можете также назначить объект локальной переменной или настроить его с помощью Spring или вашей любимой инфраструктуры конфигурации:

1
2
3
4
5
6
DSLContext dsl = DSL.using(connection);
 
dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch();

jOOQ внутренне создаст новый JDBC PreparedStatement , выполнит его, использует JDBC ResultSet и охотно закроет все созданные им ресурсы. Результирующий объект является Result , который также реализует очень полезный метод toString() :

1
2
3
4
5
6
System.out.println(
    dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
       .from(AUTHOR)
       .orderBy(AUTHOR.ID)
       .fetch()
);

Вышеуказанное даст:

1
2
3
4
5
6
+----------+---------+
|FIRST_NAME|LAST_NAME|
+----------+---------+
|George    |Orwell   |
|Paulo     |Coelho   |
+----------+---------+

3. Другие типы выписок

Каждый оператор DML SQL (а также некоторые операторы DDL SQL) изначально поддерживаются jOOQ, включая SELECT , UPDATE , INSERT , DELETE , MERGE . Если мы хотим создать, обновить, удалить новую запись AUTHOR , мы можем написать следующие операторы SQL:

3.1. ВСТАВИТЬ

1
2
3
dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(3, "Alfred", "Hitchcock")
   .execute();

Обратите внимание, что вместо вызова fetch() мы теперь вызовем execute() , который возвращает количество затронутых строк.

Интересным аспектом вышеупомянутого запроса является тот факт, что jOOQ использует много обобщений Java для обеспечения безопасности типов в ваших запросах. Например, следующие два запроса приведут к ошибкам компиляции:

1
2
3
4
5
dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(3, "Alfred")
// ^^^^^^^ values() expects three arguments (for ID, FIRST_NAME, LAST_NAME),
//                  but only two were provided!
   .execute()

Это довольно мощно, так как вы никогда не забудете добавить равное количество значений в предложение VALUES , как того требует предложение INTO . Это также распространяется на несоответствия типов. Если вы решили изменить порядок столбцов в предложении INTO , но забыли адаптировать предложение VALUES , есть вероятность, что ваш компилятор Java снова будет жаловаться. Следующее не будет компилироваться:

1
2
3
4
5
dsl.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, AUTHOR.ID)
   .values(4, "Alfred", "Hitchcock")
// ^^^^^^^ values() expects arguments of type (String, String, Integer),
                    but (Integer, String, String) was provided!
   .execute()

3.2. ОБНОВИТЬ

Оператор UPDATE так же прост, как и предыдущие:

1
2
3
4
dsl.update(AUTHOR)
   .set(AUTHOR.DATE_OF_BIRTH, Date.valueOf("1899-08-13"))
   .where(AUTHOR.ID.eq(3))
   .execute()

К настоящему времени выбор автора с ID = 3 приведет к полной записи AUTHOR :

1
2
3
4
dsl.select()
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(3))
   .fetch()

… уступающий

1
2
3
4
5
+----+----------+---------+-------------+
|  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|
+----+----------+---------+-------------+
|   3|Alfred    |Hitchcock|1899-08-13   |
+----+----------+---------+-------------+

3.4. УДАЛИТЬ

И наконец, оператор DELETE следует тривиально:

1
2
3
dsl.delete(AUTHOR)
   .where(AUTHOR.ID.eq(3))
   .execute()

4. Предикаты

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

4.1. Ежедневные предикаты

4.2. Более продвинутые предикаты

Кроме того, в SQL предикаты могут быть легко объединены с использованием AND и OR . jOOQ свободно отражает все эти предикаты непосредственно на типах столбцов. Это лучше всего объяснить на примере:

4,3. Предикаты сравнения

1
2
3
4
5
// A filtering predicate
AUTHOR.ID.eq(3);
 
// A join predicate
AUTHOR.ID.eq(BOOK.AUTHOR_ID);

Большинство из этих предикатов также являются типобезопасными, например, вы не можете сравнивать числа со строками.

1
2
3
AUTHOR.ID.eq("abc");
//        ^^ Compilation error. An expression of type Integer is expected
//           (or Field<Integer>, or Select<? extends Record1<Integer>>)

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

1
2
3
4
5
// IN list
AUTHOR.ID.in(1, 2, 3);
 
// IN with subquery
AUTHOR.ID.in(select(BOOK.AUTHOR_ID).from(BOOK))

Второй пример выполняет AUTHOR между таблицами AUTHOR и BOOK , возвращая только тех авторов, которые написали хотя бы одну книгу. Из-за обобщений Java следующий запрос не будет компилироваться:

1
2
3
4
5
6
7
8
// IN list with wrong types
AUTHOR.ID.in("a", "b", "c");
//           ^^^^^^^^^^^^^ This in() method expects an Integer... argument
 
// IN with subquery returning wrong type
AUTHOR.ID.in(select(BOOK.TITLE).from(BOOK))
//           ^^^^^^^^^^^^^^^^^^ This in() method expects a   Select<? extends Record1<Integer>>
//

Кроме того, следующий (недопустимый) оператор будет отклонен компилятором Java:

1
2
3
AUTHOR.ID.in(select(BOOK.AUTHOR_ID, BOOK.TITLE).from(BOOK))
//        ^^ This in() method expects a   Select<? extends Record1<Integer>>,
//           but instead, an incompatible Select<Record2<Integer, String>> was provided

5. Столбец выражений

В SQL вы можете создавать новые типы выражений столбцов, применяя к ним функции или операции. Например, вы можете объединить имена и фамилии авторов:

5.1. В SQL:

1
2
3
SELECT   author.first_name || ' ' || author.last_name
FROM     author
ORDER BY author.id

5.2. С JOOQ ::

1
2
3
4
dsl.select(concat(AUTHOR.FIRST_NAME, val(" "), AUTHOR.LAST_NAME))
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch()

Помните, что мы статически импортируем все из DSL , включая DSL.concat() и DSL.val() .

1
2
3
4
dsl.select(AUTHOR.FIRST_NAME.concat(" ").concat(AUTHOR.LAST_NAME))
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch()

Конечно, поскольку Java не допускает перегрузку операторов (или символьных имен методов), нам придется использовать обычные имена методов, в данном случае concat . Хотите ли вы использовать префиксную нотацию для функций или инфиксную нотацию для операторов, как правило, зависит от вас.

6. Больше информации о jOOQ DSL

В jOOQ DSL очень много функций. Перечисление всех функций в этом курсе будет повторять справочное руководство. Ознакомьтесь с разделом руководства по сборке SQL, чтобы узнать больше о jOOQ DSL.