Survivor - 2012 Survivor - 2012 - 29 days ago 19
MySQL Question

How to call a MySQL stored procedure from Hibernate

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

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 db and check, the SP 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 cant see what i missed.

Answer

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();
Comments