How can we call a stored procedure with Hibernate and JPA?

Question

How can we call a stored procedure using Hibernate or JPA?

Answer 1

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:

• How to call Oracle stored procedures and functions with JPA and Hibernate
• How to call SQL Server stored procedures and functions with JPA and Hibernate
• How to call PostgreSQL functions (stored procedures) with JPA and Hibernate
• How to call MySQL stored procedures and functions with JPA and Hibernate