Spring SimpleJdbcCall to invoke stored procedures

Q. How will you use SimpleJdbcCall  to invoke a stored procedure for example in Sybase like

CREATE PROCEDURE calculate_avail_cash_balance
(
    @p_account_code       char(6),
    @p_avail_cash_bal     money   OUTPUT
)
AS

BEGIN
    DECLARE @avail_cash_holding money,
            @minimum_cash_req       money

 SELECT  @p_avail_cash_bal = 0;  
 --  some logic to calculate available balance 
 SELECT @p_avail_cash_bal = isnull(@avail_cash_holding,0) 
                             -  isnull(@minimum_cash_req,0)  

 if(@p_avail_cash_bal < 0)
    SELECT @p_avail_cash_bal = 0.0;
END 

So, calculate the available cash balance for a given account code.

A. Here is a sample DAO class that shows SimpleJdbcCall in action.

package com.mayapp.dao;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlInOutParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;


public class TradeDaoImpl implements TradeDao {

    @Resource(name = "myJdbcTemplate")
 private JdbcTemplate myJdbcTemplate;

    @Override
 public BigDecimal getCalculatedAvailableBalance(String accountCode) {
  SimpleJdbcCall call = new SimpleJdbcCall(
     myJdbcTemplate
)
    .withProcedureName("calculate_avail_cash_balance");

  // required to fix rounding issue
  call.addDeclaredParameter(new SqlInOutParameter("p_avail_cash_bal", java.sql.Types.DOUBLE));

  final MapSqlParameterSource params = new MapSqlParameterSource();
  params.addValue("p_account_code", accountCode);
  

  // execute the stored proc with the input parameters
  Map<String, Object> results = call.execute(params);

  Double calcAvailCashBalance = (Double) results.get("p_avail_cash_bal");

  return new BigDecimal(calcAvailCashBalance);
 }
}

If you need to provide catalog and schema values then

SimpleJdbcCall call = new SimpleJdbcCall(myJdbcTemplate)
          .withCatalogName("my_catalog")
          .withSchemaName("dbo")
   .withProcedureName("calculate_avail_cash_balance");

Q. How do you configure the  jdbcTemplate?
A.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:batch="http://www.springframework.org/schema/batch" xmlns:p="http://www.springframework.org/schema/p" 
 xmlns:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
  http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-2.0.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
  
 <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDriver" />
  <property name="url" value="jdbc:sybase:Tds:server:7777/mydb" />
  <property name="username" value="test" />
  <property name="password" value="test" />
 </bean>
 
 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" lazy-init="true">
  <property name="dataSource" ref="myDataSource" />
 </bean>
 
 <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="myDataSource" />
 </bean>   
  
</beans>

If you have return values from stored proc that has to be recursively processed then look at
Spring JDBC Template examples -- calling stored proc, simple select, and insert with returning the generated key