使用Spring和DBCP和MySQL设置连接时区 [英] Setting connection timezone with Spring and DBCP and MySQL
问题描述
我的环境
- Java 5
- Spring 2.5。 5
- DBCP DataSource （org.apache.commons.dbcp.BasicDataSource）
- MySQL
- Java 5
- Spring 2.5.5
- DBCP DataSource (org.apache.commons.dbcp.BasicDataSource)
- MySQL
类似帖子
- Setting session timezone with spring jdbc oracle
链接
- http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to-manage-Time-Zone-in-MySQL
我的问题
- 我需要在我的连接上设置时区，目的是在处理TIMESTAMP列时阻止转换。
我的想法/研究
-
DBCP连接池没有提及时区周围的任何内容。 LINK
我调查和认为是oK的内容在这篇帖子中有所描述，举例说明是：
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>
寻求帮助区域：）
- 但这不起作用!!
- 我想要的是一个简单的方法，优先使用Spring在jdbc上配置时区连接。
预先感谢任何帮助/提示/建议/知识分享
Thanks in advance for any help/tips/advice/knowledge share
解决方案：
我的解决方案基于此帖子收集的提示！谢谢大家！
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;
}
(...)
和我的Spring配置文件：
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>
我希望这篇文章可以在将来帮助某人。任何问题ping我！
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;
}
}
见这里http://www.electrictoolbox.com/mysql-set-timezone-per-connection/ 查询详细信息。
每个连接时区。连接的每个客户端都有自己的时区设置，由会话time_zone变量给出。最初，会话变量从全局time_zone变量获取其值，但客户端可以使用以下语句更改自己的时区：
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;
mysql> SET time_zone = timezone;
您还可以检查 c3p0 是否无效没有内置的东西。
You can also check if c3p0 doesn't have something built-in.
这篇关于使用Spring和DBCP和MySQL设置连接时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
