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

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

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

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

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

CREATE PROCEDURE count_comments (
   IN postId INT, 
   OUT commentCount INT 
) 
BEGIN 
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment  
    WHERE post_comment.post_id = postId; 
END

Эта хранимая процедура имеет два параметра: входной параметр (например, 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");

По умолчанию базовый 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();
  }
  

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

Хранимая процедура MySQL, выводящая РЕФКУРСОР

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

CREATE PROCEDURE post_comments(IN postId INT) 
BEGIN 
    SELECT *  
    FROM post_comment   
    WHERE post_id = postId;  
END

При попытке вызвать эту хранимую процедуру:

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();

Режим гибернации вызывает следующее исключение:

org.hibernate.QueryException: java.lang.IllegalArgumentException: org.hibernate.QueryException: Dialect [org.hibernate.dialect.MySQL57InnoDBDialect] not known to support REF_CURSOR parameters

Несмотря на то , что эта хранимая процедура правильно работает в Oracle и PostgreSQL, в MySQL она не работает, потому что драйвер MySQL не поддерживает РЕФКУРСОР вне хранимой процедуры.

Однако вместо использования параметра REFCURSOR вы можете просто использовать возвращенный Набор результатов :

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("post_comments");

query.registerStoredProcedureParameter(
    1, 
    Long.class, 
    ParameterMode.IN
);

query.setParameter(1, 1L);

List postComments = query.getResultList();

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

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

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

CREATE FUNCTION fn_count_comments(postId integer)
RETURNS integer 
DETERMINISTIC 
READS SQL DATA 
BEGIN 
    DECLARE commentCount integer; 
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment  
    WHERE post_comment.post_id = postId; 
    RETURN commentCount; 
END

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

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

int 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);
    }
});

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