Приветствую!
Меня зовут Артём, я back-end разработчик на продукте WFM (Workforce Management). В компании наш продукт помогает в развитии процессов розничной сети, одна из основных наших задач — это построение рабочего графика для персонала розничного магазина.
В данной статье я хотел бы познакомить вас (в очередной раз, так как на хабре уже есть несколько статей, посвященных этой библиотеке) с Jooq и показать, как можно легко работать с хранимыми процедурами Oracle. Будет немного вводной части, а затем посмотрим примеры.
Почти в каждом продукте, скорее всего, есть база данных. В нашем случае это РСУБД Oracle, большая часть логики содержится внутри в виде хранимых процедур. Удобство взаимодействия с базой всегда было достаточно важной и щепетильной задачей для любого бэкендера, особенно, если речь идет о взаимодействии с хранимками, которые выдают и принимают "сложные" plsql-типы. Конвертация plsql-типа в java-тип — задачка не самая приятная, мягко говоря, и удобных инструментов в этом деле не так уж много.
Давайте кратко вспомним некоторые популярные подходы к взаимодействию с РСУБД, какими достоинствами и недостатками они обладают.
Подход | Плюсы | Минусы |
чистый JDBC | максимальный контроль над выполнением | очень трудоемкий подход корректность sql и обработки типов целиком на разработчике нет гарантий стыковки с контрактом БД (узнаем в runtime) |
JPA (orm-фреймворки) | набор api из коробки по работе с сущностями, гарантирующих корректность sql | потеря контроля исполнения sql нет гарантий стыковки с контрактом БД (узнаем в runtime) (исключение в случае, если используется code first подход и схема БД формируется по сущностям) |
Spring Data JDBC | достаточно простой и удобный api для выполнения sql по сравнению с чистым jdbc мы сами контролируем какой sql выполняется | корректность sql и обработки типов целиком на разработчике нет гарантий стыковки с контрактом БД (узнаем в runtime) |
В рамках данной статьи более корректно было бы сравнить подходы к работе с хранимыми процедурами и добавить в список Spring Simplejdbccall, но попробуем все же выделить преимущества Jooq в более широком смысле, нежели только работа с хранимками.
Как видно, инструментов предостаточно, зачем же нужна еще одна абстракция над БД?
Jooq – Java Object Oriented Querying
Что в основе?
Jooq generates Java code from your database and lets you build type safe SQL queries through its fluent API
Это основное отличие от других инструментов. Что же нам это дает? Фактически мы получаем сгенерированный клиент для взаимодействия с базой, как, например, клиенты, которые мы генерируем для wsdl-, openapi- спецификаций. Думаю, никто не станет отрицать удобство кодегенерации по контрактам.
Когда генерировать?
перегенерация всех классов при каждой сборке;
генерация классов только когда вам это нужно с хранением классов в репозитории.
Мы придерживаемся перегенерации классов при каждой сборке, в качестве проверки совместимости с контрактом базы данных.
Из чего генерировать?
по jpa-сущностям;
по sql-скриптам (liquibase, flyway);
по доступным объектам из БД.
В своем продукте мы генерируем классы по доступным объектам БД, то есть на этапе сборки происходит коннект к базе, считывание доступных объектов и генерация java-классов по ним.
Библиотека предоставляет исчерпывающий список поддерживаемых РСУБД. Нашей компании больше всего интересен Oracle, тут с поддержкой тоже всё хорошо.
Есть поддержка самой свежей версии 21с. Для работы с Oracle библиотека предоставляется только на коммерческой основе.
Давайте вспомним, в каком виде БД Oracle (и не только) может предоставить api для своих клиентов. Мы не будем рассматривать различные варианты, связанные с получением данных через http-протокол, хотя и такие возможности тоже есть, мы говорим про то, что в конечном счёте будет получено через jdbc. Глобально можно обозначить два подхода:
table/view — когда мы напрямую обращаемся к табличкам или вьюшкам;
stored procedure — вызов хранимых процедур.
Во втором подходе возможны различные варианты хранимых процедур:
возвращающие примитивные типы (число, строка, дата);
возвращающие «сложные» pl/sql объекты (object, record);
возвращающие sys_refcursor (нетипизированный курсор);
возвращающие refcursor (типизированный курсор);
возвращающие коллекции (в том числе pipeline-функции).
Конечно, процедуры могут не только отдавать данные, но и принимать их на вход, причем входные данные тоже могут быть сколько угодно "сложными" по своей структуре. В примерах мы посмотрим преимущественно чтение данных из базы.
Примеры
Самой важной частью примеров является показать простоту обращения к объектам базы, а также удобство и безопасность работы с типами, результаты вывода абсолютно не важны, но для наглядности кода я оставлю println. Показывая код сгенерированных классов, я буду оставлять только самую важную часть.
Начнем с создания объектов БД. Обычно у нас есть схема в базе, под которой ведется разработка (обзовем dev_user), и отдельная схема для приложения, которое ходит в базу с набором выданных грантов (обзовем app_user).
-- табличка откуда будем получать данные
create table demo_table
(
a number(2),
b varchar2(5)
);
-- какие-то записи в табличке
insert into demo_table(a, b) values (1, 'hello');
insert into demo_table(a, b) values (1, 'hell');
insert into demo_table(a, b) values (2, 'world');
-- view для инкапсуляции
create or replace view v_demo_table
as
select a, b from demo_table;
-- выдача грантов на view
grant read on v_demo_table to app_user;Я не буду подробно останавливаться на конфигурации приложения, оставлю лишь самые важные моменты. Основные зависимости:
<!--драйвер для работы с бд-->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc10</artifactId>
<version>${jooq.version}</version>
</dependency>
<!-- непосредственно сам jooq -->
<dependency>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq</artifactId>
<version>${jooq.version}</version>
</dependency>
<dependency>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq-meta</artifactId>
<version>${jooq.version}</version>
</dependency>
<dependency>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq-codegen</artifactId>
<version>${jooq.version}</version>
</dependency>
<!-- стартер для создания бинов jooq -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
<exclusions>
<exclusion>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
</exclusion>
</exclusions>
</dependency>Плагин, с помощью которого будем производить генерацию нужных классов по схеме БД:
конфиг плагина
<plugin>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${jooq.version}</version>
<executions>
<!-- Generate the required class from the database -->
<execution>
<id>generate-jooq</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<!-- сюда подставляются настройки соединения с бд -->
<jdbc>
<driver>${driver-class-name}</driver>
<url>${url}</url>
<user>${username}</user>
<password>${password}</password>
</jdbc>
<generator>
<database>
<name>${jooq.generator.db.dialect}</name>
<includes>.*</includes>
<inputSchema>${jooq.schema}</inputSchema>
</database>
<!-- Generate classes for tables and records -->
<generate>
<globalTableReferences>true</globalTableReferences>
<globalUDTReferences>false</globalUDTReferences>
<fluentSetters>true</fluentSetters>
<javaTimeTypes>true</javaTimeTypes>
</generate>
<!-- Configure the target package and directory -->
<target>
<packageName>ru.sportmaster.wfm.jooq.db</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>В общем-то, всё что нужно (дополнительно используется junit и spring boot test для прогона тестов). Прогонять примеры я буду в обычном тестовом классе. Попрошу spring boot предоставить мне все необходимое для этого.
@SpringBootTest
public class Demo {
/* основной бин из библиотеки jooq, который мы будем использовать для
обращения к бд */
@Autowired
Configuration conf;
}Давайте попробуем выполнить mvn clean compile (можно и напрямую дернуть плагин jooq-codeget, но я по привычке пойду сложным путём). Если всё было настроено корректно, то в логе мы увидим, как Jooq прицепился к базе, зачитал доступные объекты из словарей и сформировал лог о своей проделанной работе. В результате в папке target (ну или куда настроили) можно найти сгенерированные классы:
Там как раз наша вьюшка, к которой мы выдали гранты. Можно посмотреть, как выглядит класс :
VDemoTable
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class VDemoTable extends TableImpl<VDemoTableRecord> {
.......................
/**
* The column <code>V_DEMO_TABLE.A</code>.
*/
public final TableField<VDemoTableRecord, Byte> A = createField(DSL.name("A"), SQLDataType.TINYINT, this, "");
/**
* The column <code>V_DEMO_TABLE.B</code>.
*/
public final TableField<VDemoTableRecord, String> B = createField(DSL.name("B"), SQLDataType.VARCHAR(5), this, "");
........................
}
В нём есть то, что нас интересует, — сгенерированный маппинг полей базы в поля java-класса: поле a number(2) -> Byte, поле b varchar2(5) -> String. Дальше остается только зачитать данные:
Получим данные из view:
@Test
void getFromView() {
Result<VDemoTableRecord> result = conf.dsl()
.selectFrom(Tables.V_DEMO_TABLE)
.fetch();
result.forEach(record -> {
System.out.println(record.getA());
System.out.println(record.getB());
});
}Достаточно просто и что важно — нам не нужно думать о типах, обо всём позаботился Jooq.
Чуть более сложный пример с фильтрацией и группировкой:
@Test
void getViewGroupBy() {
Result<Record2<Byte, Integer>> agg = conf.dsl()
.select(Tables.V_DEMO_TABLE.A, count())
.from(Tables.V_DEMO_TABLE)
.where(Tables.V_DEMO_TABLE.A.eq((byte) 1))
.groupBy(Tables.V_DEMO_TABLE.A)
.fetch();
agg.forEach(record -> {
System.out.println(record.component1());
System.out.println(record.component2());
});
}Как видно, dsl Jooq очень похож на обычный sql. Record2<Byte, Integer> - в такую структуру Jooq запаковал строку результата, где Byte - это поле A, а Integer - count() по этому полю.
Перейдем к хранимым процедурам.
-- plsql хранимая функция
create or replace function get_num_value return number
as
begin
return 2;
end;
/
-- не забудем дать грант
grant execute on get_num_value to app_user;Запустим плагин jooq для перегенерации, получим:
Давайте посмотрим, что внутри:
GetNumValue
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetNumValue extends AbstractRoutine<BigDecimal> {
/**
* The parameter <code>GET_NUM_VALUE.RETURN_VALUE</code>.
*/
public static final Parameter<BigDecimal> RETURN_VALUE =
Internal.createParameter("RETURN_VALUE", SQLDataType.NUMERIC,
false, false);
..........
}Routines
/**
* Convenience access to all stored procedures and functions.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {
/**
* Call <code>GET_NUM_VALUE</code>
*/
public static BigDecimal getNumValue(
Configuration configuration
) {
GetNumValue f = new GetNumValue();
f.execute(configuration);
return f.getReturnValue();
}
.....
}Получение значения из функции:
@Test
void getNumValue() {
BigDecimal r = Routines.getNumValue(conf);
System.out.println(r);
}Очень просто — вызов метода у нужного класса, который нам подготовили (я использую глобальное пространство имён для процедур и функций — Routines) .
Далее поработаем с хранимыми процедурами/функциями внутри пакета (как правило, логика инкапсулируется в пакетах, нежели в отдельных функциях), полная спецификация пакета:
спецификация пакета
create or replace package demo_test_pkg is
-- коллекция целых чисел
type t_ints is table of integer;
-- рекорд
type t_rec is record
(
a number(2),
b varchar2(10 char)
);
-- курсор рекордов
type t_cur is ref cursor return t_rec;
-- коллекция рекордов
type t_tab is table of t_rec;
procedure get_min_max
(
p_ints in t_ints,
p_min out integer,
p_max out integer
);
function get_sys_refcursor return sys_refcursor;
function get_refcursor return t_cur;
function get_collection return t_tab;
function get_collection_pipe return t_tab pipelined;
end demo_test_pkg;
/
grant execute on demo_test_pkg to app_user;Запустим перегенерацию объектов. Смотрим:
Видим наш оракловый пакет в виде класса DemoTestPkg, все хранимые процедуры внутри и все plsql-типы в виде java-классов.
Передача массива на вход и получение двух значений на выход
@Test
void getMinMax() {
GetMinMax r = DemoTestPkg.getMinMax(
conf,
new TIntsRecord(
BigInteger.ONE,
BigInteger.TWO,
BigInteger.TEN
)
);
System.out.println(r.getPMin());
System.out.println(r.getPMax());
}Здесь TIntsRecord это сгенерированная обёртка для нашего типа в пакете бд:
-- коллекция целых чисел
type t_ints is table of integer;GetMinMax.java
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetMinMax extends AbstractRoutine<java.lang.Void> {
/**
* The parameter <code>DEMO_TEST_PKG.GET_MIN_MAX.P_MIN</code>.
*/
public static final Parameter<BigInteger> P_MIN = ...
/**
* The parameter <code>DEMO_TEST_PKG.GET_MIN_MAX.P_MAX</code>.
*/
public static final Parameter<BigInteger> P_MAX = ...Дальше попробуем почитать курсоры из базы. В Oracle курсоры могут быть типизированные (ref_cursor) и нетипизированные (sys_refcursor).
Получим sys_refcursor:
@Test
void getSysRefCursor() {
Result<Record> result = DemoTestPkg.getSysRefcursor(conf);
Integer r = result.get(0).get("A", Integer.class);
System.out.println(r);
}Как видим, тут дела обстоят не очень хорошо, что, в общем-то, и понятно, мы пытаемся получить курсор произвольной структуры, и тут Jooq ничем не можем нам помочь — мы не можем обратиться к возвращаемым полям, они просто не известны. В такое случае мы вынуждены "ручками" разбирать каждое поле - result.get(0).get("VAL", Integer.class). Подход не очень — удобство и безопасность работы с типами теряется.
Хорошо, что есть типизированный курсор, в спецификации пакета явно указано, какие поля он возвращает:
-- рекорд
type t_rec is record
(
a number(2),
b varchar2(10 char)
);
-- курсор рекордов
type t_cur is ref cursor return t_rec;Получим ref_cursor:
@Test
void getRefCursor() {
Result<Record> result = DemoTestPkg.getRefcursor(conf);
Integer r = result.get(0).get("A", Integer.class);
System.out.println(r);
}Но увы, и с ref_cursor дела обстоят аналогично как и с sys_refcursor — информации о возвращаемых полей нет, Record — ничем не типизирован. Баг это или фича? У меня, к сожалению, руки пока не дошли написать вопрос в поддержку (забегая вперед, скажу, что мы редко практикуем передачу курсора).
А вот с получением коллекции, типизированной тем же record-ом, дела обстоят намного лучше:
-- рекорд
type t_rec is record
(
a number(2),
b varchar2(10 char)
);
-- коллекция рекордов
type t_tab is table of t_rec;@Test
void getCollection() {
TTabRecord r = DemoTestPkg.getCollection(conf);
r.forEach(record -> {
System.out.println(record.getA());
System.out.println(record.getB());
});
}TTabRecord
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class TTabRecord extends ArrayRecordImpl<TRecRecord> { ....
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class TRecRecord extends UDTRecordImpl<TRecRecord> implements Record2<Byte, String> {
/**
* Getter for <code>DEMO_TEST_PKG.T_REC.A</code>.
*/
public Byte getA() {
return (Byte) get(0);
}
/**
* Getter for <code>DEMO_TEST_PKG.T_REC.B</code>.
*/
public String getB() {
return (String) get(1);
}
......Как видим, Jooq создал нам TTabRecord, по которому можно удобно пробегаться и обращаться к полям рекорда.
Мы в продукте как правило стараемся использовать получение коллекций через pipeline-функции — такой подход позволяет более бережно относиться к ресурсам БД, если коротко, то база будет отдавать результат по мере его формирования, а не накапливать целиком, как в прошлом примере.
С точки зрения приложения, вызов ничем не отличается от предыдущего примера:
@Test
void getCollectionPipe() {
TTabRecord r = DemoTestPkg.getCollectionPipe(conf);
r.forEach(record -> {
System.out.println(record.getA());
System.out.println(record.getB());
});
}Но отличия все же есть, их можно увидеть, если запустить наш код с флагом дебага Jooq:
-Dlogging.level.org.jooq=DEBUGВ случае получения коллекции из БД мы увидим:
В случае обращения к pipeline-функции:
То есть вызов pipeline-функции это фактически выражение select * from table(function) с фетчем результата. Кстати, это еще и удобно использовать при отладке непосредственно в самом oracle — заходим с свою любимую ide для plsql, выполняем select, смотрим результат.
Попробуем передать сложный объект уровня схемы и принять обратно коллекцию сложных объектов, идем в базу:
-- создадим объект, который потом станет вложенным в другой объект
create or replace type t_inner_obj as object
(
dt date,
num_val number,
str_val varchar2(100 char)
);
/
-- создадим объект, внутри которого будет t_inner_obj
create or replace type t_obj as object
(
dt_with_time timestamp,
f_obj t_inner_obj
);
-- завернем в коллекциию t_obj
create or replace type t_tab_obj is table of t_obj;
/
-- создадим следующую функцию (можно было положить и в пакет):
create or replace function get_collection_obj
(
p_inner_obj in t_inner_obj
) return t_tab_obj
as
v_res t_tab_obj;
begin
select
t_obj
(
dt_with_time => sysdate,
f_obj => p_inner_obj
)
bulk collect into
v_res
from
dual;
return v_res;
end;
/
-- раздадим гранты
grant execute on t_tab_obj to app_user;
grant execute on t_obj to app_user;
grant execute on t_inner_obj to app_user;
grant execute on get_collection_obj to app_user;Вызываем перегенерацию классов. Смотрим:
Функция появилась, все нужные типы созданы, остается только вызвать:
@Test
void getCollectionObj() {
TTabObjRecord r = Routines.getCollectionObj(
conf,
new TInnerObjRecord(LocalDate.now(), BigDecimal.ONE, "World!")
);
r.forEach(record -> {
System.out.println(record.getDtWithTime());
System.out.println(record.getFObj().getDt());
System.out.println(record.getFObj().getNumVal());
System.out.println(record.getFObj().getStrVal());
});
}Разработчик освобожден от написания мапперов. Как видим, вызов хранимых процедур становится достаточно простой задачей, а работа с типами превратилась в одно удовольствие :)
Заключение
Какие тут преимущества?
Простота интеграции в продукт.
Не мешает использовать рядом другие технологии для работы с БД.
Обширная, подробная документация.
Релизы раз в полгода + оперативные фиксы.
Быстрые ответы на stackoverflow от создателя библиотеки.
Поддержка scala, groovy, kotlin.
Кодогенерация для стыковки с контрактом БД (проверка на compile time).
Простота вызова хранимых процедур.
Когда стоит точно использовать?
Если на продукте часто приходится работать с хранимыми процедурами.
Если есть потребность в написании sql, которого нет «из коробки» в «jpa-фреймворках».
Конечно, это далеко не все преимущества и рекомендации к использованию, с полным списком возможностей лучше знакомиться на официальном сайте — https://www.jooq.org/
Спасибо за внимание!
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>techclub</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>techclub</name>
<description>techclub</description>
<properties>
<java.version>11</java.version>
<jooq.groupId>org.jooq.pro-java-11</jooq.groupId>
<jooq.version>3.15.0</jooq.version>
<jooq.generator.db.dialect>org.jooq.meta.oracle.OracleDatabase</jooq.generator.db.dialect>
<jooq.schema>dev_user</jooq.schema>
<!--db driver-->
<ojdbc10.version>19.7.0.0</ojdbc10.version>
<junit-jupiter>5.8.1</junit-jupiter>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>${junit-jupiter}</version>
<scope>test</scope>
</dependency>
<!--db access-->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc10</artifactId>
<version>${ojdbc10.version}</version>
</dependency>
<!-- jooq -->
<dependency>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq</artifactId>
<version>${jooq.version}</version>
</dependency>
<dependency>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq-meta</artifactId>
<version>${jooq.version}</version>
</dependency>
<dependency>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq-codegen</artifactId>
<version>${jooq.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
<exclusions>
<exclusion>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<!--считываем настройки-->
<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/application.properties</file>
</files>
</configuration>
</execution>
</executions>
</plugin>
<plugin>
<groupId>${jooq.groupId}</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${jooq.version}</version>
<executions>
<!-- Generate the required class from the database -->
<execution>
<id>generate-jooq</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<!-- Configure the database connection -->
<jdbc>
<driver>${driver-class-name}</driver>
<url>${url}</url>
<user>${username}</user>
<password>${password}</password>
</jdbc>
<generator>
<database>
<!-- <tableValuedFunctions>true</tableValuedFunctions>--> <!--?!-->
<!-- Configure the used database dialect -->
<name>${jooq.generator.db.dialect}</name>
<includes>.*</includes>
<inputSchema>${jooq.schema}</inputSchema>
</database>
<!-- Generate classes for tables and records -->
<generate>
<globalTableReferences>true</globalTableReferences>
<globalUDTReferences>false</globalUDTReferences>
<fluentSetters>true</fluentSetters>
<javaTimeTypes>true</javaTimeTypes>
</generate>
<!-- Configure the target package and directory -->
<target>
<packageName>ru.sportmaster.wfm.jooq.db</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>
</plugins>
</build>
</project>полный код класса Demo.java
package ru.techclub.jooq;
import org.jooq.Configuration;
import org.jooq.Record;
import org.jooq.Record2;
import org.jooq.Result;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import ru.sportmaster.wfm.jooq.db.Routines;
import ru.sportmaster.wfm.jooq.db.Tables;
import ru.sportmaster.wfm.jooq.db.packages.DemoTestPkg;
import ru.sportmaster.wfm.jooq.db.packages.demo_test_pkg.GetMinMax;
import ru.sportmaster.wfm.jooq.db.packages.demo_test_pkg.udt.records.TIntsRecord;
import ru.sportmaster.wfm.jooq.db.packages.demo_test_pkg.udt.records.TTabRecord;
import ru.sportmaster.wfm.jooq.db.tables.records.VDemoTableRecord;
import ru.sportmaster.wfm.jooq.db.udt.records.TInnerObjRecord;
import ru.sportmaster.wfm.jooq.db.udt.records.TTabObjRecord;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.LocalDate;
import static org.jooq.impl.DSL.count;
@SpringBootTest
public class Demo {
/* основной бин из библиотеки jooq, который мы будем использовать для
обращения к бд */
@Autowired
Configuration conf;
@Test
void getView() {
Result<VDemoTableRecord> result = conf.dsl().selectFrom(Tables.V_DEMO_TABLE).fetch();
result.forEach(record -> {
System.out.println(record.getA());
System.out.println(record.getB());
});
}
@Test
void getViewGroupBy() {
Result<Record2<Byte, Integer>> agg = conf.dsl()
.select(Tables.V_DEMO_TABLE.A, count())
.from(Tables.V_DEMO_TABLE)
.where(Tables.V_DEMO_TABLE.A.eq((byte) 1))
.groupBy(Tables.V_DEMO_TABLE.A)
.fetch();
agg.forEach(record -> {
System.out.println(record.component1());
System.out.println(record.component2());
});
}
@Test
void getNumValue() {
BigDecimal r = Routines.getNumValue(conf);
System.out.println(r);
}
@Test
void getMinMax() {
GetMinMax r = DemoTestPkg.getMinMax(
conf,
new TIntsRecord(BigInteger.ONE, BigInteger.TWO, BigInteger.TEN)
);
System.out.println(r.getPMin());
System.out.println(r.getPMax());
}
@Test
void getSysRefCursor() {
Result<Record> result = DemoTestPkg.getSysRefcursor(conf);
Integer r = result.get(0).get("A", Integer.class);
System.out.println(r);
}
@Test
void getRefCursor() {
Result<Record> result = DemoTestPkg.getRefcursor(conf);
Integer r = result.get(0).get("A", Integer.class);
System.out.println(r);
}
@Test
void getCollection() {
TTabRecord r = DemoTestPkg.getCollection(conf);
r.forEach(record -> {
System.out.println(record.getA());
System.out.println(record.getB());
});
}
@Test
void getCollectionPipe() {
TTabRecord r = DemoTestPkg.getCollectionPipe(conf);
r.forEach(record -> {
System.out.println(record.getA());
System.out.println(record.getB());
});
}
@Test
void getCollectionObj() {
TTabObjRecord r = Routines.getCollectionObj(
conf,
new TInnerObjRecord(LocalDate.now(), BigDecimal.ONE, "World!")
);
r.forEach(record -> {
System.out.println(record.getDtWithTime());
System.out.println(record.getFObj().getDt());
System.out.println(record.getFObj().getNumVal());
System.out.println(record.getFObj().getStrVal());
});
}
}
полный код скриптов в бд
create table demo_table
(
a number(2),
b varchar2(5)
);
insert into demo_table(a, b) values (1, 'hello');
insert into demo_table(a, b) values (1, 'hell');
insert into demo_table(a, b) values (2, 'world');
commit;
create or replace view v_demo_table
as
select * from demo_table;
grant read on v_demo_table to app_user;
-- функция возвращающая число
create or replace function get_num_value return number
as
begin
return 2;
end;
/
grant execute on get_num_value to app_user;
create or replace type t_inner_obj as object
(
dt date,
num_val number,
str_val varchar2(100 char)
);
/
create or replace type t_obj as object
(
dt_with_time timestamp,
f_obj t_inner_obj
);
create or replace type t_tab_obj is table of t_obj;
/
create or replace function get_collection_obj
(
p_inner_obj in t_inner_obj
) return t_tab_obj
as
v_res t_tab_obj;
begin
select
t_obj
(
dt_with_time => sysdate,
f_obj => p_inner_obj
)
bulk collect into
v_res
from
dual;
return v_res;
end;
/
grant execute on t_tab_obj to app_user;
grant execute on t_obj to app_user;
grant execute on t_inner_obj to app_user;
grant execute on get_collection_obj to app_user;
create or replace package demo_test_pkg is
-- коллекция целых чисел
type t_ints is table of integer;
-- рекорд
type t_rec is record
(
a number(2),
b varchar2(10 char)
);
-- курсор рекордов
type t_cur is ref cursor return t_rec;
-- коллекция рекордов
type t_tab is table of t_rec;
procedure get_min_max
(
p_ints in t_ints,
p_min out integer,
p_max out integer
);
function get_sys_refcursor return sys_refcursor;
function get_refcursor return t_cur;
function get_collection return t_tab;
function get_collection_pipe return t_tab pipelined;
end demo_test_pkg;
/
create or replace package body demo_test_pkg is
procedure get_min_max
(
p_ints in t_ints,
p_min out integer,
p_max out integer
)
as
begin
p_min := p_ints.first;
p_max := p_ints.first;
for i in p_ints.first .. p_ints.last loop
if p_ints(i) > p_max then
p_max := p_ints(i);
end if;
if p_ints(i) < p_min then
p_min := p_ints(i);
end if;
end loop;
end;
function get_sys_refcursor return sys_refcursor
as
v_res sys_refcursor;
begin
open v_res for select 1 as a, 'hello' as b from dual
union all select 2, 'world!' from dual;
return v_res;
end;
function get_refcursor return t_cur
as
v_res t_cur;
begin
open v_res for select 1 as a, 'hello' as b from dual
union all select 2, 'world!' from dual;
return v_res;
end;
function get_collection return t_tab
as
v_res t_tab;
begin
select q.* bulk collect into v_res from (select 1 as a, 'hello' as b from dual
union all select 2, 'world!' from dual) q;
return v_res;
end;
function get_collection_pipe return t_tab pipelined
as
begin
for i in (select 1 as a, 'hello' as b from dual
union all select 2, 'world!' from dual) loop
pipe row(i);
end loop;
end;
end demo_test_pkg;
/
grant execute on demo_test_pkg to app_user;