title: Setting connection timezone with Spring and DBCP and MySQL
body: <p><strong>My Enviroment</strong></p>
  
  <ul>
  <li>Java 5</li>
  <li>Spring 2.5.5</li>
  <li>DBCP DataSource <em>(org.apache.commons.dbcp.BasicDataSource)</em></li>
  <li>MySQL</li>
  </ul>
  
  <p><strong>Similar posts</strong></p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/2805907/setting-session-timezone-with-spring-jdbc-oracle">Setting session timezone with spring jdbc oracle</a></li>
  </ul>
  
  <p><strong>Links</strong></p>
  
  <ul>
  <li><a href="http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to-manage-Time-Zone-in-MySQL" rel="noreferrer">http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to-manage-Time-Zone-in-MySQL</a></li>
  </ul>
  
  <p><strong>My Problem</strong></p>
  
  <ul>
  <li>I need to set on my connection the timezone, aiming to prevent the conversions when dealing with TIMESTAMP columns.</li>
  </ul>
  
  <p><strong>My Idea/research</strong></p>
  
  <ul>
  <li><p>DBCP Connection Pool did not mention anything around timezone. <a href="http://commons.apache.org/dbcp/configuration.html" rel="noreferrer">LINK</a></p></li>
  <li><p>What I investigate and thought that was oK is described on <a href="http://forums.oracle.com/forums/thread.jspa?threadID=722355" rel="noreferrer">THIS</a> post, exemplifying is:</p></li>
  </ul>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close"&gt;
      &lt;property name="URL" value="${database.url}" /&gt; 
      &lt;property name="user" value="${database.username}" /&gt; 
      &lt;property name="password" value="${database.passwd}" /&gt; 
      &lt;property name="connectionCachingEnabled" value="true"/&gt;
      &lt;property name="sessionTimeZone" value="GMT-3"/&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p><strong>Asking for help area :)</strong></p>
  
  <ul>
  <li>But this is not working!!</li>
  <li>What I want here is a simple way, preferentially using Spring to configure the timezone on jdbc connection.</li>
  </ul>
  
  <p>Thanks in advance for any help/tips/advice/knowledge share </p>
  
  <hr>
  
  <p><strong>SOLUTION:</strong></p>
  
  <p>My Solution was based on tips collected on this post! Thanks for all!</p>
  
  <pre><code>(...)
  @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;
  }
  (...)
  </code></pre>
  
  <p>and on my Spring configuration file:</p>
  
  <pre><code>&lt;bean id="dataSource" class="com.my.package.dbcp.TimezoneEnabledDataSource" destroy-method="close"&gt;
      (...)
      &lt;property name="timezone" value="${database.timezone}" /&gt;
      (...)
  &lt;/bean&gt;
  </code></pre>
  
  <p>I hope this post can help someone in the future. Any question ping me!</p>