Common approach to stored procedure

[jakim8915]

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?

[devijvers]

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.

[cspartano]

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.

Regards

[jakim8915]

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));
        compile();
    }

    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(),
                policy.getAuthorization());
        
        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);
            policy.setPolicyNumber(policyNumber);
        } catch (UnableToRetrieveException e) {
            log.error(e + " Policy [" + policy + "]");
            throw new PolicyException("error.get.policy.number");
        }
     
................ other business logic

[cspartano]

awsome, thxs mate.
Cheers

[vkid]

Hi,

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

[cspartano]

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

Regards

/**
* @@author christianspartano
* Stored Procedure example
*/
public class SomeStoredProcedureextends StoredProcedure {
public SomeStoredProcedure(DataSource dataSource ) {
super(dataSource,"somesp");

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

public Map execute(Long placementId) {
Map params = new HashMap();
params.put("someparam",someparam);
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>
</bean>


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


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">
<props>
key="callsomeStoredProceduree">PROPAGATION_REQUIRE D</prop>
<prop key="*">PROPAGATION_SUPPORTS</prop>
</props>
</property>
</bean>

[gmatthews]

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="com.mycompany.dao.support.StoredProcedureFactory" abstract="true"/>

<bean id="myStoredProcSP" parent="storedProcedure">
  <constructor-arg index="0">
    <value>call oraclePackage.myStoredProc(?,?,?,?,?,?,?)</value>
  </constructor-arg>
  <constructor-arg index="1">
    <list>
      <bean parent="inParamNumber">
        <constructor-arg index="0">
          <value>someId</value>
        </constructor-arg>
      </bean>
      etc.

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>
    </bean>
  </constructor-arg>
</bean>

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.

[whitestone]

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 ..

regards

[acharyshiva]

Hi

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

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

Regards
Shiva