how to set driver connection properties using c3p0 libraries?

Question

I am Migrating from dbcp2 to c3p0,

We create a datasource by extending the BasicDataSource from dbcp2 and setting the properties. Some properties are set at driver level via setConnectionProperties method.

I dont see such a provision in c3p0 when extending the AbstractComboPooledDataSource. Is there another way to set the same?

Digging through the docs, I found something called a connectionCustomizer, but not sure if it does the same

This is how I am currently setting the properties with dbcp2:

this.setConnectionProperties("driver:oracle.jdbc.ReadTimeout=180000");
this.setConnectionProperties("driver:oracle.net.CONNECT_TIMEOUT=180000");

where "this" is class which extends BasicDataSource

Is there anyway in c3p0 to get the same result?

EDIT:

Just to be clear, I am able to set the properties provided by the c3p0 library, what I am looking for is setting properties at driver level the way dbcp2 allows to do via the SetConnectionProperties() method.THanks

Answer 1

You can get the details from the below answer,

https://stackoverflow.com/a/51838455/1529092

So basically you have to do something like this,

@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;
}

Edit:

As per documentation, the timeout properties are different in each framework, so in this case, the time out is handled by,

• maxConnectionAge
• maxIdleTime
• maxIdleTimeExcessConnections

Managing Pool Size and Connection Age Go To Top

Different applications have different needs with regard to trade-offs between performance, footprint, and reliability. C3P0 offers a wide variety of options for controlling how quickly pools that have grown large under load revert to minPoolSize, and whether "old" Connections in the pool should be proactively replaced to maintain their reliablity.

• maxConnectionAge
• maxIdleTime
• maxIdleTimeExcessConnections

By default, pools will never expire Connections. If you wish Connections to be expired over time in order to maintain "freshness", set maxIdleTime and/or maxConnectionAge. maxIdleTime defines how many seconds a Connection should be permitted to go unused before being culled from the pool. maxConnectionAge forces the pool to cull any Connections that were acquired from the database more than the set number of seconds in the past.

maxIdleTimeExcessConnections is about minimizing the number of Connections held by c3p0 pools when the pool is not under load. By default, c3p0 pools grow under load, but only shrink if Connections fail a Connection test or are expired away via the parameters described above. Some users want their pools to quickly release unnecessary Connections after a spike in usage that forces a large pool size. You can achieve this by setting maxIdleTimeExcessConnections to a value much shorter than maxIdleTime, forcing Connections beyond your set minimum size to be released if they sit idle for more than a short period of time.

Some general advice about all of these timeout parameters: Slow down! The point of Connection pooling is to bear the cost of acquiring a Connection only once, and then to reuse the Connection many, many times. Most databases support Connections that remain open for hours at a time. There's no need to churn through all your Connections every few seconds or minutes. Setting maxConnectionAge or maxIdleTime to 1800 (30 minutes) is quite aggressive. For most databases, several hours may be more appropriate. You can ensure the reliability of your Connections by testing them, rather than by tossing them. (see Configuring Connection Testing.) The only one of these parameters that should generally be set to a few minutes or less is maxIdleTimeExcessConnections.

Answer 2

I found the answer in this answer: c3p0 hangs on getConnection when there is a network failure.

cpds = new ComboPooledDataSource();
...

//--------------------------------------------------------------------------------------
// NOTE: Once you decide to use cpds.setProperties() to set some connection properties,
//       all properties must be set, including user/password, otherwise an exception
//       will be thrown
Properties prop = new Properties();
prop.setProperty("oracle.net.CONNECT_TIMEOUT",
    Integer.toString(JDBC_CONNECTION_TIMEOUT_IN_MILLISECONDS));
prop.setProperty("oracle.jdbc.ReadTimeout",
    Integer.toString(JDBC_SOCKET_TIMEOUT_IN_MILLISECONDS));
prop.setProperty("user", username);
prop.setProperty("password", password);
cpds.setProperties(prop);