Sharing Spring JdbcTemplate instance

By Abhijat Upadhyay – Sr. Software Engineer – ADP Cobalt Inventory Platform

Prior to sharing the JdbcTemplate instance in our application, we injected a Datasource object directly into the DAOs and then in the setter method we created an instance of  the JdbcTemplate class.

For example:

public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);

Then we made the following change to our context xml:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
    <property name="fetchSize" value="200" />

And this is how we started injecting the shared instance:

private JdbcTemplate jdbcTemplate

As per Spring, JdbcTemplate is thread safe once constructed. However, they also mention that the object is stateful but that state is not conversational. This is evident from our declaration in the context.xml above.

Motivation to share JdbcTemplate instance:

  1. To improve performance by reducing the roundtrips that resultset object has to make to fetch the next set of records. We achieve this by setting the fetchSize on JdbcTemplate. Without that the default value of 10 is used by Oracle JDBC driver. For example, if we are fetching 250 records from db with default fetchSize then there will be 25 roundtrips between Java layer and the database. This results in some performance hits. In our case prior to this change we reduced our cache priming time by about 40%. We work with large caches (30 GB+).
  2. We can set config params like fetchSize globally versus having to do inside each DAO.
  3. Object reuse: why to create new ones when it is thread safe… (or may be not, as we found out).

So, what’s the issue we ran into?

The issue is that JdbcTemplate is a “threadsafe” stateful object. In addition to storing fetchSize it also allows us to set 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.
private int maxRows = 0;

Couple of our DAO classes were setting this value to 30K for business reasons. Since our JdbcTemplate instance is shared, if we set maxRows on it then it impacts all DAOs. Any DAO that is trying to fetch more than that many records will return back truncated resultset. For example if a query returns 45,000 records and the maxRows is set to 30,000 then the JdbcTemplate will return 30,000 records only. Previously, since each DAO had its own instance of JdbcTemplate we were not bitten by this issue.

So, what’s the solution?

Few options that we have on the table:

  1. Do what we were doing previously… meaning each DAO should create its own instance of JdbcTemplate.
  2. Make jdbcTemplate instance a prototype bean. This will ensure that each injected jdbcTemplate instance will be different. Similar to #1 but Spring does creation and injection and we can also set global variables in one place.
  3. Do not set maxRows on jdbcTemplate and “fix” the code that is doing that.
  4. Write a wrapper around JdbcTemplate to disallow modifying the state once it has been created.
  5. Request Spring to make JdbcTemplate safe to be used by shared instances. Meaning state should be assigned only during creation time and once that is done any further modification should generate an exception.
%d bloggers like this: