Handling Connection Pool Issues In Spring Boot

Problem:
I created a Spring Boot App that connects to a Mysql Database. This App works fine when it is started. After some time of inactivity, the App fails to connect to Mysql database until it is restarted. I narrowed the issue down to improper connection pool configurations. Essentially, Spring Boot’s autoconfiguration was not working for me in this particular case.

Solution:
I configured my own ‘dataSource’ bean instead of relying on the one that Spring boot configures automatically. I used the c3p0 dataSource. Here is what I did:

1. Got the c3p0 dependency.

2. Configured the dataSource.

You can add the dataSource bean in an already existing Configuration class or create a new Configuration class:

@Configuration
public class CustomConfig {

    @Value("${c3p0.max_size}")
    private int maxSize;

    @Value("${c3p0.min_size}")
    private int minSize;

    @Value("${c3p0.acquire_increment}")
    private int acquireIncrement;

    @Value("${c3p0.idle_test_period}")
    private int idleTestPeriod;

    @Value("${c3p0.max_statements}")
    private int maxStatements;

    @Value("${c3p0.max_idle_time}")
    private int maxIdleTime;

    @Value("${c3p0.url}")
    private String url;

    @Value("${c3p0.username}")
    private String username;

    @Value("${c3p0.password}")
    private String password;

    @Value("${c3p0.driverClassName}")
    private String driverClassName;

    @Bean
    public ComboPooledDataSource dataSource() throws PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setMaxPoolSize(maxSize);
        dataSource.setMinPoolSize(minSize);
        dataSource.setAcquireIncrement(acquireIncrement);
        dataSource.setIdleConnectionTestPeriod(idleTestPeriod);
        dataSource.setMaxStatements(maxStatements);
        dataSource.setMaxIdleTime(maxIdleTime);
        dataSource.setJdbcUrl(url);
        dataSource.setPassword(password);
        dataSource.setUser(username);
        dataSource.setDriverClass(driverClassName);
        return dataSource;
    }
}

Specify the values (@Value variables) in your properties file.

Once this bean has been provided, Spring Boot will use it rather than the one it provides automatically.

 

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s