Oracle JDBC Driver and Spring 2.0.X Timestamp issue

Oracle JDBC Driver (ojdbc14.jar)

Spring Framework 2.0

import org.springframework.jdbc.support.rowset.SqlRowSet;

public class QueryDAO extends JdbcDaoSupport {

    public SqlRowSet loadSqlRowSet(String sSQL) {
        return getJdbcTemplate().queryForRowSet(sSQL);
    }

}

Using Spring JDBC in such way Timestamp fields are recovered without time information (hours, minutes and seconds set to 0). It seems some issue relative to Sun or Oracle, but there is a workaround in order to achieve desired result.

SqlRowSet rset = 
    jdbcTemplate.queryForRowSet("select cast(sysdate as timestamp) from dual");
while (rset.next()) {
    System.out.println("-> " + 
        ((oracle.sql.TIMESTAMP)rset.getObject(1)).timestampValue());
}

Our batch process failed last night because of this…

Oracle JDBC: Implicit Connection Caching bug exposed

References:

Note. Following code behaves equal in standalone or application server environments.

Right way

// IBM's example
// The following property ordering results in
// a cache being created.

OracleDataSource ods = new OracleDataSource();

ods.setConnectionCachingEnabled(true);
ods.setURL("jdbc:" + "oracle:thin:@localhost:1521:IBM");
ods.setConnectionCacheName("ICC");

ods.setUser("scott");
ods.setPassword("manager1");

Wrong way

// ORACLE's example
// The following property ordering causes no exception,
// but no cache is created.

// create a DataSource
OracleDataSource ods = new OracleDataSource();

// set cache properties
java.util.Properties prop = new java.util.Properties();
prop.setProperty("MinLimit", "2");
prop.setProperty("MaxLimit", "10");

// set DataSource properties
String url = "jdbc:" + "oracle:thin:@";
ods.setURL(url);
ods.setUser("hr");
ods.setPassword("hr");
ods.setConnectionCachingEnabled(true); // be sure set to true
ods.setConnectionCacheProperties (prop);
ods.setConnectionCacheName("ImplicitCache01"); // this cache's name

Oracle documentation includes an example with the incorrect order and has no reference to the bug!

Oracle JDBC date manipulation

Consider following SQL queries:

select count(1)
from request
where request_date between
to_date(?, 'DD-MM-YYYY HH24:MI:SS') and
to_date(?, 'DD-MM-YYYY HH24:MI:SS')

 

select count(1)
from request
where request_date between ? and ?

It seems both queries should be similar. Moreover, I thought the second one would be the fastest. However, the sample below shows another reality.

import oracle.jdbc.pool.OracleDataSource;

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * Sample class demonstrating Oracle 10g date manipulation
 */
public class OracleTest extends JdbcDaoSupport {

  // Query using Oracle date conversion
  static final String countRequests1 =
    "select count(1) " +
        "from request " +
    "where request_date between to_date(?, 'DD-MM-YYYY HH24:MI:SS') and " +
                            "to_date(?, 'DD-MM-YYYY HH24:MI:SS')";

  // Query using JDBC date conversion
  static final String countRequests2 =
    "select count(1) " +
        "from request " +
    "where request_date between ? and ?";

  // Compare two queries
  private void doQueries() throws Exception {

    // Oracle Datasource
    OracleDataSource ds = new OracleDataSource();
    ds.setURL("jdbc:oracle:thin:@127.0.0.1:2000:PREMIDBP");
    ds.setUser("sdf_monitor");
    ds.setPassword("sdf_monitor");
    SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(ds);

    // Date parameters
    Calendar c = Calendar.getInstance();
    Date dateTo = (Date)c.getTime().clone();
    c.add(Calendar.MINUTE, -1);
    c.set(Calendar.MILLISECOND, 0);
    Date dateFrom = c.getTime();

    // String parameters
    String dateFromString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateFrom);
    String dateToString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateTo);

    // Query countRequests1
    long initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests1, dateFromString, dateToString);
    System.out.println("Query 1: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

    // Query countRequests2
    initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests2, dateFrom, dateTo);
    System.out.println("Query 2: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

  }

  // Launcher
  public static void main(String... args) throws Exception {
        new OracleTest().doQueries();
  }

}

With my data results are:

Query 1: 1208 ms.
Query 2: 20038 ms.

Curious behaviour.

Edited!

An explanation can be always found (Spanish link): JDBC 9.x y las variables de Timestamp de Java