Как вызывать хранимые процедуры и функции Oracle с помощью JPA и Hibernate

Автор оригинала: Vlad Mihalcea.

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

Хранимые процедуры Oracle

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

Хранимая процедура Oracle, выводящая простое значение

CREATE OR REPLACE PROCEDURE count_comments (  
   postId IN NUMBER,  
   commentCount OUT NUMBER )  
AS  
BEGIN  
    SELECT COUNT(*) INTO commentCount  
    FROM post_comment  
    WHERE post_id = postId; 
END;

Эта хранимая процедура имеет два параметра: входной параметр (например, postID ) и выходной параметр (например, commentCount ), который используется для возврата количества записей post_comment , связанных с заданной post_id родительской строкой.

Для вызова этой хранимой процедуры можно использовать следующий синтаксис Java Persistence API 2.1:

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("count_comments")
.registerStoredProcedureParameter(
    1, 
    Long.class, 
    ParameterMode.IN
)
.registerStoredProcedureParameter(
    2, 
    Long.class, 
    ParameterMode.OUT
)
.setParameter(1, 1L);

query.execute();

Long commentCount = (Long) query.getOutputParameterValue(2);

По умолчанию базовый JDBC CallableStatement остается открытым даже после выполнения хранимой процедуры и извлечения параметров OUT или REF_CURSOR .

Чтобы явно закрыть CallableStatement , вам необходимо вызвать релиз для базового Вывода процедуры объекта, как показано в следующем примере:

  StoredProcedureQuery query = entityManager
  .createStoredProcedureQuery("count_comments")
  .registerStoredProcedureParameter(
      1, 
      Long.class, 
      ParameterMode.IN
  )
  .registerStoredProcedureParameter(
      2, 
      Long.class, 
      ParameterMode.OUT
  )
  .setParameter(1, 1L);
  
  try {
      query.execute();
      
      Long commentCount = (Long) query
      .getOutputParameterValue(2);
  
      assertEquals(Long.valueOf(2), commentCount);
  } finally {
      query.unwrap(ProcedureOutputs.class)
      .release();
  }

Для получения более подробной информации ознакомьтесь с этой статьей .

Хранимая процедура Oracle, выводящая SYS_REFCURSOR

Хранимая процедура также может определить КУРСОР SYS_REF выходной параметр, связанный с курсором базы данных, который может быть повторен для извлечения нескольких записей базы данных:

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS  
BEGIN 
    OPEN postComments FOR 
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

В Oracle эту хранимую процедуру можно вызвать с помощью синтаксиса JPA 2.1:

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("post_comments")
.registerStoredProcedureParameter(
    1, 
    Long.class, 
    ParameterMode.IN
)
.registerStoredProcedureParameter(
    2, 
    Class.class, 
    ParameterMode.REF_CURSOR
)
.setParameter(1, 1L);

query.execute();

List postComments = query.getResultList();

В отличие от предыдущего вызова хранимой процедуры, на этот раз мы используем getResultList() и получаем Объект [] , содержащий все значения столбцов, связанные с выбранными записями базы данных.

Hibernate уже давно предлагает свой собственный API хранимых процедур, и вызов вышеупомянутой хранимой процедуры можно выполнить следующим образом:

Session session = entityManager.unwrap(Session.class);
ProcedureCall call = session
.createStoredProcedureCall("post_comments");
    
call.registerParameter(
    1, Long.class, 
    ParameterMode.IN
)
.bindValue(1L);
    
call.registerParameter(
    2, 
    Class.class, 
    ParameterMode.REF_CURSOR
);

Output output = call.getOutputs().getCurrent();
if (output.isResultSet()) {
    List postComments = ((ResultSetOutput) output)
    .getResultList();
}

Функции Oracle

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

Функция Oracle, возвращающая простое значение

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

CREATE OR REPLACE FUNCTION fn_count_comments ( 
    postId IN NUMBER ) 
    RETURN NUMBER 
IS 
    commentCount NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment 
    WHERE post_id = postId; 
    RETURN( commentCount ); 
END;

К сожалению, на момент написания (Hibernate 5.1.0) как хранимая процедура Java Persistence 2.1, так и API, специфичный для Hibernate, не могут использоваться для вызова функций. Однако для этого ограничения существует несколько обходных путей.

Во-первых, мы можем просто вызвать функцию Oracle, как и любой другой SQL-запрос:

BigDecimal commentCount = (BigDecimal) entityManager
    .createNativeQuery(
        "SELECT fn_count_comments(:postId) FROM DUAL"
    )
    .setParameter("postId", 1L)
    .getSingleResult();

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

Session session = entityManager.unwrap( Session.class );

Integer commentCount = session.doReturningWork( 
    connection -> {
    try (CallableStatement function = connection
        .prepareCall(
            "{ ? = call fn_count_comments(?) }" )) {
        function.registerOutParameter( 1, Types.INTEGER );
        function.setInt( 2, 1 );
        function.execute();
        return function.getInt( 1 );
    }
} );

Функция Oracle, возвращающая SYS_REFCURSOR

Как и в случае с хранимыми процедурами, функция Oracle также может возвращать SYS_REFCURSOR , и, чтобы сделать пример еще более интересным, мы собираемся извлечь Сообщение вместе с соответствующими последующими дочерними сущностями.

Функция Oracle выглядит следующим образом:

CREATE OR REPLACE FUNCTION fn_post_and_comments ( 
    postId IN NUMBER ) 
    RETURN SYS_REFCURSOR 
IS 
    postAndComments SYS_REFCURSOR; 
BEGIN 
   OPEN postAndComments FOR 
        SELECT 
            p.id AS "p.id", 
            p.title AS "p.title", 
            p.version AS "p.version", 
            c.id AS "c.id", 
            c.post_id AS "c.post_id", 
            c.version AS "c.version", 
            c.review AS "c.review" 
       FROM post p 
       JOIN post_comment c ON p.id = c.post_id 
       WHERE p.id = postId; 
   RETURN postAndComments; 
END;

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

Сопоставление NamedNativeQuery выглядит следующим образом:

@NamedNativeQuery(
    name = "fn_post_and_comments",
    query = "{ ? = call fn_post_and_comments( ? ) }",
    callable = true,
    resultSetMapping = "post_and_comments"
)
@SqlResultSetMapping(
    name = "post_and_comments",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult( 
                    name = "id", 
                    column = "p.id" 
                ),
                @FieldResult( 
                    name = "title", 
                    column = "p.title" 
                ),
                @FieldResult( 
                    name = "version", 
                    column = "p.version" 
                ),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult( 
                    name = "id", 
                    column = "c.id" 
                ),
                @FieldResult( 
                    name = "post", 
                    column = "c.post_id" 
                ),
                @FieldResult( 
                    name = "version", 
                    column = "c.version" 
                ),
                @FieldResult( 
                    name = "review", 
                    column = "c.review" 
                ),
            }
        )
    }
)

Как вы можете видеть, вам необходимо указать SqlResultSetMapping , если вы хотите возвращать сущности вместо объекта[] массива.

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

При наличии этих двух сопоставлений извлечение сущностей Post и Комментарий к сообщению выглядит следующим образом:

List postAndComments = entityManager
.createNamedQuery("fn_post_and_comments")
.setParameter(1, 1L)
.getResultList();
    
Object[] postAndComment = postAndComments.get(0);
Post post = (Post) postAndComment[0];
PostComment comment = (PostComment) postAndComment[1];

Вывод

Вызов хранимых процедур и функций совсем не сложен, но для этого требуется знать некоторые подробности о режиме гибернации и основных возможностях драйвера JDBC. Следующая статья посвящена вызову хранимых процедур и функций SQL Server , так что следите за обновлениями!