java c3p0: how can i configure autoreconnect=true?

Question

I'm writing a red5 application using Java and I'm using the c3p0 for the database interaction.

It seems that after the connection as timed out in my MySQL server my application stops working with a suggestion to configure autoreconnect=true.

how can i do so?

this is the function that i use to create datasource:

private ComboPooledDataSource _createDataSource() {
    Properties props = new Properties();
    // Looks for the file 'database.properties' in {TOMCAT_HOME}\webapps\{RED5_HOME}\WEB-INF\
    try {
        FileInputStream in = new FileInputStream(System.getProperty("red5.config_root") + "/database.properties");
        props.load(in);
        in.close();
    } catch (IOException ex) {
        log.error("message: {}", ex.getMessage());
        log.error("stack trace: " + ExceptionUtils.getFullStackTrace(ex));
        return null;
    }

    // It will load the driver String from properties
    String drivers = props.getProperty("jdbc.drivers");
    String url = props.getProperty("jdbc.url");
    String username = props.getProperty("jdbc.username");
    String password = props.getProperty("jdbc.password");

    ComboPooledDataSource cpds = new ComboPooledDataSource();
    try {
        cpds.setDriverClass(drivers);
    } catch (PropertyVetoException ex) {
        log.error("message: {}", ex.getMessage());
        log.error("stack trace: " + ExceptionUtils.getFullStackTrace(ex));
        return null;
    }

    cpds.setJdbcUrl(url);
    cpds.setUser(username);
    cpds.setPassword(password);
    cpds.setMaxStatements(180);

    return cpds;
}

Rubio

Answer 1

Create a file c3p0.properties which must be in the root of the classpath:

# c3p0.properties
c3p0.testConnectionOnCheckout=true

For further documentation refer to this.

This post might be helpful also.

Wentzel

Can someone shed some light on what a connection check-in and check-out are? – Sculley

Answer 2

The property autoreconnect is no part of C3p0 objectTo use a C3P0 pool its recommended to configure other options (like testConnectionOnCheckout) , and to use a Factory.

You got all C3p0 information and samples here http://www.mchange.com/projects/c3p0/index.html

You can use and external properties file, or add by code: for example how to create a custom Pooled datasource using a datasource and adding custom options (more samples in the C3p0 documentation url)

// Your datasource fetched from the properties file
DataSource ds_unpooled = DataSources.unpooledDataSource("url", "user", "password");


// Custom properties to add to the Source
// See http://www.mchange.com/projects/c3p0/index.html#configuration_properties                           

Map overrides = new HashMap();
overrides.put("maxStatements", "200");         //Stringified property values work
overrides.put("maxPoolSize", new Integer(50)); //"boxed primitives" also work

// Your pooled datasource with all new properties
ds_pooled = DataSources.pooledDataSource( ds_unpooled, overrides ); 

Grory

Yes! Much better. autoreconnect is officially strongly recommended against by MYSQL - it can cause corruption issues. Sorry, don't have the link - saw this a few years ago. – Leatherback