Setting connection timezone with Spring and DBCP and MySQL

Question

My Enviroment

• Java 5
• Spring 2.5.5
• DBCP DataSource (org.apache.commons.dbcp.BasicDataSource)
• MySQL

Similar posts

• Setting session timezone with spring jdbc oracle

Links

• http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to-manage-Time-Zone-in-MySQL

My Problem

• I need to set on my connection the timezone, aiming to prevent the conversions when dealing with TIMESTAMP columns.

My Idea/research

• DBCP Connection Pool did not mention anything around timezone. LINK

• What I investigate and thought that was oK is described on THIS post, exemplifying is:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="URL" value="${database.url}" /> 
    <property name="user" value="${database.username}" /> 
    <property name="password" value="${database.passwd}" /> 
    <property name="connectionCachingEnabled" value="true"/>
    <property name="sessionTimeZone" value="GMT-3"/>
</bean>

Asking for help area :)

• But this is not working!!
• What I want here is a simple way, preferentially using Spring to configure the timezone on jdbc connection.

Thanks in advance for any help/tips/advice/knowledge share

---

SOLUTION:

My Solution was based on tips collected on this post! Thanks for all!

(...)
@Override
public Connection getConnection() {
    Connection conn = null;
    Statement statement = null;
    try {
        conn = super.getConnection();
        statement = conn.createStatement();
        statement.execute("SET time_zone = \'" + timezone+"\'");
    } catch (SQLException e) {
        LOG.fatal("Error while SET time_zone", e);
    } finally {
        try {
            statement.close();
        } catch (SQLException e) {
            LOG.warn("Error while closing statement", e);
        }
    }
    if(LOG.isDebugEnabled())
        LOG.debug("SET time_zone("+timezone+") for connection, succeed!");
    return conn;
}
(...)

and on my Spring configuration file:

<bean id="dataSource" class="com.my.package.dbcp.TimezoneEnabledDataSource" destroy-method="close">
    (...)
    <property name="timezone" value="${database.timezone}" />
    (...)
</bean>

I hope this post can help someone in the future. Any question ping me!

Answer 1

You should be able to put the same SQL statements in the initConnectionSqls property of the DBCP configuration element. Just add this to the DBCP configuration element

<property name="initConnectionSqls" value="SET time_zone = '${database.timezone}'"/>

Depending on your version of DBCP, you may have to use connectionInitSqls as the property name. This information is straight from DBCP configuration documentation.

Answer 2

If the data source doesn't have such a property, you can extend it and add that property:

public TimezoneEnabledDataSource extends BasicDataSource {
    private String timezone;
    //getter and setter for it

    @Override    
    public Connection getConnection() {
        Connection c = super.getConnection();
        // execute a query: SET time_zone = '-8:00'
        return c;
    }
}

See here http://www.electrictoolbox.com/mysql-set-timezone-per-connection/ for the query details.

MySQL documentation writes:

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

You can also check if c3p0 doesn't have something built-in.

Answer 3

One of the possible solutions:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="URL" value="${database.url}?serverTimezone=America/Los_Angeles" /> 
    <property name="user" value="${database.username}" /> 
    <property name="password" value="${database.passwd}" /> 
    <property name="connectionCachingEnabled" value="true"/>
    <property name="sessionTimeZone" value="GMT-3"/>
</bean>

Answer 4

There is no "sessionTimeZone" member in the BasicDataSource. Use C3P0 which is a "better" connection pool than DBCP, or even better, if you are in a Java EE web server, use it to initialize a JNDI datasource ;)