Автор оригинала: 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
В отличие от предыдущего вызова функции, на этот раз мы используем 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
Вызов функций PostgreSQL совсем не сложен, но для этого требуется знать некоторые подробности о Hibernate и базовых возможностях драйвера JDBC. Следующая статья посвящена вызову хранимых процедур и функций MySQL, так что следите за обновлениями!