Announcement Announcement Module
No announcement yet.
Initializing with different approaches of JDBC database access : Connection Issues Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Initializing with different approaches of JDBC database access : Connection Issues

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

    // classic Spring JDBC approach
    super.setDataSource(SpringApplicationContextLoader .getJDBCTemplate()
    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.

  • #2
    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.


    • #3
      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.


      • #4
        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.