In our DB we have multiple entities with Date fields. Oracle sees every date as the same, with a date and a time part. JPA entities however distinguish via the annotaton @Temporal. When we want to omit the time part we annotate Date fields with @Temporal(TemporalType.DATE) and Oracle saves 00:00:00, if not, we just leave it without annotation.
Example:
@Entity
public class MyEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private long myentityId;
@Temporal(TemporalType.DATE)
private Date importantDate; //01.01.2015 00:00:00
private Date creationDate; //01.01.2015 10:35:51
...
}
...
MyEntity me = new MyEntity();
me.setImportantDate(new Date());
me.setCreationDate(new Date());
...
We upgraded from Oracle 11 to Oracle 12 and now the time part of importantDate is no longer omitted!
I tested this extensively on both databases with exactly the same program. This actually breaks our application.
What can I do to restore the previous behaviour?
UPDATE 1: I narrowed the problem down: driver ojdbc6 12.1.0.1.0 has the problem, ojdbc6 11.2.0.3.0 works as intended. (both using an Oracle 12 DB)
Is this a continuation of the timestamp problem fixed in 11.1? (http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01)
UPDATE 2: Since Hibernate does not seem to be the problem, I wrote an example with pure JDBC:
OracleDataSource ods = new OracleDataSource();
...
Connection conn = ods.getConnection();
PreparedStatement ps = conn.prepareStatement("UPDATE MyEntity SET importantDate = ? WHERE myentityId = 4385");
ps.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
ps.execute();
...
This snippet behaves different when switching between ojdbc6 11.1 and ojdbc6 12.1.
It will store as TIMESTAMP in the database. @Temporal private Date joinedDate; Above code will store value looks like 08-07-17 04:33:35.870000000 PM. If we want to store only the DATE in the database, We can use/define TemporalType .
@Temporal annotation is available in javax. persistent package and introduced in Java Persistence 1.0. This annotation used with fields/properties which is a type of Date or Calendar and there are three possible types we can have @Temporal(TemporalType. DATE), @Temporal(TemporalType.
You can define the preferred mapping with the @Temporal annotation. As you can see in the following code snippet, it takes a TemporalType enum as a value. The enum allows you to select the SQL type (DATE, TIME or TIMESTAMP) which you want to use.
The @Temporal annotation has the single parameter value of type TemporalType. It can be either DATE, TIME or TIMESTAMP, depending on the underlying SQL type that we want to use for the mapping.
We have conntacted the Oracle Support and they replied as follows (unfortunately I'm not able to provide link to the answer because an Oracle Support Account is needed):
The new behaviour works as intended:
In JDBC 12.1.0.1, getDate and setDate do not truncate the time component of the date. This behavior is different to JDBC 11.2.0.X, where the time component is truncated. As per bug 14389749, 17228297 this change is deliberate and the 12c driver's behavior is correct.
The workarounds provided work for me:
Workaround #1: Modify application to not insert the time component (e.g. with a static UtilMethod)
public static Date truncateTime(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);
return calendar.getTime();
}
Workaround #2: Download and apply patch 19297927 from MOS: (My Oracle Support)
After patching replace ojdb7.jar in %Oracle_Home%\oracle_common\modules\oracle.jdbc_12.1.0
and add -Doracle.jdbc.DateZeroTime=true
to your JVM Arguments
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With