The test java code runs on JDK 8 and database is a 12.1.0.2 CDB. The only thing changes between tests is the JDBC driver.
First the test is run with 11.2 (11.2.0.4) driver. The output (truncated here) will have no time component on the values inserted or returned by setDate and getDate methods. Timestamp method is used to check what is stored in the table. The output is of the following form: the method (getDate or getTimestamp) used, the driver used to insert the value and value returned by get method used.
Date 11.2.0.4.0 2016-10-12 00:00:00 Timestamp 11.2.0.4.0 2016-10-12 00:00:00.0There's no issues to reading these values with a 12.1 (12.1.0.2, ojdbc7.jar) driver, the output will be the same (run the test commenting calls to delete and insert methods)
Date 11.2.0.4.0 2016-10-12 00:00:00 Timestamp 11.2.0.4.0 2016-10-12 00:00:00.0Changing the driver to 12c results in date being inserted with a time component (run test by un-commenting the previously commented methods).
Date 12.1.0.2.0 2016-10-12 12:26:12 Timestamp 12.1.0.2.0 2016-10-12 12:26:12.0Depending on the application logic upgrade to 12c driver could cause issues due to this change in behavior. In such cases going back to 11.2 driver may work in some cases, as getDate would truncate the time component. But the actual value stored has the time component, shown here in the getTimestamp method, so the behavior is not entirely reversible by reverting to the 11.2 driver.
Date 12.1.0.2.0 2016-10-12 00:00:00 Timestamp 12.1.0.2.0 2016-10-12 12:26:12.0There's few bug reports on MOS related this issue (Bug 19297927, Bug 20551186, 1944845.1). But the final outcome seem to be that this deliberate (Bug 17766200) and expected behavior in 12c driver (2177909.1) and that oracle documentation isn't reflecting it (Bug 18124680).
Therefore, if the use of 12c driver is a must then oracle has few workarounds and patches for this. One option is to use a calendar object and set the time components to 0 (1944845.1). Other is to apply patch 21161279 (2177909.1). The patch 21161279 supersedes patch 19297927 which only patched the setDate method. So getDate will return values inserted with time component. Once the patch 21161279 is applied following JVM option must be set in order for the set/get Date method to behave as 11.2 driver's methods. Patch has no effect unless this option is set
-Doracle.jdbc.DateZeroTime=trueOnce patched and JVM option is set the run the test with 12.1 driver. The time components will be set to 0.
Date 12.1.0.2.0 2016-10-12 00:00:00 Timestamp 12.1.0.2.0 2016-10-12 00:00:00.0This behavior and output values are same as 11.2 driver.
Same patch and JVM option could be used for 12.1 driver for JDK 6 as well (ojdbc6.jar).
Useful metalink notes
getDate() Gets Time After Upgrading To 12c [ID 2177909.1]
JDBC 12c Adds Timestamp to java.sql.Date After Upgrading From 11.2.0.4 [ID 1944845.1]
Bug 19297927 : CHANGE OF BEHAVIOR IN JDBC 12.1 DUE TO BUG 14389749 CAUSES QUERIES TO FAIL
Bug 17766200 : GETDATE AND SETDATE DO NOT TRUNCATE TIME IN JDBC 12C
Bug 20551186 : GETDATE DIFFERS WITH THE JDBC DRIVER VERSION 11G AND 12C
Bug 18124680 : GETDATE AND SETDATE DO NOT TRUNCATE TIME IN JDBC 12C BUT DOC STATES OTHERWISE
Bug 17228297 : JDBC DRIVER OJDBC*.JAR 12.1.0.1.0 SPECIFICATION VIOLATION REGRESSION
Related Post
java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver
Update 29 March 2017
Same behavior is observed with 12.2.0.1 driver. To remove the time portion the JVM option (-Doracle.jdbc.DateZeroTime=true) is required. No patch is needed.
Test Table DDL
create table datetest (version varchar2(20), indate date);Java Test Code
public class OJDBC7DateTest {
public static void main(String[] args) throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setUser("asanga");
ds.setPassword("asa");
ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521/onepdb");
Connection con = ds.getConnection();
con.setAutoCommit(false);
System.out.println("Auto commit status : " + con.getAutoCommit());
DatabaseMetaData meta = con.getMetaData();
System.out.println("Driver Name " + meta.getDriverName());
System.out.println("Driver Version " + meta.getDriverVersion());
System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());
System.out.println("delete");
delete(con);
System.out.println("insert");
insert(con,meta.getDriverVersion());
System.out.println("select");
select(con);
con.close();
}
public static void select(Connection con) throws SQLException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
PreparedStatement ps = con.prepareStatement("select * from datetest order by 1");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println("Date " + rs.getString(1) + " " + sdf.format(rs.getDate(2)));
System.out.println("Timestamp " + rs.getString(1) + " " + rs.getTimestamp(2));
}
rs.close();
ps.close();
}
public static void insert(Connection con, String version) throws SQLException {
PreparedStatement ps = con.prepareStatement("insert into datetest values(?, ?)");
Date cd = new Date(System.currentTimeMillis());
ps.setString(1, version);;
ps.setDate(2, cd);
ps.execute();
con.commit();
ps.close();
}
public static void delete(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement("delete from datetest");
ps.execute();
con.commit();
ps.close();
}
}Related Postsjava.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver
getBoolean in 12c JDBC Driver and java.sql.SQLException: Fail to convert to internal representation
