CODEWITHSUNDEEP
javamysqldatabasehibernatestored-procedures
Survivor - 2012
Survivor - 2012

Reputation: 290

How to call a MySQL stored procedure from Hibernate

I am trying to call a MySQL stored procedure from Java Application which uses MySQL. Below is the part in DAO where i used to call a stored procedure 'insertComm'

String opt="REFUND";
        Query query = this.getSession().createSQLQuery("CALL insertComm (:remitNo,     :opt)")
           .setParameter("remitNo", remitNo)
               .setParameter("opt", opt);
opt=query.toString();
hemappLogger.info(opt);

But as i query the database and check, the stored procedure hasn't been executed. The 'opt' value is shown as SQLQueryImpl(CALL insertComm (:remitNo, :opt))

The parameter is okay and application is not showing error also. I can't see what i missed.

Upvotes: 0

Views: 7973

Answers (3)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154170

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

If your stored procedure returns a REFCURSOR or a TABLE result:

CREATE PROCEDURE post_comments(IN postId INT) 
BEGIN
    SELECT *  
    FROM post_comment   
    WHERE post_id = postId;  
END

You need to call the stored procedure as follows:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("post_comments");
query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
 
query.setParameter(1, 1L);
 
List<Object[]> postComments = query.getResultList();

For database functions, that return the result set instead of placing it in an OUT variable:

CREATE FUNCTION fn_count_comments(postId integer)
RETURNS integer
DETERMINISTIC 
READS SQL DATA 
BEGIN
    DECLARE commentCount integer; 
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment  
    WHERE post_comment.post_id = postId; 
    RETURN commentCount; 
END

The Hibernate 4.x and 5.x API will not help you, and you have to use JDBC instead:

int commentCount = session.doReturningWork(connection -> {
    try (CallableStatement function = connection.prepareCall(
            "{ ? = call fn_count_comments(?) }")) {
        function.registerOutParameter(1, Types.INTEGER);
        function.setInt(2, 1);
        function.execute();
        return function.getInt(1);
    }
});

Upvotes: 3

shazin
shazin

Reputation: 21923

Unfortunately you can't call a Stored Procedure using Session.createSQLQuery(). As the name suggests it allows to create a SQL Query. A procedure call is not a query.

But fear not, the work around is this.

Connection conn = getSession().connection();
CallableStatment stat = conn.prepareCall("{CALL insertComm (?,?)}");
stat.setString(1, remitNo); // Assuming both parameters are String
stat.setString(2, opt);

stat.executeUpdate();
stat.close();

Upvotes: 2

Kanagaraj M
Kanagaraj M

Reputation: 966

You didn't add Entity to your session object... .addEntity(classname.class).setParameter()

Upvotes: 1

Related Questions