Spring JDBC connection pool best practices

I have a basic Spring JDBC application with a pretty basic configuration:

<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
   <property name="url" value="jdbc:oracle:thin:@1.1.1.1:1521:XXX"/>
   <property name="username" value="username"/>
   <property name="password" value="password"/>
</bean>

<bean id="dbThing" class="com.DbThing">
   <property name="dataSource" ref="myDataSource"/>
</bean>

I would like to introduce a connection pool, and after reading several threads on SO I am a bit confused about which pooling library to use.

The libraries that seem to have more credits on SO are C3P0 and DBCP. Since I'm using Oracle, I could also use the pooled data source offered by the driver.

I understand that there are more libraries available - for instance the new Apache Tomcat 7 pooling libraries.

Is there any library that I should really avoid?

Is there any recommended configuration I should use with a given library?

Any "war story" you care to share?

Solution 1:

C3PO and DBCP development have stalled mostly because they are mature. I have seen both of these drivers be able to support hundreds of transactions per second.

The Tomcat pool is a reworked & updated DBCP driver. MyBatis 3.0 also contains it's own pooling implementation which, based on code inspection, seems solid. Finally, there's BoneCP which claims to have the best performance. I haven't used any of these on a project yet.

Probably the best advice is to pick any of them test it. Spring makes it easy to swap out later.

Solution 2:

As an alternative to BoneCP, have you perhaps tried Oracle's own database connection pool?

I've had good experiences for the last couple of weeks, so it might be worth giving it a shot - also, I suppose Oracle would know a thing or two about making a connection pool especially when paired up with their own database.

<bean id="dataSource" class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
    <property name="URL" value="${jdbc.url}" />
    <property name="user" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</bean>

UPDATE: Also, if you're using (one of) the latest Oracle JDBC drivers (11.2.0.1+), you may want to try out the new Universal Connection Pool. The OracleConnectionPoolDataSource seems to be officially deprecated in favour of this pool. However, some users have reported errors using it, so it may be too early. I am in a position to use Oracle's latest JDBC drivers, so I will give it a try and update here as soon as have any info on this.

More info on this SO thread: Oracle UCP

Solution 3:

BoneCP has been claiming but then a new tool is introduced named HiKariCP it has overcomed many drawbacks which were present in pass tools you can configure it by below change in application-context.xml

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
  <property name="maximumPoolSize" value="10" />
  <property name="minimumPoolSize" value="2" />
  <property name="dataSourceClassName" 
            value="oracle.jdbc.pool.OracleDataSource" />
  <property name="dataSourceProperties" ref="props" />
  <property name="poolName" value="springHikariCP" />
</bean>

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
      <constructor-arg ref="hikariConfig" />
</bean>

<util:properties id="props" location="classpath:datasource.properties"/>

where in database.properties you should provide details of database like below

 url=jdbc:oracle:thin:@IP:port:SID/Databasename
 user=usernmae
 password=password

For proper demo you can use this link

Solution 4:

Definately you can use C3P0, this is developed for enterprise solution. To Check advantages you can follow this answer.

Here is the example code of integration:

@Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager =
                new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return transactionManager;
    }

This Bean is for getting JpaTransactionManager.

@Primary
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

    LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
    entityManagerFactoryBean.setDataSource(dataSource());
    entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
    entityManagerFactoryBean.setPackagesToScan("YOUR.DATABSE.ENTITY.PACKAGE");
    entityManagerFactoryBean.setJpaProperties(hibProperties());

    return entityManagerFactoryBean;
}

This Bean is for getting LocalContainerEntityManagerFactoryBean. It Takes DataSource ,PersistenceProviderClass , Entity Package Name PackagesToScan and JpaProperties from hibProperties().

@Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

private Properties hibProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
        properties.put("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
        return properties;
    }

Here, env value are comming from application.properties.

Check bellow properties:

hibernate.dialect: org.hibernate.dialect.Oracle12cDialect
hibernate.show_sql: false
hibernate.hbm2ddl.auto: none

The main part is DataSource Setup. That is given bellow:

@Bean
    public ComboPooledDataSource dataSource(){
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        try {
            dataSource.setDriverClass(env.getProperty("db.driver"));
            dataSource.setJdbcUrl(env.getProperty("db.url"));
            dataSource.setUser(env.getProperty("db.username"));
            dataSource.setPassword(env.getProperty("db.password"));
            dataSource.setMinPoolSize(Integer.parseInt(env.getProperty("minPoolSize")));
            dataSource.setMaxPoolSize(Integer.parseInt(env.getProperty("maxPoolSize")));
            dataSource.setMaxIdleTime(Integer.parseInt(env.getProperty("maxIdleTime")));
            dataSource.setMaxStatements(Integer.parseInt(env.getProperty("maxStatements")));
            dataSource.setMaxStatementsPerConnection(Integer.parseInt(env.getProperty("maxStatementsPerConnection")));
            dataSource.setMaxIdleTimeExcessConnections(10000);

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

It is using ComboPooledDataSource that is taking many more important parameter's like maxPoolSize,MinPoolSize, MaxIdleSize etc. It's environment parameter are given bellow:

db.driver: oracle.jdbc.driver.OracleDriver // for Oracle
db.username: YOUR_USER_NAME
db.password: YOUR_USER_PASSWORD
db.url: DATABASE_URL
minPoolSize:5 // number of minimum poolSize
maxPoolSize:100 // number of maximum poolSize
maxIdleTime:5 // In seconds. After that time it will realease the unused connection.
maxStatements:1000
maxStatementsPerConnection:100
maxIdleTimeExcessConnections:10000

Here is the full working sample code:

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.*;
import org.springframework.core.env.Environment;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories
@PropertySource("classpath:application.properties")
@Scope("singleton")
public class TestDataSource {

    @Autowired
    private Environment env;

    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;

    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager =
                new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return transactionManager;
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource());
        entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);
        entityManagerFactoryBean.setPackagesToScan("YOUR.PACKAGE.NAME");
        entityManagerFactoryBean.setJpaProperties(hibProperties());

        return entityManagerFactoryBean;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

    @Bean
    public ComboPooledDataSource dataSource(){
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        try {
            dataSource.setDriverClass(env.getProperty("db.driver"));
            dataSource.setJdbcUrl(env.getProperty("db.url"));
            dataSource.setUser(env.getProperty("db.username"));
            dataSource.setPassword(env.getProperty("db.password"));
            dataSource.setMinPoolSize(Integer.parseInt(env.getProperty("minPoolSize")));
            dataSource.setMaxPoolSize(Integer.parseInt(env.getProperty("maxPoolSize")));
            dataSource.setMaxIdleTime(Integer.parseInt(env.getProperty("maxIdleTime")));
            dataSource.setMaxStatements(Integer.parseInt(env.getProperty("maxStatements")));
            dataSource.setMaxStatementsPerConnection(Integer.parseInt(env.getProperty("maxStatementsPerConnection")));
            dataSource.setMaxIdleTimeExcessConnections(10000);

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    private Properties hibProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
        properties.put("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
        return properties;
    }
}

Another things. Here is the gradle link

compile group: 'org.hibernate', name: 'hibernate-c3p0', version: '5.2.10.Final'

Hope this will help you. Thanks :)