Initializing with different approaches of JDBC database access : Connection Issues

[sidd]

In my class I have instantiated different flavors of JDBC database access to achieve different scenarios.

Class{
Constructor(){
// classic Spring JDBC approach
super.setDataSource(SpringApplicationContextLoader .getJDBCTemplate()
.getDataSource());
jdbcTemplate = super.getJdbcTemplate();
// for making proper usage of temporary tables and different combinations with named parameters Template
scds = new SingleConnectionDataSource(jdbcTemplate.getDataSou rce()
.getConnection(), true);
scdsJdbcTemplate = new JdbcTemplate(scds);
namedParameterJdbcTemplateForSCDS = new NamedParameterJdbcTemplate(scds);
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSou rce());
}

getDatafromAbcTable(){ //make use of traditional template i.e. jdbc template
}

getDatafromDefTable(){ //make use of named parameter with single source
}

getDatafromGhiTable(){ //make use of named parameter
}
}

There are different methods that we call to fetch data from 'Abc','Def' or 'Ghi' Table. But the problem is that while I call this from a different class (creating an object of the above class), I found that there are connections created every-time I create an object of the above class. As I have used SingleConnectionDataSource() and the other objects also get initialized while I call this constructor, I am facing connections issues if I repeatedly call the above methods from other classes. I cannot increase the connections available. I need to figure out a better approach to write the above class. Please guide me out.

[Marten Deinum]

The first thing that comes to mind is... ARGH.... Why on earth would you want to do that.

1) Ugly singleton to get a datasource from a jdcbtemplate ..
2) Instantiate 4 templates.

This is slow and why on earth would you want to do that.

You only need a NamedParameterJdbcTemplate with the getJdbcOperations method you can get access to the underlying JdbcTemplate used by this bean. Also again why on earth this contraption of a class. You should only need a single NamedParameterJdbcTemplate and springs transaction support will manage the connections for you... And please INJECT the datasource instead of this ugly beast with lookups.

[sidd]

Hey Marten,
Thanks for the reply.....I am a newbie to Spring and have started implementing the JDBC data access. I learn from your reply that the implementation done by me is truly bewildering. I would like to share some more info like we are heavily dependent on usage of temporary tables and performing the same under single connection. (that's why implemented SingleConnectionDataSource) and for the rest of the queries where we don't want to make use of temporary tables (we have implemented using NamedParameterJdbcTemplate).
I personally don't want to make use of so many templates, but could not find any solution to serve my purpose. It would be of great help if you can propose some generic solution to my problem and to avoid SingleConnectionDataSource also.
Thanks

[Marten Deinum]

I strongly suggest reading up on spring and especially transaction and resource management.

As I stated you only need a single NamedParameterJdbcTemplate and Spring to manage your transactions that way all will work. Spring opens a single connection for the duration of the transaction (so no need for the single connection stuff, which should be avoided in production code anyways!).

Either use the NamedParameterJdbcDaoSupport class as a bass class and inject the datasource in there which gives you access to both the JdbcTemplate and NamedParameterJdbcTemplate or create a JdbcTemplate and NamedParameterJdbcTemplate in your configuration and inject that in your class that needs it.