Managing multiple jdbcTemplates programmatically

Question

I have a custom business requirement, in which i need to manage the details of various Oracle data sources in the database. And as soon as my application deploys, i create jdbcTemplates corresponding to each of 'em.

Now the problem is that, if i am using DataSource connections, it leads to too many open data connections and hence the DB refuses connections and application crashes.

I also used SingleConnectionDataSource, but in that case, Spring throws connection timeout error after 1 or 2 hours of idle session.

Another trial was with BasicDataSource, but that also resulted in screwing the Application.

The way i am maintaining the Data

     BasicDataSource dataSource = new BasicDataSource();
            dataSource.setDriverClassName(DRIVER_CLASS_NAME);
            dataSource.setUrl(CONNECTION_PREFIX + getHost() + ":" + getPort() + ":" + getServiceName());
            dataSource.setUsername(getUserName());
            dataSource.setPassword(getPassword());
            dataSource.setInitialSize(15);
            dataSource.setMinIdle(20);
            dataSource.setMaxIdle(100);
            dataSource.setMaxActive(150);
            dataSource.setMaxWait(6000);
            dataSource.setRemoveAbandoned(true);
            dataSource.setRemoveAbandonedTimeout(30000);
            dataSource.setLogAbandoned(true);
            dataSource.setTestOnBorrow(true);
            dataSource.setTestOnReturn(false);
            dataSource.setTestWhileIdle(true);
            dataSource.setTimeBetweenEvictionRunsMillis(30000);
            dataSource.setMinEvictableIdleTimeMillis(30000);
            dataSource.setMinEvictableIdleTimeMillis(30000);
            dataSource.setValidationQuery("SELECT 1 from dual");

            JdbcTemplate template = new JdbcTemplate(dataSource);

Note that i am creating multiple jdbcTemplates in this way and maintaining them in a HashMap. Data source details are also coming from a database table

This is because the front application will actually decide which particular data source will be used to be used for performing the DB operation.

Please suggest me a better approach, or solution to the existing timeout / idle connection problem !!

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

I'll suggest connection pooling. It is possible to set the number of initial connections, the limit of total connections and dozens of other options.

Take a look here.

edited May 23 '17 at 12:12

Community

1
1

answered Sep 12 '14 at 05:42

Sezin Karli

2,517
19
24

Thank you @Sezin. I knew Connection pooling would help, but was wondering about how can i implement in my code. Ty – Manish Kr. Shukla Sep 13 '14 at 08:23
Happy to help. Manually implementing this kind of stuff is normally risky so I suggested this approach. – Sezin Karli Sep 13 '14 at 10:09

Managing multiple jdbcTemplates programmatically

1 Answers1