Hibernate and stored procedures examples

Hmm ... See section 16.2.2 in the Hibernate 3.2 Reference:
http://www.hibernate.org/hib_docs/reference/en/html/querysql.html

There are some database-specific rules that you need to follow for Oracle, Sybase, etc. But Hibernate will definitely allow you to use stored procedures for query. Since the SP call is represented as a named native query you can use the standard named query interface to get a Query object, set parameter values, and retrieve the result set. About the only thing I can think of that you /might/ not be able to do is use the paging methods (setFirstResult, setMaxResults) on it.

Jim

Hi,

Start off with  Named Query configuration

Sample code:
________________________________________
 
<?xml version="1.0" encoding="UTF-8"?>
 
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>  
  <sql-query name="selectPaymentMaster_SP" callable="true">
      <return alias="paymentMaster" class="com.gateway.payment.model.PaymentMaster">
            
      </return>
      { call OC_PAYMENT_METHOD_SEL(?,  PaymentTypeCode, :callingAPI) }
  </sql-query>
</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.
Sample code:
________________________________________
 
public List<PaymentMaster> search(PaymentMasterSearch paymentMasterSearchValue) throws PaymentException {
        LOGGER.debug(" $$$---Start of search  method in the  PaymentMasterService:");
        Session sessionObj;
        List<PaymentMaster> paymentMasterlist;
        try {
            if (paymentMasterSearchValue != null) {
                sessionObj = HibernateUtil.currentSession();
 
                String keyWord = paymentMasterSearchValue.getSearchKeyWord();
                paymentMasterlist = sessionObj.getNamedQuery("selectPaymentMaster_SP")
                        .setParameter("paymentTypeCode", keyWord)
                        .setParameter("callingAPI", "OC25")
                        .list();
                HibernateUtil.closeSession();
 
            } else {
                LOGGER
                        .debug(" $$$--Invalid payment master Search data.");
                throw new PaymentException(
                        "Invalid payment master Search data.");
            }
 
        } catch (Exception ex) {
            LOGGER.error("Could not find a payment master Record." , ex);
            throw new PaymentException("Error in  Proccessing the search method.",ex);
        }
        return paymentMasterlist;
    }

hope this is helps :-)