Статьи

Безболезненный доступ из Java к процедурам PL / SQL с помощью jOOQ

Март 13, 2019

PL / SQL — одна из таких вещей.

Большинство людей стараются держаться подальше от этого. Мало кто действительно любит это. У меня просто случился Стокгольмский синдром, так как я много работаю с банками.

Даже если синтаксис PL / SQL и инструментарий иногда напоминают мне о старых добрых временах…

«Фицджеральд, мы круиз для синяка». Я перемотаю ленту. - «У меня нет коровы, Лоуренс. Мы можем вставить новый картридж PL / SQL в любое время ». Изображение в свободном доступе

«Фицджеральд, мы едем за синяками». Я перемотаю ленту. — «Нет коровы, Лоуренс. Мы можем вставить новый картридж PL / SQL в любое время ».
Изображение в свободном доступе

… Я все еще считаю, что процедурный язык (ну, любой язык) в сочетании с SQL может творить чудеса с точки зрения производительности, производительности и выразительности.

В этой статье мы увидим позже, как мы можем добиться того же с SQL (и PL / SQL) в Java, используя jOOQ .

Но сначала немного истории…

Доступ к PL / SQL из Java

Одна из главных причин, почему разработчики Java, в частности, воздерживаются от написания своего собственного кода PL / SQL, заключается в том, что интерфейс между PL / SQL и Java — ojdbc — является серьезной проблемой. В следующих примерах мы увидим, как это происходит.

Предположим, мы работаем над портом Oracle популярной базы данных Sakila ( изначально созданной для MySQL ). Этот конкретный порт Sakila / Oracle был реализован DB Software Laboratory и опубликован под лицензией BSD.

Вот частичное представление этой базы данных Sakila.

ERD создан с помощью vertabelo.com - узнайте, как использовать Vertabelo с jOOQ

ERD создан с помощью vertabelo.com — узнайте, как использовать Vertabelo с jOOQ

Теперь, давайте предположим, что у нас есть API в базе данных, который не предоставляет вышеуказанную схему, но вместо этого предоставляет API PL / SQL . API может выглядеть примерно так:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE TYPE LANGUAGE_T AS OBJECT (
  language_id SMALLINT,
  name CHAR(20),
  last_update DATE
);
/
 
CREATE TYPE LANGUAGES_T AS TABLE OF LANGUAGE_T;
/
 
CREATE TYPE FILM_T AS OBJECT (
  film_id int,
  title VARCHAR(255),
  description CLOB,
  release_year VARCHAR(4),
  language LANGUAGE_T,
  original_language LANGUAGE_T,
  rental_duration SMALLINT,
  rental_rate DECIMAL(4,2),
  length SMALLINT,
  replacement_cost DECIMAL(5,2),
  rating VARCHAR(10),
  special_features VARCHAR(100),
  last_update DATE
);
/
 
CREATE TYPE FILMS_T AS TABLE OF FILM_T;
/
 
CREATE TYPE ACTOR_T AS OBJECT (
  actor_id numeric,
  first_name VARCHAR(45),
  last_name VARCHAR(45),
  last_update DATE
);
/
 
CREATE TYPE ACTORS_T AS TABLE OF ACTOR_T;
/
 
CREATE TYPE CATEGORY_T AS OBJECT (
  category_id SMALLINT,
  name VARCHAR(25),
  last_update DATE
);
/
 
CREATE TYPE CATEGORIES_T AS TABLE OF CATEGORY_T;
/
 
CREATE TYPE FILM_INFO_T AS OBJECT (
  film FILM_T,
  actors ACTORS_T,
  categories CATEGORIES_T
);
/

Вы сразу заметите, что это, по сути, просто копия схемы 1: 1 в этом случае, смоделированная как типы Oracle SQL OBJECT и TABLE , за FILM_INFO_T типа FILM_INFO_T , который действует как агрегат.

Теперь наш администратор БД (или наш разработчик базы данных) реализовал следующий API для доступа к вышеуказанной информации:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PACKAGE RENTALS AS
  FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
  FUNCTION GET_ACTORS RETURN ACTORS_T;
  FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
  FUNCTION GET_FILMS RETURN FILMS_T;
  FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
  FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/

Дамы и господа, вот как вы можете …

… Утомительно получить доступ к PL / SQL API с помощью JDBC

Итак, чтобы избежать неуклюжего CallableStatement с его регистрацией параметров OUT и escape-синтаксисом JDBC, мы собираемся FILM_INFO_T запись FILM_INFO_T помощью оператора SQL, подобного следующему:

1
2
3
4
5
6
try (PreparedStatement stmt = conn.prepareStatement(
        "SELECT rentals.get_film_info(1) FROM DUAL");
     ResultSet rs = stmt.executeQuery()) {
 
    // STRUCT unnesting here...
}

Все идет нормально. К счастью, есть Java-попытка с ресурсами, чтобы помочь нам очистить эти многочисленные объекты JDBC. Теперь, как действовать? Что мы получим от этого ResultSet ? java.sql.Struct :

1
2
3
4
5
while (rs.next()) {
    Struct film_info_t = (Struct) rs.getObject(1);
 
    // And so on...
}

Теперь самые смелые из вас продолжат java.sql.Struct до еще более неясного и загадочного oracle.sql.STRUCT , который почти не содержит Javadoc, но содержит множество устаревших дополнительных, специфичных для поставщика методов.

А пока давайте придерживаться «стандартного API».

Interlude:

Давайте на минутку оценим JDBC во времена Java 8.

Когда появилась Java 5, были и дженерики. Мы переписали наши большие базы кода, чтобы убрать все виды бессмысленных приведений типового образца, которые больше не нужны. За исключением JDBC. Когда дело доходит до JDBC, угадывать подходящие типы — дело удачи. Мы обращаемся к сложным вложенным структурам данных, предоставляемым внешними системами, путем разыменования элементов по индексу, а затем даем необдуманные предположения о результирующих типах данных.

Лямбды только что были представлены, но JDBC все еще общается с мэйнфреймами.

Ронда сказала, что она поместила эту СТРУКТУРУ прямо между разъемами 73 и 75 на массиве F-B4. Интересно, нужен ли мне конвертер переменного тока в постоянный, чтобы подключить его?

Ронда сказала, что она поместила эту СТРУКТУРУ прямо между разъемами 73 и 75 на массиве F-B4. Интересно, нужен ли мне конвертер переменного тока в постоянный для его подключения?
Изображение в свободном доступе

А потом…

И здесь будут драконы. И СТРУКТЫ Оригинальное изображение в свободном доступе

И здесь будут драконы. И СТРУКТЫ
Исходное изображение в свободном доступе

Хорошо, достаточно этих рантов.

Давайте продолжим навигацию по нашей STRUCT

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
while (rs.next()) {
    Struct film_info_t = (Struct) rs.getObject(1);
 
    Struct film_t = (Struct) film_info_t.getAttributes()[0];
    String title = (String) film_t.getAttributes()[1];
    Clob description_clob = (Clob) film_t.getAttributes()[2];
    String description = description_clob.getSubString(1, (int) description_clob.length());
 
    Struct language_t = (Struct) film_t.getAttributes()[4];
    String language = (String) language_t.getAttributes()[1];
 
    System.out.println("Film       : " + title);
    System.out.println("Description: " + description);
    System.out.println("Language   : " + language);
}

Исходя из начального STRUCT который мы получили в позиции 1 из ResultSet , мы можем продолжить разыменование атрибутов по индексу. К сожалению, нам постоянно нужно искать тип SQL в Oracle (или в некоторой документации), чтобы запомнить порядок атрибутов:

1
2
3
4
5
6
CREATE TYPE FILM_INFO_T AS OBJECT (
  film FILM_T,
  actors ACTORS_T,
  categories CATEGORIES_T
);
/

И это еще не все! Первый атрибут типа FILM_T — это еще один вложенный STRUCT . А потом, эти ужасные CLOB s. Приведенный выше код не является строго полным. В некоторых случаях, которые могут понять только сопровождающие JDBC, java.sql.Clob.free() должен быть вызван, чтобы убедиться, что ресурсы высвобождаются во времени. Помните, что CLOB , в зависимости от вашей базы данных и конфигурации драйвера, может находиться за пределами вашей транзакции.

К сожалению, метод вызывается free() вместо AutoCloseable.close() , поэтому использовать try-with-resources невозможно. Итак, поехали:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
List<Clob> clobs = new ArrayList<>();
 
while (rs.next()) {
    try {
        Struct film_info_t = (Struct) rs.getObject(1);
        Struct film_t = (Struct) film_info_t.getAttributes()[0];
 
        String title = (String) film_t.getAttributes()[1];
        Clob description_clob = (Clob) film_t.getAttributes()[2];
        String description = description_clob.getSubString(1, (int) description_clob.length());
 
        Struct language_t = (Struct) film_t.getAttributes()[4];
        String language = (String) language_t.getAttributes()[1];
 
        System.out.println("Film       : " + title);
        System.out.println("Description: " + description);
        System.out.println("Language   : " + language);
    }
    finally {
        // And don't think you can call this early, either
        // The internal specifics are mysterious!
        for (Clob clob : clobs)
            clob.free();
    }
}

Вот и все. Теперь мы нашли небольшой вывод на консоль:

1
2
3
4
5
Film       : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad
             Scientist who must Battle a Teacher in
             The Canadian Rockies
Language   : English

Вот и все — вы можете подумать! Но…

Боль только началась

… потому что мы еще не закончили. Есть также два типа вложенных таблиц, которые нам необходимо десериализовать из STRUCT . Если вы еще не java.sql.Array меня, хорошие новости близки), вам понравится читать о том, как получить и раскрутить java.sql.Array . Давайте продолжим сразу после печати фильма:

1
2
Array actors_t = (Array) film_info_t.getAttributes()[1];
Array categories_t = (Array) film_info_t.getAttributes()[2];

Опять же, мы обращаемся к атрибутам по индексам, которые мы должны помнить и которые могут легко сломаться. Массив ACTORS_T — это не что иное, как еще один упакованный STRUCT :

01
02
03
04
05
06
07
08
09
10
System.out.println("Actors     : ");
 
Object[] actors = (Object[]) actors_t.getArray();
for (Object actor : actors) {
    Struct actor_t = (Struct) actor;
 
    System.out.println(
        "  " + actor_t.getAttributes()[1]
       + " " + actor_t.getAttributes()[2]);
}

Вы заметите несколько вещей:

  • Метод Array.getArray() возвращает массив. Но он объявляет возвращение Object . Надо вручную кастовать.
  • Мы не можем привести к Struct[] даже если это разумный тип. Но тип, возвращаемый ojdbc — это Object[] (содержащий элементы Struct )
  • Цикл foreach также не может разыменовать структуру с правой стороны. Там нет никакого способа заставить тип actor в то, что мы знаем, что это на самом деле
  • Мы могли бы использовать Java 8 и Streams и тому подобное, но, к сожалению, все лямбда-выражения, которые могут быть переданы в Streams API, не допускают выброс проверенных исключений . И JDBC бросает проверенные исключения. Это будет еще страшнее.

Так или иначе. Теперь, когда мы наконец достигли этого, мы можем увидеть вывод на печать:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
Film       : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad
             Scientist who must Battle a Teacher in
             The Canadian Rockies
Language   : English            
Actors     :
  PENELOPE GUINESS
  CHRISTIAN GABLE
  LUCILLE TRACY
  SANDRA PECK
  JOHNNY CAGE
  MENA TEMPLE
  WARREN NOLTE
  OPRAH KILMER
  ROCK DUKAKIS
  MARY KEITEL

Когда это безумие прекратится?

Это остановится прямо здесь!

До сих пор эта статья читалась как учебник (или, скорее, средневековая пытка) о том, как десериализовать вложенные пользовательские типы из Oracle SQL в Java (не заставляйте меня начинать их сериализацию снова!)

В следующем разделе мы увидим, как с помощью jOOQ и его генератора исходного кода можно без проблем реализовать ту же бизнес-логику (перечисляя Film с ID = 1 и ее участников). Проверь это:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// Simply call the packaged stored function from
// Java, and get a deserialised, type safe record
FilmInfoTRecord film_info_t = Rentals.getFilmInfo1(
    configuration, new BigInteger("1"));
 
// The generated record has getters (and setters)
// for type safe navigation of nested structures
FilmTRecord film_t = film_info_t.getFilm();
 
// In fact, all these types have generated getters:
System.out.println("Film       : " + film_t.getTitle());
System.out.println("Description: " + film_t.getDescription());
System.out.println("Language   : " + film_t.getLanguage().getName());
 
// Simply loop nested type safe array structures
System.out.println("Actors     : ");
for (ActorTRecord actor_t : film_info_t.getActors()) {
    System.out.println(
        "  " + actor_t.getFirstName()
       + " " + actor_t.getLastName());
}
 
System.out.println("Categories     : ");
for (CategoryTRecord category_t : film_info_t.getCategories()) {
    System.out.println(category_t.getName());
}

Это оно?

Да!

Вау, я имею в виду, что все эти типы / процедуры / функции PL / SQL на самом деле были частью Java. Все предостережения, которые мы видели ранее, скрыты за этими сгенерированными типами и реализованы в jOOQ , так что вы можете сосредоточиться на том, что изначально хотели сделать. Получите доступ к объектам данных и сделайте с ними значимую работу. Не сериализуйте / десериализуйте их!

Давайте на минутку оценим эту потребительскую рекламу:

jooq-The-лучший способ к записи-SQL-в-Java-маленький

Еще не убежден?

Я сказал вам не начинать сериализацию типов в JDBC. И я не буду, но вот как сериализовать типы в JOOQ, потому что это кусок пирога!

Давайте рассмотрим этот другой тип агрегата, который возвращает историю аренды клиента:

1
2
3
4
5
CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
  customer CUSTOMER_T,
  films FILMS_T
);
/

И полные спецификации пакета PL / SQL:

01
02
03
04
05
06
07
08
09
10
11
12
13
CREATE OR REPLACE PACKAGE RENTALS AS
  FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
  FUNCTION GET_ACTORS RETURN ACTORS_T;
  FUNCTION GET_CUSTOMER(p_customer_id INT) RETURN CUSTOMER_T;
  FUNCTION GET_CUSTOMERS RETURN CUSTOMERS_T;
  FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
  FUNCTION GET_FILMS RETURN FILMS_T;
  FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer_id INT) RETURN CUSTOMER_RENTAL_HISTORY_T;
  FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) RETURN CUSTOMER_RENTAL_HISTORY_T;
  FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
  FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/

Таким образом, при вызове RENTALS.GET_CUSTOMER_RENTAL_HISTORY мы можем найти все фильмы, которые когда-либо снимал клиент. Давайте сделаем это для всех клиентов, чье FIRST_NAME — «JAMIE», и на этот раз мы используем Java 8:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
// We call the stored function directly inline in
// a SQL statement
dsl().select(Rentals.getCustomer(
          CUSTOMER.CUSTOMER_ID
      ))
     .from(CUSTOMER)
     .where(CUSTOMER.FIRST_NAME.eq("JAMIE"))
 
// This returns Result<Record1<CustomerTRecord>>
// We unwrap the CustomerTRecord and consume
// the result with a lambda expression
     .fetch()
     .map(Record1::value1)
     .forEach(customer -> {
         System.out.println("Customer  : ");
         System.out.println("- Name    : "
           + customer.getFirstName()
           + " " + customer.getLastName());
         System.out.println("- E-Mail  : "
           + customer.getEmail());
         System.out.println("- Address : "
           + customer.getAddress().getAddress());
         System.out.println("            "
           + customer.getAddress().getPostalCode()
           + " " + customer.getAddress().getCity().getCity());
         System.out.println("            "
           + customer.getAddress().getCity().getCountry().getCountry());
 
// Now, lets send the customer over the wire again to
// call that other stored procedure, fetching his
// rental history:
         CustomerRentalHistoryTRecord history =
           Rentals.getCustomerRentalHistory2(dsl().configuration(), customer);
 
         System.out.println("  Customer Rental History : ");
         System.out.println("    Films                 : ");
 
         history.getFilms().forEach(film -> {
             System.out.println("      Film                : "
               + film.getTitle());
             System.out.println("        Language          : "
               + film.getLanguage().getName());
             System.out.println("        Description       : "
               + film.getDescription());
 
// And then, let's call again the first procedure
// in order to get a film's actors and categories
             FilmInfoTRecord info =
               Rentals.getFilmInfo2(dsl().configuration(), film);
 
             info.getActors().forEach(actor -> {
                 System.out.println("          Actor           : "
                   + actor.getFirstName() + " " + actor.getLastName());
             });
 
             info.getCategories().forEach(category -> {
                 System.out.println("          Category        : "
                   + category.getName());
             });
         });
     });

… И краткая выдержка из результатов, полученных выше:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Customer  :
- Name    : JAMIE RICE
- E-Mail  : JAMIE.RICE@sakilacustomer.org
- Address : 879 Newcastle Way
            90732 Sterling Heights
            United States
  Customer Rental History :
    Films                 :
      Film                : ALASKA PHANTOM
        Language          : English            
        Description       : A Fanciful Saga of a Hunter
                            And a Pastry Chef who must
                            Vanquish a Boy in Australia
          Actor           : VAL BOLGER
          Actor           : BURT POSEY
          Actor           : SIDNEY CROWE
          Actor           : SYLVESTER DERN
          Actor           : ALBERT JOHANSSON
          Actor           : GENE MCKELLEN
          Actor           : JEFF SILVERSTONE
          Category        : Music
      Film                : ALONE TRIP
        Language          : English            
        Description       : A Fast-Paced Character
                            Study of a Composer And a
                            Dog who must Outgun a Boat
                            in An Abandoned Fun House
          Actor           : ED CHASE
          Actor           : KARL BERRY
          Actor           : UMA WOOD
          Actor           : WOODY JOLIE
          Actor           : SPENCER DEPP
          Actor           : CHRIS DEPP
          Actor           : LAURENCE BULLOCK
          Actor           : RENEE BALL
          Category        : Music

Если вы используете Java и PL / SQL …

… Тогда вы должны нажать на баннер ниже и скачать бесплатную пробную версию прямо сейчас, чтобы экспериментировать с jOOQ и Oracle:

jooq-The-лучший способ к записи-SQL-в-Java-маленький

Порт Oracle базы данных Sakila доступен по этому URL-адресу бесплатно на условиях лицензии BSD:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/Sakila/oracle-sakila-db

Наконец, пришло время снова наслаждаться написанием PL / SQL!

Ссылка: Безболезненный доступ из Java к процедурам PL / SQL с jOOQ от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и AND JOOQ .
33
Share: