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:

@Autowired
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" />
</bean>

And this is how we started injecting the shared instance:

@Resource(name="jdbcTemplate")
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.

From JdbcTemplate.java:

/**
* 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.

About collectivegenius
Everyone has a voice and great ideas come from anyone. At Cobalt, we call it the collective genius. When technical depth and passion meets market opportunity, the collective genius is bringing it’s best to the table and our customers win.

One Response to Sharing Spring JdbcTemplate instance

  1. cyber high says:

    Hurrah! In the end I got a web site from where I be able to really get useful information regarding my
    study and knowledge.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: