JDBC SQL types vs Oracle SQL types

I have just had a frustrating experience with Oracle and JDBC. Imagine you want to select a set of records from a table that fall between two date/time values, and you want to do this with a prepared statement. Using the standard java.sql.Date and PreparedStatement.setDate() method will not work properly with Oracle 9.

Here's what I had to do:

((OraclePreparedStatement)stmt).setDATE(2, new oracle.sql.DATE(new java.sql.Timestamp(startDate.getTime())));

:D

An explanation:

First, I needed to cast to OraclePreparedStatement to have access to the setDATE() function (as opposed to PreparedStatement.setDate()). The setDATE() function takes the crucial oracle.sql.DATE object that maps between JDBC SQL types and the Oracle DATE SQL type. Think of it this way; I can instantiate oracle.sql.DATE with a java.sql.Date, Timestamp, Time, String or other object, and it will try to map those objects to the Oracle DATE SQL type. In this case, I needed an object that preserved the time information along with the date. Therefore, I instantiated the oracle.sql.DATE object with java.sql.Timestamp. Had I instantiated the DATE object with java.sql.Date, it may have succeeded, but time information would be zeroed out.

The upside to this method is that it is an intuitive way to handle Oracle dates with prepared statements. The downside is its dependence on Oracle JDBC extensions. But, with the right DAO architecture, redesign can be minimal when things change.

An alternative to the previous method would be to map a java.sql.Timestamp object to the DATE field using the following:

stmt.setTimestamp(2, new java.sql.Timestamp(startDate.getTime()));

I am to understand from the Oracle JDBC FAQ that since Oracle 9.2, the Oracle DATE SQL type has been mapped to java.sql.Date. The FAQ calls attention to the fact that there will be conflicts if you depend on time information in a DATE field. The above methods both work with Oracle 9.2.0.6.0, so I am not sure what aspect of functionality will be impacted.

Below is a quick summary:

• java.util.Date - Holds date and time information; really of no use here other than to get the date/time into the method.
  
• java.sql.Date - Corresponds to a generic JDBC DATE SQL type. Holds only the date part (2007-06-06).
  
• java.sql.Timestamp - Corresponds to a generic JDBC TIMESTAMP SQL type. Holds date AND time (2007-06-06 02:32:04).
  
• Oracle SQL DATE type - Stores date and time information, but no nanoseconds. Previous to Oracle 9.2, mapped to java.sql.Timestamp. Post 9.2, maps to java.sql.Date.
• Oracle SQL TIMESTAMP type - New in Oracle 9.2. Stores date and time information; includes nanoseconds. Since Oracle 9.2, mapped to java.sql.Timestamp.
  
• oracle.sql.DATE - Corresponds to the Oracle DATE SQL type. Holds date and time, but in order to function properly, should be instantiated with a java.sql.Timestamp object that also holds date and time.
  
• PreparedStatement.setDate(int, java.sql.Date) - Set a JDBC DATE SQL type field in the prepared statement. Only preserves the date part.
  
• PreparedStatement.setTimestamp(int, java.sql.Timestamp) - Set a JDBC TIMESTAMP SQL type field in the prepared statement. Preserves date and time, and can be tricked into setting an Oracle DATE type field with date and time information.
  
• OraclePreparedStatement.setDATE(int, oracle.sql.DATE) - Set an Oracle DATE SQL type field in the prepared statement. Preserves date and time. In my opinion, the most intuitive method (once you understand it) to set Oracle DATE fields in prepared statements.