Setting session timezone with spring jdbc oracle

Question

I have a spring/jdbc/oracle 10g application. The Oracle server database timezone is set to GMT + 2 JVM timezone is GMT + 2 (even though it doesn't matter in my case).

I have a stored procedure that performs some date operations. The problem is that session timezone is different(GMT) than database timezone even though I do not set session timezone explicit in my code/configuration.

As far as I know the session timezone is by default equal to database timezone. Any idea why is the session timezone different than database timezone or how can I configure it in spring configuration (org.apache.commons.dbcp.BasicDataSource) ?

Thank you.

Answer 1

The correct way is to use DelegatingDataSource, retrieve OracleConnection object from the original data source and call OracleConnection.setSessionTimeZone() with the appropriate parameter.

C3P0 code looks like:

private Object[] timeZoneArgs = new Object[] { "Europe/Berlin" };

@Override
public Connection getConnection() throws SQLException {
    Connection conn = super.getConnection();
    try {
        final Method setSessionTimeZoneMethod = OracleConnection.class.getMethod("setSessionTimeZone", String.class);
        final C3P0ProxyConnection castCon = (C3P0ProxyConnection) conn;
        castCon.rawConnectionOperation(setSessionTimeZoneMethod, C3P0ProxyConnection.RAW_CONNECTION, timeZoneArgs);
        return conn;
    } catch (Exception e) {
        log.error("setSessionTimeZone failed " + e.getMessage());
        return conn;
    }
}

Answer 2

I solved this problem by upgrading Oracle's JDBC drivers from v10.2.0.1.0 to v11.2.0.3. No changes to my java code were required.

Source: Spring forums.

Answer 3

In spring configuration, add below VM options before running the application:

-Duser.timezone=EDT

Also make sure your pom has latest jdbc driver

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>