How configure connection existence check in C3P0?

I am using the below code to get Connection. I have used the c3p0 library for connection pooling.

package com.dataSource.c3p0;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSource {

    private static DataSource     datasource;
    private ComboPooledDataSource cpds;

    private DataSource() throws IOException, SQLException, PropertyVetoException {
        cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.jdbc.Driver"); //loads the jdbc driver
        cpds.setJdbcUrl("jdbc:mysql://localhost/test");
        cpds.setUser("root");
        cpds.setPassword("root");

        // the settings below are optional -- c3p0 can work with defaults
        cpds.setMinPoolSize(5);
        cpds.setAcquireIncrement(5);
        cpds.setMaxPoolSize(20);
        cpds.setMaxStatements(180);

    }

    public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
        if (datasource == null) {
            datasource = new DataSource();
            return datasource;
        } else {
            return datasource;
        }
    }

    public Connection getConnection() throws SQLException {
        return this.cpds.getConnection();
    }

}

Now my question is, this code is not checking whether the connection is existing or not. Most likely this will get hit by the famous connection closed error after 8 hours.

While I was using C3P0 with hibernate, there were configurations to test the connection and re-establish it. The config I did was below

<property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">3000</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.idle_test_period">300</property>
    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
    <property name="hibernate.c3p0.preferredTestQuery">SELECT 1</property>

How can I do the same connection test on checkout here, so I can use it in JDBC too?

Answer

There is a good section on configuring connection testing in the C3P0 documentation. Also, see the section on configuration override, in particular the precedence rules.

(In particular, the advice on using JDBC4 drivers which supports isValid() is good.)

In your code, you could simply add

cpds.setTestConnectionOnCheckout( true );
cpds.setPreferredTestQuery( "SELECT 1" );

to your DataSource constructor. Be sure that the query actually talks to the database, I seem to recall some driver implementations that didn’t actually go to the DB for certain queries of this kind 🙂

Cheers,