How to call a MySQL stored procedure with multiple OUT parameters using JPA and Hibernate?

Question

I have following Mysql stored procedure with only OUT parameters, How I can call this stored procedure through hibernate in with only OUT Parameters?

CREATE PROCEDURE DBNAME.getStatistics (OUT A BIGINT UNSIGNED, OUT B BIGINT UNSIGNED, OUT C BIGINT UNSIGNED)
    BEGIN

        SELECT count(*) into A   from  DBNAME.Table1 where id =0;
        SELECT count(*) into B   from DBNAME.Table2 where id>0 and id<4;
        SELECT count(*) into C   from  DBNAME.Table3 where id>4;
    END

EDIT

Following code I used in java to test the call of MySQL stored procedure (with only OUT Parameters) through hibernate(Java):

Session session = HibernateUtil.getSessionFactory().openSession();

Connection connection = session.connection();
CallableStatement callable = null;

try {

callable = connection.prepareCall("{Call DBNAME.getStatistics(?,?,?)}");

// three ?,?,? means three OUT parameter. 
// If any parameters is IN type of
// Lets say the first one then use : callable.setInt(1, 10);

    callable.registerOutParameter(1, Types.BIGINT);
    callable.registerOutParameter(2, Types.BIGINT);
    callable.registerOutParameter(3, Types.BIGINT);
    callable.executeUpdate();

    int value1 = callable.getInt(1);
    int value2 = callable.getInt(2);
    int value3 = callable.getInt(3);


    System.out.println(value1);
    System.out.println(value2);
    System.out.println(value3);
   } catch (SQLException e) {
       e.printStackTrace();
 }

score 0 · Answer 1 · answered Feb 07 '16 at 22:18

0

I don't think you can do this with Hibernate. I have seen this done before

Connection connection = session.connection();
CallableStatement callable = null;
callable = connection.prepareCall("execute [procedure] ?");
callable.registerOutParameter(1, Types.INTEGER);
callable.execute();
int id = callable.getInt(1);

Which has worked. See Retrieving a value from Stored Procedure using Native SQL Hibernate for more context.

answered Feb 07 '16 at 22:18

Amir Raminfar

33,777
7
93
123

Thanks Aamir, Yes, You are right, this approach worked, but this approach is not very clear with the given explanation, so I added the answer of my test code as complete code of the question I asked above. – Sumit Arora Feb 09 '16 at 06:11

Vlad Mihalcea · Answer 2 · 2018-01-06T10:28:54.573

I wrote a very detailed article about how you can call MySQL stored procedures and database functions from Hibernate, but I'll write a short summary here as well.

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("getStatistics")
.registerStoredProcedureParameter("A", Long.class, ParameterMode.OUT)
.registerStoredProcedureParameter("B", Long.class, ParameterMode.OUT)
.registerStoredProcedureParameter("C", Long.class, ParameterMode.OUT);

query.execute();

Long a = (Long) query.getOutputParameterValue("A");
Long b = (Long) query.getOutputParameterValue("B");
Long c = (Long) query.getOutputParameterValue("C");

How to call a MySQL stored procedure with multiple OUT parameters using JPA and Hibernate?

2 Answers2