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
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 ******************* @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.
JDBC, Spring, and Hibernate tutorials
- Spring and Hibernate simple JDBC example
- JDBC Tutorial with HSQLDB
- Spring SimpleJdbcCall to invoke stored procedures
Labels: Spring JDBC
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home