Feb 19, 2013

Spring JDBC Template examples -- calling stored proc, simple select, and insert with returning the generated key

Spring Interview Questions and Answers Q1 - Q14 are FAQs

Q1 - Q4 Overview & DIP Q5 - Q8 DI & IoC Q9 - Q10 Bean Scopes Q11 Packages Q12 Principle OCP Q14 AOP and interceptors
Q15 - Q16 Hibernate & Transaction Manager Q17 - Q20 Hibernate & JNDI Q21 - Q22 read properties Q23 - Q24 JMS & JNDI Q25 JDBC Q26 Spring MVC Q27 - Spring MVC Resolvers

Q24. How will you go about invoking stored procedures with Spring JDBC?  
A24. This post covers three typical scenarios of using the Spring JDBC template.   

1. Invoking a stored procedure to retrieve some results. This uses the JDBC Callable statement.
2. Retrieving the data from the database via a simple "SELECT" query.
3. Insert a new record into a table and then return the generated primary key.

Here is the sample code snippet to achieve the above requirements using the Spring framework.

package com.myapp.repository.impl;

import java.math.BigInteger;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

//...other imports

@Repository(value = "myAppDao")
public class MyAppDaoImpl implements MyAppDao {

 private static Logger logger = LoggerFactory.getLogger(MyAppDaoImpl.class);

 @Resource(name = "jdbcBasicTemplateSybase")
 private JdbcTemplate jdbcTemplateSybase;

 // ************ Retrieve data fromm a stored procedure  *******************
 public List<MyAppFeedResult> getMyAppFeedData(final MyAppFeedCriteria criteria) {
  SimpleJdbcCall call  = new SimpleJdbcCall(jdbcTemplateSybase)
  call = call.returningResultSet("my_app_proc_result", new RowMapper<MyAppFeedResult>() {
   public MyAppFeedResult mapRow(ResultSet rs, int rowNum) throws SQLException {
    MyAppFeedResult  record = new MyAppFeedResult();

    return record;

  //construct the stored proc input parameters      
  java.sql.Date valDate = new java.sql.Date(criteria.getValuationDate().getTime());
  java.sql.Date foreCastDateAsAtEndOf = null;
  java.sql.Date foreCastDate = null; 

  if (criteria.getForeCastAsAtEndOf() != null) foreCastDateAsAtEndOf = new java.sql.Date(criteria.getForeCastAsAtEndOf().getTime());
  if (criteria.getForeCastDate() != null) foreCastDate = new java.sql.Date(criteria.getForeCastDate().getTime());
  final MapSqlParameterSource params = new MapSqlParameterSource();
  params.addValue("PortfolioCd", criteria.getPortfolioCode());
  params.addValue("ForecastDate", foreCastDate);
  params.addValue("TranTypeDesc", criteria.getTranTypeDesc());
  params.addValue("Debug", "N");
  //execute the stored proc with the input parameters
  Map<String, Object> results = call.execute(params);
  //get the results
  List<MyAppFeedResult> resultList = (List<MyAppFeedResult>)results.get("my_app_proc_result");
  return resultList;
 /** Simple select query **/
 public List<MyAppAccount> getMyAppAccountRecords(ReconciliationCriteria criteria) 
  String sql = "Select MyAppId, PortfolioCd, AccountCd, CurrencyCd, ValuationDttm" +
                  "From MyApp " +
         "Where PortfolioCd = ? " +
         "And   InactiveFlag = 'N' " +
                  "Order by CurrencyCd, AccountCd";
  List<Object> parametersList = new ArrayList<Object>();

  Object[] parameters = parametersList.toArray(new Object[parametersList.size()]);

  List<MyAppAccount> parentList = jdbcTemplateSybase.query(sql, parameters, new RowMapper<MyAppAccount>() {
   public MyAppAccount mapRow(ResultSet rs, int rowNum) throws SQLException {
    MyAppAccount record = new MyAppAccount();

    return record;
  return parentList;

 /** insert a new record and get the generated primary key id**/
 public MyAppDetail addOrModifyAdjustment(MyAppDetail adjDetail) {
  if (adjDetail == null) {
   throw new RuntimeException("adjDetail is null");

  try {
   SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplateSybase).withTableName("MyAppdetail").usingGeneratedKeyColumns("MyAppDetailid");
   Map<String, Object> lParameters = new HashMap<String, Object>(20);
      lParameters.put("MyAppId", adjDetail.getMyAppId().longValue());
      lParameters.put("TranCd",  adjDetail.getTxnCd());
      lParameters.put("TranTypeCd", Integer.valueOf(adjDetail.getTxnTypeCd()));
      lParameters.put("TranTypeDesc",  adjDetail.getTxnTypeDesc());
   Number generatedKey = jdbcInsert.executeAndReturnKey(lParameters);"adjustment detail added with id = " + generatedKey.longValue());
  } catch (Exception e) {
   logger.error("Error saving MyApp transaction detail: ", e); 
   throw new RuntimeException(e);

  return adjDetail;

 //seter of the jdbcTemplate
 public void setJdbcTemplateSybase(JdbcTemplate jdbcTemplateSybase) {
  this.jdbcTemplateSybase = jdbcTemplateSybase;


Q. How will you process the results and return them as a Map?
A. Use the ResultSetExtractor class from Spring.

 public Map<String, BigDecimal> getAccountPVClosingBalances(PortfolioCriteria criteria) {
  String sql = "select accountcd, LiquidityLocal from portfolio p where p.portfoliocd = ? and   p.valuationdttm = ?  ";
  List<Object> parametersList = new ArrayList<Object>();

  //where clause prepared statement parameters
  Object[] parameters = parametersList.toArray(new Object[parametersList.size()]);

  //store results in a map
  Map<String, BigDecimal> results = jdbcTemplateSybase.query(sql, parameters, new ResultSetExtractor<Map<String, BigDecimal>>() {
   public Map<String, BigDecimal> extractData(ResultSet rs) throws SQLException {
    Map<String, BigDecimal> mapOfPortfolioBalances = new HashMap<String, BigDecimal>(100);
    while ( {
     String accounrCd = rs.getString("accountcd");
     BigDecimal portfolioBalance = rs.getBigDecimal("LiquidityLocal");
     mapOfPortfolioBalances.put(accounrCd, portfolioBalance);
    return mapOfPortfolioBalances;
  return results;

The "jdbcTemplateSybase" is configured and injected via the Spring dependency injection.

JDBC, Spring, and Hibernate tutorials



Post a Comment

Subscribe to Post Comments [Atom]

<< Home