Рубрики
Без рубрики

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

Узнайте, как вызывать хранимые процедуры и функции при использовании PostgreSQL с JPA и Hibernate.

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

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

С точки зрения синтаксиса PostgreSQL поддерживает только ключевое слово FUNCTION. Однако функция PostgreSQL также может принимать параметр OUT, что означает, что функция PostgreSQL может вести себя так же, как и некоторые другие хранимые процедуры СУБД.

Функция PostgreSQL, выводящая простое значение

CREATE OR REPLACE FUNCTION count_comments( 
   IN postId bigint, 
   OUT commentCount bigint) 
   RETURNS bigint AS 
$BODY$ 
    BEGIN 
        SELECT COUNT(*) INTO commentCount 
        FROM post_comment  
        WHERE post_id = postId; 
    END; 
$BODY$ 
LANGUAGE plpgsql;

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

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

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

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

assertEquals(Long.valueOf(2), commentCount);

Стоит отметить, что в приведенном выше примере удалось извлечь результат из параметра OUT, что именно так работает хранимая процедура.

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

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

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

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

Тем не менее, та же функция PostgreSQL также может быть вызвана как функция SQL:

Session session = entityManager.unwrap(Session.class);
Long commentCount = session.doReturningWork(connection -> {
    try (CallableStatement function = connection
        .prepareCall(
            "{ ? = call count_comments(?) }" )) {
        function.registerOutParameter(1, Types.BIGINT);
        function.setLong(2, 1L);
        function.execute();
        return function.getLong(1);
    }
} );
assertEquals(Long.valueOf(2), commentCount);

При вызове функции PostgreSQL через API JDBC параметры должны указываться по индексу, а не по имени, так как в противном случае возникает следующее исключение:

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4CallableStatement
.registerOutParameter(String,int) is not yet implemented.

Функция PostgreSQL, выводящая РЕФКУРСОР

Функция также может определить УКАЗАТЕЛЬ ССЫЛКИ выходной параметр, связанный с курсором базы данных, который может быть повторен для извлечения нескольких записей базы данных:

CREATE OR REPLACE FUNCTION post_comments(postId BIGINT) 
   RETURNS REFCURSOR AS 
$BODY$ 
    DECLARE 
        postComments REFCURSOR; 
    BEGIN 
        OPEN postComments FOR  
            SELECT *  
            FROM post_comment   
            WHERE post_id = postId;  
        RETURN postComments; 
    END; 
$BODY$ 
LANGUAGE plpgsql

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

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

List postComments = query.getResultList();

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

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

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

ProcedureCall call = session
.createStoredProcedureCall("post_comments");

call.registerParameter(
    1, 
    void.class, 
    ParameterMode.REF_CURSOR
);

call.registerParameter(
    2, 
    Long.class, 
    ParameterMode.IN
)
.bindValue(1L);

Output output = call.getOutputs().getCurrent();

if (output.isResultSet()) {
    List postComments = ((ResultSetOutput) output)
    .getResultList();

    assertEquals(2, postComments.size());
}

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