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

database, hibernate, java, mysql, stored-procedures

Solution

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

Problem

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 :)

Original source