Multiple JdbcTemplate instances or not?

Question

From what I understand, both DataSource and JdbcTemplates are threadsafe, so you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs (or repositories). Also DataSourceshould be a Spring singleton, since it manages the connection pool.

The official Spring Documentation JdbcTemplate best practices explains the alternatives (excerpts from the manual are in italics, and my notes between square brackets:

• configure a DataSource in your Spring configuration file, and then dependency-inject that shared DataSource bean into your DAO classes; the JdbcTemplate is created in the setter for the DataSource. [with XML configuration and this leads to multiple JdbcTemplate instances, since in the datasource setter there is new JdbcTemplate(dataSource)]
• use component-scanning and annotation support for dependency injection. In this case you annotate the class with @Repository (which makes it a candidate for component-scanning) and annotate the DataSource setter method with @Autowired. [also this case leads to multiple JdbcTemplate instances]
• If you are using Spring's JdbcDaoSupport class, and your various JDBC-backed DAO classes extend from it, then your sub-class inherits a setDataSource(..) method from the JdbcDaoSupport class. You can choose whether to inherit from this class. The JdbcDaoSupport class is provided as a convenience only. [since you've an instance of JdbcDaoSupport for each class extending it, there is an instance of JdbcTemplate too for each instance of the derived class (see source code for JdbcDaoSupport)]

However, a later note, discourages all the options just presented:

Once configured, a JdbcTemplate instance is threadsafe. You may want multiple JdbcTemplate instances if your application accesses multiple databases, which requires multiple DataSources, and subsequently multiple differently configured JdbcTemplates.

In other words, all the options just presented will result in having multiple JdbcTemplate instances (one per DAO), and just after the docs says that is not necessary when working with a single database.

What I would do is inject directly JdbcTemplate to the various DAOs needing it, so my question is, is it OK to do so? And also, do you also think that the Spring reference documentation is self-contradicting? Or is my misunderstanding?

Answer 1

IMO, there is no problem to inject JdbcTemplate to your (multiple) DAO(s). The template is used to "wire" your DAO to the physical resource (db connection) when you need to run db query. So if the SessionFactory and the TransactionManager are properly configured you will not run into concurrency problems - Spring manages the lifecycle of the beans you need for working with you persistence layer. The advantages of using a template are:

1. JDBC template manages physical resources required to interact with the DB automatically, e.g. create and release the database connections.
2. The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows you to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages

Answer 2

so it should be spilt two situations:

We don’t change JdbcTemplate properties in DAO, we can define as below:

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

NOTE: Most of time we don’t change the JdbcTemplate properties, because it is not necessary.

We change JdbcTemplate properties in DAO, we should be extends JdbcDaoSupport.

State: •   fetchSize: If this variable is set to a non-zero value, it will be used for setting the fetchSize property on statements used for query processing(JDBC Driver default) •   maxRows: If this variable is set to a non-zero value, it will be used for setting the maxRows property on statements used for query processing(JDBC Driver default) •   queryTimeout: If this variable is set to a non-zero value, it will be used for setting the queryTimeout property on statements used for query processing.(JDBC Driver default) •   skipResultsProcessing: If this variable is set to true then all results checking will be bypassed for any callable statement processing.  This can be used to avoid a bug in some older Oracle JDBC drivers like 10.1.0.2.(false) •   skipUndeclaredResults: If this variable is set to true then all results from a stored procedure call that don't have a corresponding SqlOutParameter declaration will be bypassed. All other results processing will be take place unless the variable {@code skipResultsProcessing} is set to {@code true}(false) •   resultsMapCaseInsensitive: If this variable is set to true then execution of a CallableStatement will return the results in a Map that uses case insensitive names for the parameters if Commons Collections is available on the classpath.(false) 

1. JdbcDaoSupport

   public abstract class JdbcDaoSupport extends DaoSupport {

   private JdbcTemplate jdbcTemplate;   /**  * Set the JDBC DataSource to be used by this DAO.  */ public final void setDataSource(DataSource dataSource) {     if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {         this.jdbcTemplate = createJdbcTemplate(dataSource);         initTemplateConfig();     } } 

summary: I don’t think spring give the practice in guide is the best.