dbcp和mysql,使用Spring和DBCP和MySQL设置连接时区

My Enviroment

Java 5

Spring 2.5.5

DBCP DataSource (org.apache.commons.dbcp.BasicDataSource)

MySQL

Similar posts

Links

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:

destroy-method="close">

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:

(...)

(...)

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

解决方案

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;

}

}

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.