6

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

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
JackDev
  • 11,003
  • 12
  • 51
  • 68
  • 1
    Look at JPA speficication 2.0 (JSR 317) and search for any section talking about data types in general. I'm afraid you will will not find too much there. JPAs support only numbers, strings and dates. They even do not support any collections nor XMLs datatypes, only scalars are supported. Maybe you have to wait for ver. 3.0. JPAs must cover "greatest common divisor" of all the databases. All the Oracle's special features are considered "illegal" because it would break vendor independence. – ibre5041 Aug 19 '14 at 06:12

2 Answers2

4

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.

Community
  • 1
  • 1
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
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

Adrian Shum
  • 38,812
  • 10
  • 83
  • 131