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

This blog 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  *******************
 
 @Override
 public List<MyAppFeedResult> getMyAppFeedData(final MyAppFeedCriteria criteria) {
  SimpleJdbcCall call  = new SimpleJdbcCall(jdbcTemplateSybase)
                          .withProcedureName("ProcGetMyAppFeed");
  
  call = call.returningResultSet("my_app_proc_result", new RowMapper<MyAppFeedResult>() {
   public MyAppFeedResult mapRow(ResultSet rs, int rowNum) throws SQLException {
    MyAppFeedResult  record = new MyAppFeedResult();

    record.setPortfolioCode(criteria.getPortfolioCode());
    record.setValuationDate(criteria.getValuationDate());
    record.setAccountcd(rs.getString("accountCd"));
    record.setPositionIndicator(rs.getString("PositionIndicator"));
    record.setAmount(rs.getBigDecimal("amount"));
    record.setSecurityIdentifier(rs.getString("securityIdentifier"));
    record.setCurrencyCode(rs.getString("currencyCd"));
    record.setUnitCost(rs.getBigDecimal("unitCost"));
    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("ValuationDttm",valDate);
  params.addValue("ForeCastAsAtEndOf",foreCastDateAsAtEndOf);
  params.addValue("AccountCd",criteria.getAccountCode());
  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;
 }
 
 @Override
 /** 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>();
  parametersList.add(criteria.getPortfolioCode());
  parametersList.add(criteria.getValuationDate());

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

    record.setMyAppId(rs.getLong("MyAppId"));
    record.setPortfolioCode(rs.getString("portfolioCd"));
    record.setAccountCd(rs.getString("AccountCd"));
    record.setCurrencyCd(rs.getString("CurrencyCd"));
    record.setValuationDate(rs.getDate("ValuationDttm"));   
    return record;
   }
  });
  
  return parentList;
 }
 

 
 @Override
 /** 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);
   logger.info("adjustment detail added with id = " + generatedKey.longValue());
   
   adjDetail.setMyAppId(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.

    @Override
 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>();
  parametersList.add(criteria.getPortfolioCd()); 
  parametersList.add(criteria.getValuationDtTm());

  //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 (rs.next()) {
     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.