TimeZone Error with EclipseLink and Oracle DB

Question

I am writing an application with uses JPA with GlassFish 3.1.2.2 and EclipseLink 2.3.2. I am using Oracle DB 11g and trying to store date and times with timezone using the TIMESTAMPTZ field type.

With my setup, I am able to persist the date and time with the timezone to the database. (Update- Actually when looking at the actual SQL call, it is only passing in the date and time. Oracle must be appending the timezone when it saves to the DB).

However when retrieving the data back, I am getting the following exception:

Exception [EclipseLink-3002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [oracle.sql.TIMESTAMPTZ@12cbe3f], of class [class oracle.sql.TIMESTAMPTZ], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[startDateTime-->APPT_EVENT.START_DATE_TIME]] with descriptor [RelationalDescriptor(com.ntst.caremanager.server.entities.ApptEvent --> [DatabaseTable(APPT_EVENT)])], could not be converted to [class java.util.Date].

Has anyone ever encountered this before? This is how my the 'startDateTime' field in my entity class is set up:

@Column(name = "START_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date startDateTime;

START_DATE_TIME is defined in the DB with the following DDL:

"START_DATE_TIME" TIMESTAMP (6) WITH TIME ZONE

I have read on the eclipselink wiki here that EclipseLink natively supports oracle's TIMESTAMPTZ without any conversion necessary. I have also tried using the 'Calendar' type instead of 'Date' time in my entity class.

Update: Also tried this

@Convert("timestamptz")
@TypeConverter(name="timestamptz", dataType=TIMESTAMPTZ.class)
@Column(name = "START_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date startDateTime;

No luck still. Oddly enough, with the converters added, persisting to the database no longer works. I get this error when trying to save a value.

Exception [EclipseLink-3002] (Eclipse Persistence Services - 2.3.3.v20120629-r11760): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [12/4/12 7:00 AM], of class [class java.util.Date], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[startDateTime-->APPT_EVENT.START_DATE_TIME]] with descriptor [RelationalDescriptor(com.ntst.caremanager.server.entities.ApptEvent --> [DatabaseTable(APPT_EVENT)])], could not be converted to [class oracle.sql.TIMESTAMPTZ].

I still get the same error when trying to retrieve a value. I also tried updating Glassfish to EclipseLink 2.3.3 and got the same error.

This is my persistence file:

<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="CM-warPU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>CMDEV</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
  <property name="eclipselink.logging.level" value="FINE"/>
  <property name="eclipselink.logging.parameters" value="true"/>
  <property name="eclipselink.target-server" value="SunAS9"/>
</properties>

Has anyone seen this issue before, or see any error I may have made?

Thank you!

Answer 1

You need to specify a converter

@Convert("timestamptz")
@TypeConverter(name="timestamptz", dataType=TIMESTAMPTZ.class)
@Column(name = "START_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date startDateTime;

You also need to configure the server platform so that EclipseLink can get the JDBC connection to do the conversion correctly. This can be done in persistence.xml

<property name="eclipselink.target-server" value="WebLogic"/>

Answer 2

I was able to figure this out, and it actually was due to an omission on my part. I needed to add the ojdbc6.jar file to glassfish's domains\domain1\lib\ext\ directory so EclipseLink could find the oracle.sql.timestamptz type.

That's it. After doing that, I am able to retrieve and store timezone data to the Oracle database using:

@Column(name = "START_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Calendar startDateTime;

No need to use @TypeConverter or @Converter as EclipseLink automatically will convert it in this version. You also need to use Calendar, not Date so that you can retrieve the TimeZone information.

I'm leaving this up for anyone else who might encounter this.