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

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

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

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

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

CREATE PROCEDURE count_comments 
   @postId INT, 
   @commentCount INT OUTPUT 
AS 
BEGIN 
   SELECT @commentCount = COUNT(*)  
   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(
    "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();
  }
  

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

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

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

CREATE PROCEDURE post_comments 
    @postId INT, 
    @postComments CURSOR VARYING OUTPUT 
AS 
    SET NOCOUNT ON; 
    SET @postComments = CURSOR 
    FORWARD_ONLY STATIC FOR 
        SELECT *  
        FROM post_comment   
        WHERE post_id = @postId;  
    OPEN @postComments;

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

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: Dialect [org.hibernate.dialect.SQLServer2012Dialect] not known to support REF_CURSOR parameters

Несмотря на то , что эта хранимая процедура правильно работает в Oracle и PostgreSQL , на SQL Server она не работает, поскольку драйвер JDBC 4.2 Microsoft SQL Server не поддерживает эту функцию, как четко указано в документации драйвера .

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

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

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

CREATE FUNCTION fn_count_comments (@postId INT)
RETURNS INT  
AS  
BEGIN  
    DECLARE @commentCount int;  
    SELECT @commentCount = COUNT(*) 
    FROM post_comment   
    WHERE post_id = @postId;  
    RETURN(@commentCount);  
END;

К сожалению, на момент написания (Hibernate 5.1.0) как хранимая процедура 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. Следующая статья посвящена вызову хранимых процедур и функций PostgreSQL , так что следите за обновлениями!