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.
解决方案
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();