How to create datasources with Spring and why favor JNDI?

Datasource is a name given to the connection set up to a database from a server. There are two ways to create datasources in Spring. Datasources are required to create JDBC templates. All non trivial applications need to connect to the databases. Datasources are also supplied to the hibernate session factories.

Method 1:Using Apache commons-dbcp package that has the org.apache.commons.dbcp.BasicDataSource class. The pom.xml file for maven should declare the dependency.

  <properties>
  <commons-dbcp.version>1.4</commons-dbcp.version>
 </properties>
 
    <dependencies>
  <dependency>
   <groupId>commons-dbcp</groupId>
   <artifactId>commons-dbcp</artifactId>
   <version>${commons-dbcp.version}</version>
  </dependency>
 </dependencies>

Next, is the Spring configuration file that uses the Apache datasource.

 <bean id="dataSource_sybase" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDriver" />
  <property name="url" value="jdbc:sybase:Tds:my_server:20215/my_schema" />
  <property name="username" value="user" />
  <property name="password" value="password" />
 </bean>

Method 2:  Using the JNDI to connect via the application servers' data source configuration. For example, in JBoss, you configure the data source via say my-ds.xml file and copy that to the deploy folder.

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
 <local-tx-datasource> 
        <jndi-name>jdbc.dataSource.my_jndi</jndi-name>
        <use-java-context>false</use-java-context>
  <connection-url>jdbc:sybase:Tds:my-server:20345/my_schema</connection-url>
  <driver-class>com.sybase.jdbc3.jdbc.SybDriver</driver-class>
  <user-name>user</user-name>
  <password>password</password>
  <max-pool-size>50</max-pool-size>
  <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter</exception-sorter-class-name>
  <new-connection-sql>select count(1) from my_table</new-connection-sql>
        <check-valid-connection-sql>select count(1) from my_table</check-valid-connection-sql> 
 </local-tx-datasource> 
 
</datasources>

Now the Spring configuration to use the JNDI name

    <bean id="datasource_abc" class="org.springframework.jndi.JndiObjectFactoryBean"
  scope="singleton">
  <property name="jndiName">
   <value>jdbc.dataSource.my_jndi</value>
  </property>
 </bean>

   <bean id="jdbcTemplate_abc" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="datasource_abc" />
 </bean>

Q. Which approach would you favor and why?
A. JNDI based datasource creation because you have to move an application between environments like development to UAT and then to integration and finally to production. If you configure each app server to use the same JNDI name, you can have different databases in each environment and not required to change your code. You just pick up the same environment free WAR file and drop it in any environment. In other words, the environment details are externalized.

JDBC, Spring, and Hibernate tutorials

• Spring and Hibernate simple JDBC example
• JDBC Tutorial with HSQLDB
• Spring SimpleJdbcCall to invoke stored procedures
• Spring JDBC Template examples -- calling stored proc, simple select, and insert with returning the generated key

NamedParameterStatement versus PreparedStatement

Q. When will you use NamedParameterStatement over PreparedStatement?
A.

Reason 1:  PreparedStatement uses anonymous parameters that are accessed by index, which is ok when you have 2 or 3 parameters, but for larger queries with many parameters it os more difficult to keep track of the indices. The developer has to count the number of question marks.

String sql = "select * from people where (first_name = ? or last_name = ?) and address = ?";
PreparedStatement p = con.prepareStatement(sql);
p.setString(1, name);
p.setString(2, name);
p.setString(3, address);

The above query will require you to renumber the indices, and it can be improved as shown below.

String sql = "select * from people where (first_name = ? or last_name = ?) and address = ?";
PreparedStatement p = con.prepareStatement(sql);
int i = 1;
p.setString(i++, name);
p.setString(i++, name);
p.setString(i++, address);

The NamedParameter is even more cleaner.

String sql = "select * from people where (first_name = :name or last_name = :name) and address = :address";
NamedParameterStatement p = new NamedParameterStatement(con, sql);
p.setString("name", name);
p.setString("address", address);

Reason 2: In some cases parameters make your query more readable when you have combination of parameters and database functions like getdate( ), etc. The following example is Spring jdbc based to use parameter names.

 
 
public int insertOfflineSuperEquityRequest(TradeDetail td) {

    String sql = "insert into trade_request ( account_code, source_system, reference, ext_reference," +                
    " security_code,create_date,create_id,update_date,update_id) " +
    "   values (:acc_code,'SYSTEM_A', :ref, :ext_ref, :security, getdate(), suser_name(),getdate(), suser_name())";

    //Spring JDBC Named Parameter class 
    NamedParameterJdbcTemplate npJdbcTemplate =  new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
  
  
    Map parameters = new HashMap();
    parameters.put("acc_code", td.accountCode);
    parameters.put("ref", td.getReference());
    parameters.put("ext_ref", td.getExternalReference());
    parameters.put("security", td.getSecurityCode());
 
    
  
    int updateCount = npJdbcTemplate.update(sql, getOfflineRequestArgs(td));
    return updateCount;
}

As you can see, it has a combination of named parameters (:acc_code, :ref, :ext_ref, and security), a constant (i.e. SYSTEM_A), and Sybase database functions like getDate( ) to get current date time and   suser_name( ) to get the user id.

Spring JdbcTemplate batch updates and inserts

Q. Why are batch updates faster?
A.

• The query doesn't need to be repeatedly parsed. Parsed only once per batch.
• The values are transmitted in one network round-trip to the server. So, only one remote call.
• The commands can be placed inside a single transaction when run in a transnational context. 

Q. Should batch updates run within a transaction?

A. Yes.  It is important to keep in mind, that each update added to a Statement or PreparedStatement is executed separately by the database. So, to avoid some data succeeding and others failing you need to run them inside a transaction. When executed inside a transaction, either all updates succeed or all fail, leaving the data in a consistent state.

Q. What type of statement is used for the batch updates?

A. PreparedStatement.

Q. How will you perform a batch update using the Spring JdbcTemplate?

A. The example below is used for an update SQL, but it can be used in a similar fashion for inserts and deletes as well. Spring issues multiple update statements on a single PreparedStatement. There are two ways to do this.

Approach 1: 

package com.myapp.dao;

import org.springframework.jdbc.core.JdbcTemplate;

public class TradeDaoImpl implements TradeDao {

    @Resource(name = "jdbcTemplate")
    JdbcTemplate jdbcTemplate;

    @Override
 public int[] updateTradeStatusAndErrorMsg(final List<TradeDetail> tradeDetails) {
  
  final String UPDATE_TRADES_SQL = "UPDATE trade_table SET status=?, error=? "
                 + "  WHERE  trade_id=?"
  
  List<Object[]> updateBatchArgs = getUpdateBatchArgs(tradeDetails);
  
  int[] updateCounts = jdbcTemplate.batchUpdate(UPDATE_TRADES_SQL,updateBatchArgs);
   
 }
 
 
 private List<Object[]> getUpdateBatchArgs(List<TradeDetail> tradeDetails) {
  List<Object[]> updateBatchArgs = new ArrayList<Object[]>();
  for (TradeDetail d : tradeDetails) {
   Object[] updateArgs = new Object[3];
   updateArgs[0] = d.getStatus() != null ? d.getStatus().toString() : "";
   updateArgs[1] = d.getErrorMsg() != null ? d.getErrorMsg() : "";
   updateArgs[2] = d.getTradeId().intValue();
   updateBatchArgs.add(updateArgs);
  }
  
  return updateBatchArgs;
 }
}

Approach 2:

package com.myapp.dao;

import org.springframework.jdbc.core.JdbcTemplate;

public class TradeDaoImpl implements TradeDao {

    @Resource(name = "jdbcTemplate")
    JdbcTemplate jdbcTemplate;

    @Override
 public int[] updateTradeStatusAndErrorMsg(final List<TradeDetail> tradeDetails) {
  
  final String UPDATE_TRADES_SQL = "UPDATE trade_table SET status=?, error=? "
                 + "  WHERE  trade_id=?"
  
  //anonymous inner class is used
  int[] updateCounts = jdbcTemplate.batchUpdate(UPDATE_TRADES_SQL, new BatchPreparedStatementSetter() {

      //more control over the prepeared statement
   @Override
   public void setValues(PreparedStatement ps, int i) throws SQLException {
    TradeDetail d = tradeDetails.get(i);
    ps.setObject(1, d.getStatus() != null ? d.getStatus().toString() : TradeStatusType.ERR.toString(), Types.CHAR);
    ps.setString(2, d.getErrorMsg() != null ? d.getErrorMsg() : "");
    ps.setInt(3,d.getTradeId().intValue());
   }

   @Override
   public int getBatchSize() {
    return tradeDetails.size();
   }
  });

  return updateCounts;
   
 }
}

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

Spring CrudRepository with JPA example

Step 1: Add the jar dependency to your maven pom.xml file.

..
<spring.data.version>1.2.0.RELEASE</spring.data.version>
...
<dependency>
 <groupId>org.springframework.data</groupId>
 <artifactId>spring-data-jpa</artifactId>
 <version>${spring.data.version}</version>

Step 2: Define the JPA entity -- that is your model class that maps to the table in the database.

package com.mydomain.model;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
import javax.persistence.Transient;

import org.apache.commons.lang.builder.EqualsBuilder;
import org.apache.commons.lang.builder.HashCodeBuilder;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import org.hibernate.annotations.Type;
import org.springframework.data.jpa.domain.AbstractPersistable;

@Entity
@Table(name = "ReportStructure")
public class Node extends AbstractPersistable<Long>
{
    
    private static final long serialVersionUID = 1L;
    
    @ManyToOne
    @JoinColumn(name = "ParentId", insertable = false, updatable = false)
    private Node parent;
    
    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "ParentId", nullable = false)
    private List<Node> children = new ArrayList<Node>();
    
    @OneToOne(cascade = CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private NodeAttributes attributes;
    
    @ManyToOne(cascade = CascadeType.ALL, optional = false)
    @JoinColumn(name = "KeyId")
    private NodeKey key;
    
    @Column(name = "InactiveFlag", nullable = false, length = 1)
    @Type(type = "yes_no")
    private boolean isSoftDeleted;
    
   
    public Node()
    {
        this(null);
    }
    
    public Node(Long id)
    {
        this.setId(id);
    }
    
    public List<Node> getChildren()
    {
        return children;
    }
    
    public Node getParent()
    {
        return parent;
    }
    
    public void setParent(Node parent)
    {
        this.parent = parent;
        if (parent != null)
        {
            parent.addChild(this);
        }
    }
    
    public void setChildren(List<Node> children)
    {
        this.children = children;
    }
    
    public void addChild(Node child)
    {
        if (child == null)
        {
            return;
        }
        if (!children.contains(child))
        {
            children.add(child);
            synchronized (this)
            {
                if (child.parent == null)
                {
                    child.setParent(this);
                }
            }
        }
    }
    
    public NodeKey getKey()
    {
        return key;
    }
    
    public void setKey(NodeKey key)
    {
        this.key = key;
    }
    
    public NodeAttributes getAttributes()
    {
        return attributes;
    }
    
    public void setAttributes(NodeAttributes attributes)
    {
        this.attributes = attributes;
        this.attributes.setNode(this);
    }
    
    public boolean isSoftDeleted()
    {
        return isSoftDeleted;
    }
    
    public void setSoftDeleted(boolean isSoftDeleted)
    {
        this.isSoftDeleted = isSoftDeleted;
    }
    
        
    @Override
    public int hashCode()
    {
        return new HashCodeBuilder().append(key).append(parent).append(attributes).toHashCode();
    }
    
    @Override
    public boolean equals(final Object obj)
    {
        if (obj instanceof Node)
        {
            final Node other = (Node) obj;
            return new EqualsBuilder().append(key, other.getKey()).append(parent, other.getParent())
                    .append(attributes, other.getAttributes()).isEquals();
        }
        return false;
    }
    
    @Override
    public String toString()
    {
        return ToStringBuilder.reflectionToString(this, ToStringStyle.SHORT_PREFIX_STYLE);
    }
}

Step 3: Define the CRUD reposotory by extending Spring's  CrudRepository class. The CrudRepository gives you out of the box access to the following standard methods

• delete(T entity) which deletes the entity given as a parameter.
• findAll() which returns a list of entities.
• findOne(ID id) which returns the entity using the id given a parameter as a search criteria.
• save(T entity) which saves the entity given as a parameter.

You can provide additional custom methods as shown below,

package com.mydomain.model.impl

import com.mydomain.model.Node;
import com.mydomain.model.NodeKey;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;


public interface NodeRepository extends CrudRepository<Node, Long>
{
    @Query("SELECT n from Node n JOIN n.key k WITH k.clientId = ?1 and k.evalDate = ?2 "
            + "WHERE n.parent is null and n.isSoftDeleted = false ")
    List<Node> find(String clientId, Date evalDate);
    
    @Query("SELECT n from Node n JOIN n.key k WITH k.clientId = :clientId and k.evalDate = :evalDate "
            + "WHERE n.attributes.code = :code and n.isSoftDeleted = false ")
    List<Node> find(@Param("clientId") String clientId, @Param("evalDate") Date evalDate,
            @Param("code") String code);
    
    @Query("SELECT key from NodeKey key where key.isSoftDeleted = false")
    List<NodeKey> findNodeKey();
    
    @Query("SELECT key from NodeKey key WHERE key.clientId = ?1 and key.isSoftDeleted = false")
    List<NodeKey> fetch(String clientId);  
}

Step 4: The Spring config file to wire up JPA. This example uses HSQL.

<!-- Directory to scan for repository classes -->
<jpa:repositories
   base-package="com.mydomain.model" />
 
<bean class="org.springframework.orm.jpa.JpaTransactionManager"
  id="transactionManager">
  <property name="entityManagerFactory"
      ref="entityManagerFactory" />
  <property name="jpaDialect">
    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
  </property>
</bean>
 
<bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="jpaVendorAdapter">
    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
      <property name="generateDdl" value="true" />
      <property name="database" value="HSQL" />
    </bean>
  </property>
</bean>

Step 5: Use the NodeRepository for CRUD operations in the service layer.

public class ReportServiceImpl extends ReportService {
 
   @Autowired
   NodeRepository nodeRepository;
 
  ...
}

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  *******************
 
 @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