Announcement Announcement Module
No announcement yet.
Common approach to stored procedure Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Common approach to stored procedure

    What is the common approach to using a stored procedure within a spring & hibernate mix?

    This is what Im currently implementing and just wanted some general feedback:
    1) I implemented a class that extended the StoredProcedure class.

    2) I created a DAO that extends HibernateDaoSupport and inject the dao impl with a datasource object and session factory object. I do this because the class that implements the StoredProcedure class requires a datasource passed into the constructor, plus I didnt think I should call it directly from the service class.

    3) To call the stored procedure, I have a method in the DAO that will be called from within a service object.

    Is this a good approach?

  • #2
    This is indeed the best approach. One additional comment: make sure you flush the Hibernate session. This is required if you do updates or inserts in the transaction before you call the stored procedure that have to visible by the stored procedure. Without flush these changes will not be persisted to the database.


    • #3
      It'll be great if you can post some examples, like the code for your StoredProcedure class, the dao and a client.
      You should include them as best practices.



      • #4
        Hope this helps.

        Here is my class that extends the StoredProcedure class:

        public class CallGetPolicyNumber extends StoredProcedure {
            private static final Log log = LogFactory.getLog(CallGetPolicyNumber.class);
            private static final String STORED_PROC_NAME = "dbproc_get_policy_number";
            public CallGetPolicyNumber(DataSource ds) {
                super(ds, STORED_PROC_NAME);
                declareParameter(new SqlParameter("p_product_code", Types.VARCHAR));
                declareParameter(new SqlOutParameter("p_policy_number", Types.VARCHAR));
                declareParameter(new SqlParameter("p_link_type", Types.VARCHAR));
                declareParameter(new SqlParameter("p_agent_code", Types.VARCHAR));
                declareParameter(new SqlParameter("p_auth_number", Types.DECIMAL));
            public String execute(String productCode, String policyNumber,
                    String linkType, String partnerCode, BigDecimal authorization) {
                Map inParams = new HashMap(5);
                inParams.put("p_product_code", productCode);
                inParams.put("p_policy_number", policyNumber);
                inParams.put("p_link_type", linkType);
                inParams.put("p_agent_code", partnerCode);
                inParams.put("p_auth_number", authorization);
                Map outParams = execute(inParams);
                if (outParams.size() > 0) {
                    return outParams.get("p_policy_number").toString();
                } else {
                    return null;
        Here is my DAO class:
        public class HibPolicyDAO extends HibernateDaoSupport implements PolicyDAO {
            private static final Log log = LogFactory.getLog(HibPolicyDAO.class);
            private DataSource dataSource;
            public void setDataSource(DataSource dataSource) {
                this.dataSource = dataSource;
            public String getPolicyNumber(Policy policy)
                    throws UnableToRetrieveException {
                CallGetPolicyNumber proc = new CallGetPolicyNumber(dataSource);
                String policyNumber = proc.execute(policy.getProductCode(), policy
                        .getPolicyNumber(), null, policy.getPartner().getPartnerCode(),
                return policyNumber;
        Here is my service class:

        public class PolicyServiceImpl implements PolicyService {
            private static final Log log = LogFactory.getLog(PolicyServiceImpl.class);
            private PolicyDAO policyDAO;
            public void setPolicyDAO(PolicyDAO policyDAO) {
                this.policyDAO = policyDAO;
            public Policy processPolicy(Policy policy) throws PolicyException {
                // -------------------------------------------------
                // Get policy Number
                // -------------------------------------------------
                String policyNumber = "";
                try {
                    log.debug("Getting Policy Number");
                    policyNumber = policyDAO.getPolicyNumber(policy);
                } catch (UnableToRetrieveException e) {
                    log.error(e + " Policy [" + policy + "]");
                    throw new PolicyException("error.get.policy.number");
        ................ other business logic


        • #5
          awsome, thxs mate.


          • #6

            The example seems to provide a good understanding to call stored procs.

            I am trying to use the same method,but not able to provide right data source.

            Can you plz provide some inputs as to how i can get the datasource in the DAO.

            I'm using hibernate with spring in this case


            • #7
              I hope this could help you.
              By the way, I'm using the same ds that i'm using with hibernate


              * @@author christianspartano
              * Stored Procedure example
              public class SomeStoredProcedureextends StoredProcedure {
              public SomeStoredProcedure(DataSource dataSource ) {

              declareParameter(new SqlParameter("someparam", Types.INTEGER));

              public Map execute(Long placementId) {
              Map params = new HashMap();
              Map results = execute(params);
              return results;


              <!-- application ctx -->

              <!-- Sets the ds throw the constructor -->
              <bean id="someStoredProcedure" class="com.some.persistence.dao.storedprocedures.S omeStoredProcedure" >
              <constructor-arg><ref bean="datasource"/></constructor-arg>

              <!-- DS def -->
              <bean id="datasource" class="org.springframework.jndi.JndiObjectFactoryB ean">
              <property name="jndiName">

              After this I included the storedprocedure bean in a service delegate to keep the implementation (in my case I'm pushing data to a dw for reporting) independent of my business logic.

              <!-- ########################################## Delegate ######################################## -->
              <!-- Services Delegate -->
              <bean id="servicesDelegate" parent="txProxyTemplate">
              <property name="target">
              <bean class="com.some.control.implementation.ServicesDel egateImpl">
              <property name="mailSender"><ref bean="mailSender"/></property>
              <property name="someStoredProcedure"><ref bean="someStoredProcedure"/></property>
              <property name="transactionAttributes">
              key="callsomeStoredProceduree">PROPAGATION_REQUIRE D</prop>
              <prop key="*">PROPAGATION_SUPPORTS</prop>


              • #8
                We use a single 'storedProcedure' parent bean, and always have 2 out parameters for passing back the result -a numeric result and a string result.

                The StoredProcedureFactory class just extends the Spring CallableStatementCreatorFactory.

                <bean id="storedProcedure" class="" abstract="true"/>
                <bean id="myStoredProcSP" parent="storedProcedure">
                  <constructor-arg index="0">
                    <value>call oraclePackage.myStoredProc(?,?,?,?,?,?,?)</value>
                  <constructor-arg index="1">
                      <bean parent="inParamNumber">
                        <constructor-arg index="0">
                When calling the stored proc, we pass in a Map containing keys that match the above stored proc param names.

                This way, we just create the stored procedure in Oracle and map it in Spring -- with no additional Java code to write.

                An inParamNumber looks like this:

                <bean id="inParamNumber" class="org.springframework.jdbc.core.SqlParameter" abstract="true">
                  <constructor-arg index="1">
                    <bean class="org.springframework.beans.factory.config.FieldRetrievingFactoryBean">
                  <property name="staticField"><value>java.sql.Types.NUMERIC</value></property>
                This could have been done differently I guess, but we've got lots of stored procedures, having inherited them from a legacy system so it's useful not to have to write extra Java code.


                • #9
                  Stored procedures

                  Hello gmatthews,

                  Thanks for the example. but could you give more detail about the example like how and from where do you execute the procedure and where are the outputs from the procedure stored.

                  Thanks ..

                  Last edited by whitestone; Dec 20th, 2005, 07:27 AM.


                  • #10
                    Hi guys how to do a commit and rollback using StoredProcedure Class


                    I did exactly the same.(Which was mentioned above) ,
                    Works great .

                    But how do issue a commit after my sucessfully call to storedprocedure.



                    • #11
                      Check out TransactionFactoryProxyBean


                      If you declare one of these and base your business logic beans on it, Spring will take care of commit/rollback in a way similar to J2EE CMT.

                      Sounds like you should also read the manual. All of this stuff in is there.


                      • #12
                        Now, what if the StoredProcedure returns a "ResultSet" rather than just a single parameter. What changes are required to be done?


                        • #13
                          Correct me if i am wrong. To me it looks like the approach discussed above is the Spring way of invoking the StoredProcedures on the Persistance Layer. This approach can be implemented using either Hibernate2 or Hibernate3. But, it is said that Hibernate3 introduces special ways of handling StoredProcedures. I believe the approach discussed above does not take advantage of the support Hibernate3 has for StoredProcedures.

                          Look at this link.

                          What is the complete way of using StoredProcedures with Hibernate3 ?


                          • #14
                            I have been working with CallableStatementFactory and have come up with a similar approach to stored procedures and templatizing it. I'm pretty sure that this example doesn't use hibernate to access the stored proc, but it uses the data source directly.


                            • #15
                              Need help in handling cursor being returned

                              I have a oracle stored procedure that I am calling which returns a cursor
                              and I am doubtful about what to use when i use SqlOutParameter for declaring a Parameter in the constructor.

                              Any suggestions?