How to call a PostgreSQL stored procedure with Hibernate

I want to create a Stored Procedure in PostgreSQL to make some calculation and return it to My java call. I have tried Most of the option from Java-Hibernate but not able to succeed. Below is my SP.

CREATE OR REPLACE FUNCTION "GET_REPORT"()
RETURNS refcursor AS
$BODY$DECLARE
      ref refcursor; 
BEGIN
OPEN ref FOR Select sum(balance),avg(balance) from sales;
RETURN ref;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION "GET_REPORT"()
OWNER TO postgres;

In Java How to call this procedure to get the values. NOTE: I’m not using hibernate XML files to call quires.

Please provide Java code(using Session) and any changes in procedure (if required).

Answer

You can use JPA StoredProcedureQuery for calling PostgreSQL stored procedures or functions

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");

Or, if you want to use the Hibernate Session:

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<Object[]> postComments = 
        ((ResultSetOutput) output).getResultList();
    assertEquals(2, postComments.size());
}

That’s it!