Эта статья является частью нашего академического курса под названием 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. Выполнение первого запроса
В приведенном выше примере были сделаны несколько простых предположений, и эти предположения будут сделаны на протяжении всего курса:
- Всякий раз, когда вы видите отдельное ключевое слово SQL, оно, вероятно, статически импортируется из
org.jooq.impl.DSL
- Всякий раз, когда вы видите отдельную ссылку на таблицу, она, вероятно, была статически импортирована из сгенерированного класса
Tables
Другими словами, в каждом классе, который использует 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.