Spring JDBC with Tomcat DBCP and multiple datasources

Question

I am using spring with have multiple datasources (catering to multiple mysql dbs) and using tomcat dbcp. I am getting some weird exceptions like

procedure not found - when the proc is definitely present in the db
cannot borrow from pool - local dev setup, so definitely the pool is not full

the Problem I feel might be this, need inputs from everyone:

I have one jdbcTemplate object defined in my spring.xml, on every query that I need to fire, I call jdbcTemplate.setDataSource() to set the appropriate datasource and then use simplejdbccall(jdbctemplate) to execute the proc.

Should I go in for also defining multiple jdbcTemplate objects, i.e. one for each datasource defined. The bean from where I am setting the datasource on the jdbctemplate and executing the stored proc is defined as prototype.

If you aren't providing synchronization around the data access operations when you are changing the dataSource, this is horribly thread-unsafe. — matt b
– matt b, Commented Jan 14, 2011 at 15:16

skaffman · Accepted Answer · 2011-01-14 15:07:12Z

5

You should certainly not use a shared a JdbcTemplate if you're going to keep changing its DataSource. Set the DataSource once, and leave it alone.

That either means multiple JdbcTemplate beans, one for each DataSource, or create new JdbcTemplate objects manually, on demand, and don't share them. There's no significant performance overhead in creating new ones, do that's not an issue.

You could define them as prototype, sure, but there's not much point if you're going to inject the DataSource manually. Might as well instantiate JdbcTemplate using new.

answered Jan 14, 2011 at 15:07

skaffman

405k96 gold badges825 silver badges775 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rabbit · Accepted Answer · 2011-01-16 18:53:21Z

Thanks for the answer. I have got past those errors by creating a new instance of jdbctemplate everytime. I have also updated to the latest mysql jconnector jsr (5.1.14) The class design is now pretty simple. I have a base dao which uses a new instance of a custom written spring jdbc wrapper which in turn instantiates a jdbcTemplate object as an instance variable. This instance variable is used in a new instance of a SimpleJdbcCall for every request. all my dao classes inherit from this base dao.

However there are some intermittent errors like this :

org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetadata .... . . . caused by : org.apache.tomcat.dbcp.dbcp.PoolingDataSource.checkConnection() : connection is closed. I don't see a pattern for this error. I have an initialSize of 10 for each of the datasources & a maxActive of 100. any tips regarding what could be the issue here?

Aravind Yarram · Accepted Answer · 2011-01-16 20:02:40Z

0

Spring has some level of native support for switching the data sources dynamically. Here is the article on how you do it.

Below also can help

answered Jan 16, 2011 at 20:02

Aravind Yarram

80.5k49 gold badges239 silver badges335 bronze badges

Collectives™ on Stack Overflow

Spring JDBC with Tomcat DBCP and multiple datasources

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related