How to represent Oracle Interval in Java

Question

I'm using Java 7 with hibernate 4.

Want to use oracle Interval data type (http://psoug.org/definition/INTERVAL.htm) to represent a interval of a certain number of days.

Wondering what Java Type to use to map this Oracle Interval Object.

I would like to use standard Java objects and not any oracle.sql.* objects as mentioned in this document http://docs.oracle.com/cd/B28359_01/java.111/b31224/datacc.htm.

Here's the table I'm playing with:

---

CREATE TABLE "MyTest" (
    "ID" NUMBER(14,0) NOT NULL
    "DELIVERY_PERIOD" INTERVAL DAY (3) TO SECOND (6), 
    CONSTRAINT "MYTEST_PK" PRIMARY KEY ("ID"));

---

Edit

I've since tried with

@Temporal(TemporalType.TIME)
private java.util.Date deliveryPeriod;

Getting the error:

Caused by: java.sql.SQLException: Invalid column type: getTime not implemented for class oracle.jdbc.driver.T4CIntervaldsAccessor

Edit 2

http://docs.oracle.com/cd/B12037_01/java.101/b10983/datamap.htm

I know mapping it to Java String would work, but I would like to get it is some sort of date object so I don't have to parse it myself.

http://objectmix.com/jdbc-java/41781-oracle10g-oracle-sql-interval-type.html

I would also like to avoid using oracle specific data types such as oracle.sql.INTERVALS

Answer 1

Check this SO answer:

1. You can define an Interval Hibernate UserType

2. Then your Entities will simply use Integer:

   @TypeDef(name="interval", typeClass = Interval.class)
   
   @Type(type = "interval")    
   private Integer interval;
   

3. The Internal UserType is the Java Integer to SQL INTERVAL adapter.

Answer 2

If mapping to String works, then you can either

1. (JPA 2.1) write a converter, or
2. (JPA < 2.1) Write a Hibernate UserType

to convert between String to Interval class.

You can write a POJO yourself for Interval, or make use of JODA Time's Duration