Considering you have a simple stored procedure that outputs a basic type:
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
You can call this stored procedure using a JPA StoredProcedureQuery:
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");
If your stored procedure returns a REFCURSOR or a TABLE result:
CREATE PROCEDURE post_comments(IN postId INT)
BEGIN
SELECT *
FROM post_comment
WHERE post_id = postId;
END
You need to call the stored procedure as follows:
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("post_comments");
query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
query.setParameter(1, 1L);
List<Object[]> postComments = query.getResultList();
For database functions, that return the result set instead of placing it in an OUT variable:
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
The Hibernate 4.x and 5.x API will not help you, and you have to use JDBC instead:
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);
}
});
