Stored Procedures & Hibernate

This is a useful thread in Java Ranch about calling Stored Procedure from Hibernate. click here for details. I am posting part of the conversation which summarizes the topic.

First  post the Named Query configuration into the  HBM / Hibernate Mapping file

  1. <?xml version=“1.0” encoding=“UTF-8”?>
  2. <!DOCTYPE hibernate-mapping PUBLIC
  3. “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
  5. <hibernate-mapping>
  6. <sql-query name=“selectPaymentMaster_SP” callable=“true”>
  7. <return alias=“paymentMaster” class=“com.gateway.payment.model.PaymentMaster”>
  8. </return>
  9. { call OC_PAYMENT_METHOD_SEL(?,  😛     aymentTypeCode, :callingAPI) }
  10. </sql-query>
  11. </hibernate-mapping>

So that maps a Stored procedure called OC_PAYMENT_METHOD_SEL and the return type to map to the PaymentMaster DTO Java class that we have that actually maps to the PaymentMaster table in our database.

Now here is our Java code to call this stored procedure.

  1. public List<PaymentMaster> search(PaymentMasterSearch paymentMasterSearchValue) throws PaymentException {

  2. LOGGER.debug(” $$$—Start of search  method in the  PaymentMasterService:”);
  3. Session sessionObj;
  4. List<PaymentMaster> paymentMasterlist;
  5. try {
  6. if (paymentMasterSearchValue != null) {
  7. sessionObj = HibernateUtil.currentSession();
  8. String keyWord = paymentMasterSearchValue.getSearchKeyWord();
  9. paymentMasterlist = sessionObj.getNamedQuery(“selectPaymentMaster_SP”)
  10. .setParameter(“paymentTypeCode”, keyWord)
  11. .setParameter(“callingAPI”, “OC25”)
  12. .list();
  13. HibernateUtil.closeSession();
  14. else {
  15. LOGGER.debug(” $$$–Invalid payment master Search data.”);
  16. throw new PaymentException( “Invalid payment master Search data.”);
  17. }
  18. catch (Exception ex) {
  19. LOGGER.error(“Could not find a payment master Record.” , ex);
  20. throw new PaymentException(“Error in  Proccessing the search method.”,ex);
  21. }
  22. return paymentMasterlist;
  23. }

reference : How to create Stored Procedures in MySql

Leave a comment

Filed under DB, Hibernate, JDBC

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s