Dec 5, 2013

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

    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;

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;

 public BigDecimal getCalculatedAvailableBalance(String accountCode) {
  SimpleJdbcCall call = new SimpleJdbcCall(

  // 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)

Q. How do you configure the  jdbcTemplate?

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="" xmlns:xsi=""
 xmlns:batch="" xmlns:p="" 
 <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 id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" lazy-init="true">
  <property name="dataSource" ref="myDataSource" />
 <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="myDataSource" />

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



Post a Comment

Subscribe to Post Comments [Atom]

<< Home