Setting connection timezone with Spring and DBCP and MySQL

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, preferentiall开发者_如何学Goy 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!

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.

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.

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>

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 ;)