Considering the following stored procedure that simply returns a basic return value:
CREATE OR REPLACE PROCEDURE count_comments (
postId IN NUMBER,
commentCount OUT NUMBER )
AS
BEGIN
SELECT COUNT(*) INTO commentCount
FROM post_comment
WHERE post_id = postId;
END;
You can call this one with standard JPA:
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("count_comments")
.registerStoredProcedureParameter(1, Long.class,
ParameterMode.IN)
.registerStoredProcedureParameter(2, Long.class,
ParameterMode.OUT)
.setParameter(1, 1L);
query.execute();
Long commentCount = (Long) query.getOutputParameterValue(2);
If the stored procedure returns a SYS_REFCURSOR:
CREATE OR REPLACE PROCEDURE post_comments (
postId IN NUMBER,
postComments OUT SYS_REFCURSOR )
AS
BEGIN
OPEN postComments FOR
SELECT *
FROM post_comment
WHERE post_id = postId;
END;
You can call it like this:
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<Object[]> postComments = query.getResultList();
If you want to call an Oracle database function:
CREATE OR REPLACE FUNCTION fn_count_comments (
postId IN NUMBER )
RETURN NUMBER
IS
commentCount NUMBER;
BEGIN
SELECT COUNT(*) INTO commentCount
FROM post_comment
WHERE post_id = postId;
RETURN( commentCount );
END;
You can't use the StoredProcedureQuery since it does not work with Hibernate 5, so you can call it like this:
BigDecimal commentCount = (BigDecimal) entityManager
.createNativeQuery(
"SELECT fn_count_comments(:postId) FROM DUAL"
)
.setParameter("postId", 1L)
.getSingleResult();
or with plain JDBC:
Session session = entityManager.unwrap( Session.class );
Integer 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 );
}
} );
For more details check out the following articles:
