java 使用 Spring 和 DBCP 和 MySQL 设置连接时区

Setting connection timezone with Spring and DBCP and MySQL

提问by rafa.ferreira

My Enviroment

我的环境

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

• 爪哇 5
• 春天 2.5.5
• DBCP 数据源(org.apache.commons.dbcp.BasicDataSource)
• MySQL

Similar posts

类似帖子

• Setting session timezone with spring jdbc oracle

• 使用 spring jdbc oracle 设置会话时区

Links

链接

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

• 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.

• 我需要在我的连接上设置时区，目的是在处理 TIMESTAMP 列时防止转换。

My Idea/research

我的想法/研究

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

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

• DBCP 连接池没有提到有关时区的任何内容。关联

• 在这篇文章中描述了我调查并认为没问题的内容，例如：

<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.

• 但这不起作用！！
• 我这里想要的是一个简单的方法，优先使用Spring在jdbc连接上配置时区。

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:

在我的 Spring 配置文件中：

<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!

我希望这篇文章可以在将来对某人有所帮助。有什么问题可以问我！

采纳答案by Bozho

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.

有关查询详细信息，请参见此处http://www.electrictoolbox.com/mysql-set-timezone-per-connection/。

MySQL documentation writes:

MySQL 文档写道：

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;

每个连接的时区。每个连接的客户端都有自己的时区设置，由会话 time_zone 变量给出。最初，会话变量从全局 time_zone 变量中获取其值，但客户端可以使用以下语句更改自己的时区：

mysql> SET time_zone = 时区；

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

您还可以检查c3p0是否没有内置内容。

回答by Chandranshu

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

您应该能够将相同的 SQL 语句放在 DBCP 配置元素的 initConnectionSqls 属性中。只需将此添加到 DBCP 配置元素

<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 configurationdocumentation.

根据您的 DBCP 版本，您可能必须使用 connectionInitSqls 作为属性名称。此信息直接来自DBCP 配置文档。

回答by Julia Shevchuk

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>

回答by Vincent Devillers

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

没有“SESSIONTIMEZONE”成员中的BasicDataSource。使用 C3P0，它是一个比 DBCP“更好”的连接池，甚至更好，如果您在 Java EE Web 服务器中，请使用它来初始化 JNDI 数据源；)