How to retrieve output param from Stored Procedure with Hibernate

Question

I am trying to execute a Stored Procedure which updates a column and retrieves the filename from the same table after updating

StoredProcedure:

 CREATE DEFINER=`test`@`%` PROCEDURE `update_count`(
    IN in_testID                VARCHAR(64),
    OUT out_FileName        VARCHAR(100),
    OUT out_Message         VARCHAR(100))
BEGIN
    UPDATE files SET count=count+1 WHERE testID=in_testID;    
    SELECT FileName INTO out_FileName FROM files WHERE testID = in_testID;    
    SET out_Message = 'File updated uccessfully';
END

JavaCode to execute this StoredProcedure:

Query query = session.createSQLQuery("CALL update_count(:in_testID, @out_FileName, @out_Message)").addEntity(FilesBean.class)
.setParameter("in_testID",body.getTestId());
query.executeUpdate();

Updated the query.executeUpdate() with query.list(). But the line returning a error ResultSet is from UPDATE. No Data

I need to fix this with using the createSQLQuery

Answer 1

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). just add a store procedure like the following one

create procedure myProcedure_only_in_prams (
   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 Hibernate 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