Is there a Problem with JPA Entities, Oracle 10g and Calendar Type properties?

Question

I'm experiencing the following very annoying behaviour when using JPA entitys in conjunction with Oracle 10g.

Suppose you have the following entity.

@Entity
@Table(name = "T_Order")
public class TOrder implements Serializable {
    private static final long serialVersionUID = 2235742302377173533L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "activationDate")
    private Calendar activationDate;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Calendar getActivationDate() {
        return activationDate;
    }

    public void setActivationDate(Calendar activationDate) {
        this.activationDate = activationDate;
    }
}

This entity is mapped to Oracle 10g, so in the DB there will be a table T_ORDER with a primary key NUMBER column ID and a TIMESTAMP column activationDate.

Lets suppose I create an instance of this class with the activation date 15. Sep 2008 00:00AM. My local timezone is CEST which is GMT+02:00. When I persist this object and select the data from the table T_ORDER using sqlplus, I find out that in the table actually 14. Sep 2008 22:00 is stored, which is ok so far, because the oracle db timezone is GMT.

But now the annoying part. When I read this entity back into my JAVA program, I find out that the oracle time zone is ignored and I get 14. Sep 2008 22:00 CEST, which is definitly wrong.

So basically, when writing to the DB the timezone information will be used, when reading it will be ignored.

Is there any solution for this out there? The most simple solution I guess would be to set the oracle dbs timezone to GMT+02, but unfortunatly I can't do this because there are other applications using the same server.

We use the following technology

MyEclipse 6.5 JPA with Hibernate 3.2 Oracle 10g thin JDBC Driver

Tutt

Answer 1

You should not use a Calendar for accessing dates from the database, for this exact reason. You should use java.util.Date as so:

@Temporal(TemporalType.TIMESTAMP)
@Column(name="activationDate")
public Date getActivationDate() {
    return this.activationDate;
}

java.util.Date points to a moment in time, irrespective of any timezones. Calendar can be used to format a date for a particular timezone or locale.

Penurious

Answer 2

I already had my share of problems with JPA and timestamps. I've been reading in the oracle forums and please check the following:

• The field in the database should be TIMESTAMP_TZ and not just TIMESTAMP
• Try adding the annotation @Temporal(value = TemporalType.TIMESTAMP)
• If you don't really need the timezone, put in a date or timestamp field.

Triumphant

Thank you for answering. I stumbled over the same oracle forum thread that you mention above. Unfortunatly all the solutions given are related to something called EclipseLink and I really don't want to switch to a new persistance API implementation. – Tutt

The annotations are JPA annotations thus not specific for eclipselink. I used it with hibernate and they work OK. – Triumphant