java - 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 spring jdbc oracle

links

• http://www.mysqlfaqs.net/mysql-faqs/general-questions/how-to-manage-time-zone-in-mysql

my problem

• i need set on connection timezone, aiming prevent conversions when dealing timestamp columns.

my idea/research

• dbcp connection pool did not mention around timezone. link

• what investigate , thought ok 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 area :)

• but not working!!
• what want here simple way, preferentially using spring configure timezone on jdbc connection.

thanks in advance help/tips/advice/knowledge share

---

solution:

my solution based on tips collected on post! 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);     } {         try {             statement.close();         } catch (sqlexception e) {             log.warn("error while closing statement", e);         }     }     if(log.isdebugenabled())         log.debug("set time_zone("+timezone+") connection, succeed!");     return conn; } (...) 

and on spring configuration file:

<bean id="datasource" class="com.my.package.dbcp.timezoneenableddatasource" destroy-method="close">     (...)     <property name="timezone" value="${database.timezone}" />     (...) </bean> 

i hope post can in future. question ping me!

if data source doesn't have such property, can extend , add property:

public timezoneenableddatasource extends basicdatasource {     private string timezone;     //getter , setter      @override         public connection getconnection() {         connection c = super.getconnection();         // execute query: set time_zone = '-8:00'         return c;     } } 

see here http://www.electrictoolbox.com/mysql-set-timezone-per-connection/ query details.

mysql documentation writes:

per-connection time zones. each client connects has own time zone setting, given session time_zone variable. initially, session variable takes value global time_zone variable, client can change own time zone statement:

mysql> set time_zone = timezone;

you can check if c3p0 doesn't have built-in.