mysql設置時區_使用Spring和DBCP和MySQL設置連接時區

My Enviroment

我的環境

Java 5

Java 5

Spring 2.5.5

春天2.5.5

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

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

MySQL

MySQL的

Similar posts

類似帖子

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.

我需要在我的連接上設置時區，旨在防止處理TIMESTAMP列時的轉換。

My Idea/research

我的想法/研究

DBCP Connection Pool did not mention anything around timezone. LINK

DBCP連接池沒有提及時區周圍的任何內容。鏈接

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

我調查和認為是oK的內容在本文中有所描述，舉例說明如下：

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.

我想要的是一種簡單的方法，優先使用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配置文件中：

(...)

(...)

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

我希望這篇文章可以幫助將來的某個人。有什么問題我！

3 个解决方案

#1

6

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/。

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:

每個連接時區。連接的每個客戶端都有自己的時區設置，由會話time_zone變量給出。最初，會話變量從全局time_zone變量中獲取其值，但客戶端可以使用以下語句更改自己的時區：

mysql> SET time_zone = timezone;

mysql> SET time_zone = timezone;

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

您還可以檢查c3p0是否沒有內置內容。

#2

6

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配置元素即可

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

根據您的DBCP版本，您可能必須使用connectionInitSqls作為屬性名稱。此信息直接來自DBCP配置文檔。

#3

-1

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

BasicDataSource中沒有“sessionTimeZone”成員。使用C3P0這是一個比DBCP“更好”的連接池，甚至更好，如果你在Java EE Web服務器中，用它來初始化JNDI數據源;)