Oracle dilemma - Date or Timestamp

francof · **Joined:** Fri Aug 17, 2007 2:11 pm **Posts:** 19

Hi

I apologize if this question has been answered already. I posted this wrongly in the Tools forum and rightly got no response so I'm hoping I get some help here.

I am running an Oracle 9i database and using a 10G driver - ojdbc14_10-2-0-3.jar (copied to lib folder) , we are upgrading our database to 10G soon.

All my tables have timestamp columns for audit purposes.

Code:

CREATE TABLE CATALOG
(
  CATALOG_ID     INTEGER                        NOT NULL,
  CATALOG_NAME   VARCHAR2(50 BYTE)              NOT NULL,
  DATE_CREATED   TIMESTAMP(6)                   DEFAULT systimestamp          NOT NULL,
  ADDED_BY       INTEGER                        NOT NULL,
  DATE_MODIFIED  TIMESTAMP(6)                   DEFAULT systimestamp          NOT NULL,
  MODIFIED_BY    INTEGER                        NOT NULL,
  ENABLED        CHAR(1 BYTE)                   DEFAULT 'N'                   NOT NULL,
  VERSION_ID     INTEGER                        DEFAULT 1                     NOT NULL
);

After reverse engineering using Hibernate Tools,
the generated code maps my timestamp columns to Serializable

Code:

   @Column(name = "DATE_CREATED", nullable = false)
   @NotNull
   public Serializable getDateCreated() {
      return this.dateCreated;
   }

So I then modified my timestamp fields to Date datatype in my Oracle table and mapped it as

Code:

   
@Temporal(TemporalType.DATE)
@Column(name = "DATE_CREATED", nullable = false)
@NotNull
public Date getDateCreated() {
   return this.dateCreated;
}

The problem with this mapping was that Hibernate strips off time after an update.

So I annotated the columns this way
@Column(name = "DATE_CREATED", updatable = false, insertable = false)
and wrote a before insert/update trigger to update these datetime values.

The only problem I might encounter in future is when I have to really update a datetime field from the UI

Anyone here using an Oracle 9i db using timestamp columns in a Hibernate application and can give me some tips? - Basically I would like to update Oracle timestamp columns using Hibernate.

Thanks
Franco

lucas · **Posted:** Mon Apr 28, 2008 9:05 pm

Franco,

I am not sure this will be 100% helpful as it more applies to TIMESTAMP(3) not TIMESTAMP(6). A java.util.Date object cannot represent the nanos in a TIMESTAMP(6).

The problem is that the driver in the ojdbc14_10-2-0-3.jar file works in an oracle-specific way that treats "Date" as a date, ie. no time, and a "Timestamp" as a date and time value. What you are seeing is the same behaviour as we saw when we upgraded to the "jdbc14" version of Oracle's JDBC driver.

What we do is have all our date columns use TIMESTAMP(3) (same precision as a java.util.Date), and then use a java.util.Date as the entity property type. We set the "oracle.jdbc.J2EE13Compliant" JDBC connection property to true so that the Oracle driver behaves. That setting has other effects, so you'd need to do some thorough testing.

Finally all your "date" query parameters need to be set with the Query#setTimestamp(String/int, java.util.Date) method, NOT the setDate method. If you call setDate, you get similar behaviour to what you are seeing - the time portion of the java.util.Date is truncated.

I think the other way to do it is to map your entity properties as java.sql.Timestamp, but I've no experience with that. We wanted to use straight java.util.Date objects as much as we could.

sanne.grinovero · **Posted:** Tue Apr 29, 2008 3:17 am

Hi,
usually the audit columns are updated by some DB trigger:
if you don't need to change this property by java code
you could just avoid all precision problems by setting

Code:

@Column(updatabable=false,insertable=false)

In this way hibernate will not mention this column when generating update or insert statements.[/code]