Configure c3p0 in spring application context (intSQL and timezone)

Question

How to configure c3p0 in spring application context?

I am running mybatis + spring + c3p0 + Oracle 11g.

c3p0 documentation for extensions says:

extensions Default: an empty java.util.Map A java.util.Map (raw type) containing the values of any user-defined configuration extensions defined for this DataSource.

c3p0 documentation for user extensions to configurations says:

<extensions>
  <property name="initSql">SET SCHEMA 'foo'</property>
  <property name="timezone">PDT</property>
</extensions>

Therefore, I configured my spring application context as:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />
    <property name="jdbcUrl" value="jdbc:oracle:thin:@//databasehost:1527/servicename" />
    <property name="user" ref="database.user" />
    <property name="password" ref="database.password" />
    <property name="extensions">
        <map>
            <entry key="initSql" value="ALTER SESSION SET CURRENT_SCHEMA = MY_SCHEMA" />
            <entry key="timezone" value="UTC" />
        </map>
    </property>
</bean>

However nothing happens, it doesn't throw error but doesn't behave as expected.

Answer 1

What you did in your answer is not enough to make it work.

If you investigate your mysql log you will see that the timezone setting never takes effect for example (there will be no "set time_zone..." statement executing ever).

The only thing that takes effect in your answer is: preferredTestQuery which you have set to be alter session set current_schema=MY_SCHEMA.

This means that every Checkin (which happens before almost every query you execute - i.e TOO MUCH) will also call alter session set current_schema=MY_SCHEMA which is a very bad performance practice...

If you want to execute some SQL when the connection is aquired, you need to use a ConnectionCustomizer in combination with the extensions Map you have created. (you can see it written in their documentation here)

Example:

public class ExampleConnectionCustomizer extends AbstractConnectionCustomizer {
    public ExampleConnectionCustomizer () {
    }

    private String getInitSql(String parentDataSourceIdentityToken) {
        return (String)this.extensionsForToken(parentDataSourceIdentityToken).get("initSql");
    }

    public void onAcquire(Connection c, String pdsIdt) {
        String initSql = this.getInitSql(parentDataSourceIdentityToken);
        if(initSql != null) {
            Statement stmt = null;
            try {
                stmt = c.createStatement();
                stmt.executeUpdate(initSql);
            } finally {
                if(stmt != null) {
                    stmt.close();
                }
            }
        }
    }

}  

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    ....The rest of your properties...
    <property name="preferredTestQuery" value="SELECT 1" /> <!--Much more efficient-->
    <property name="connectionCustomizerClassName" value="yourpackage.ExampleConnectionCustomizer" />
    <!-- extensions -->         
    <property name="extensions">
        <map>
            <entry key="initSql" value="alter session set current_schema=MY_SCHEMA" />
        </map>
    </property>
</bean>