using stored procedure in hibernate 3

Question

Hello I am using hibernate + spring since 5 months but never used stored procedure in hibernate so please can anybody tell me how to call stored procedure from DB(MySQL)....

Answer 1

Spring has an StoredProcedure class that you can extend to call stored procedures.

class MyStoredProcedure extends StoredProcedure {
     public MyStoredProcedure(DataSource ds) {
          this.setDataSource(ds);
          this.setSql("store_procedure_name");
          this.declareParameter(new SqlParameter("name", Types.VARCHAR);
          this.compile();
     }

     public void callProcedure() {
          Map<string, String> inParams = new HashMap<String, String>();
          inParams.put("name", "taher");
          try {
               execute(inParams);
          } catch (DataAccessException dae) {
          }
     }
}

Answer 2

Hibernate defines stored procedure calls as a named query. The docs explain how to set this up in the Hibernate config.

From Spring, you can call a named query using the various HibernateTemplate.findByNamedQuery(...) methods.

Answer 3

Since you are already using Spring with Hibernate I would suggest using Spring classes. You can extend the StoredProcedure class mentioned above, there are other alternatives as well. If you have a basic stored procedure I would say the easiest way to call it would be to use Spring's SimpleJdbcCall class. The Spring documentation covers this class quite nicely with code snippets.