Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

@Temporal(TemporalType.DATE) with Oracle 12

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.

like image 686
Thomas Avatar asked Oct 20 '15 08:10

Thomas


People also ask

What is @temporal TemporalType date?

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 .

What is @temporal in spring boot?

@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.

Which annotation is used to force Hibernate to store years months and days?

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.

What is temporal annotation in hibernate?

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.


1 Answers

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)

  1. Click on Patches & Updates tab.
  2. Enter the above patch number and Click on Search.
  3. Click on the patch number corresponds to your platform from the list
  4. Click on Download button to download the patch.
  5. Read any applicable notes before downloading, then click the Download button.

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

like image 154
themenace Avatar answered Sep 23 '22 18:09

themenace