How to call a Stored Procedure from Hibernate having both IN and OUT parameters

Question

I want to call a Stored Procedure from Hibernate which returns an out value. Here is my Stored Procedure.

create procedure myProcedure
(  
in in_Id int,  
out out_Id int  
)  
begin  
...  
END;

I am trying this to call my procedure

Query query = session.createSQLQuery(  
"CALL myProcedure(:in_Id)")  
.setParameter("in_id", 123);   
//Not sure how to register out parameters...??      
List result = query.list();

I tried everything but no luck. Can you help me please? If i try the above it says:

Incorrect number of arguments for PROCEDURE myProcedure; expected 2, got 1

I tried to add an out parameter like

myProcedure(:out_id:in_Id)

but then it says

Not all named parameters have been set:

I don't know how out parameter will be set? Is it like the following?

.setParameter("out_id", ?);

Any help is appreciated :)

Answer 1

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

Answer 2

The easiest way to do that is return the out parameter as part of the returning parameters (relevant only if you have access to the store procedures).
jest add a store procedure like the following one

create procedure myProcedure_only_in_parms (
   in in_Id int)
begin
call myProcedure(in_id,@out_Id) ;
select @out_id
END;

after done that it quite simple to use it with Hibernet in the following way

Query query = session.createSQLQuery(
"CALL myProcedure_only_in_parms (:in_Id)")
.setParameter("in_id", 123);
List result = query.list();

The result contains the out parameter, if you want return multiply parameters you can add it by doing select @parm1,@parm2,... ,@parmn

Hope it helped