How to map Postgresql Interval type?

jake341 · **Joined:** Sun Apr 22, 2007 2:02 pm **Posts:** 3

Is it possible to map a column (TIMETAKEN) in a Postgresql 7.3 database that has type interval? If so what hibernate mapping type should I use?

I have attached the mapping file and the error that I get when I attempt to verify the schema. The below mapping type uses big_decimal, but i also tried timestamp, long, integer and short, all with the same result.

More info on the interval type can be found at http://www.postgresql.org/docs/current/static/datatype-datetime.html

Thanks

Jake

Hibernate version:3.2.1.ga

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="timesheets.hibernate">

    <class name="TimeSheet" table="TIMESHEET">
        <id name="id" column="TIMESHEET_ID" type="integer">
            <generator class="native"/>
        </id>
        <property name="userId" type="integer" column="USERID"/>
      <property name="projectType" type="character" column="PROJECT_TYPE"/>
      <property name="description" type="string" column="DESCRIPTION"/>
      <property name="logtime" type="timestamp" column="LOGTIME"/>
      <property name="timetaken" type="big_decimal" column="TIMETAKEN"/>
   
      <many-to-one name="activity" class="Activity" column="ACTIVITY" lazy="false" unique="false" not-null="true"/>
      <many-to-one name="project" class="Project" column="PROJECT_NO" lazy="false" unique="false" not-null="true"/>
    </class>

</hibernate-mapping>

Full stack trace of any exception that occurs:

Exception in thread "main" org.hibernate.HibernateException: Wrong column type: TIMETAKEN, expected: numeric(19, 2)
at org.hibernate.mapping.Table.validateColumns(Table.java:261)
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1080)
at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:116)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:317)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1291)
at com.vcint.timesheets.hibernate.HibernateSessionFactory.initialise(HibernateSessionFactory.java:52)
at com.vcint.timesheets.hibernate.HibernateSessionFactory.getSession(HibernateSessionFactory.java:32)
at com.vcint.timesheet.SchemaTest.main(SchemaTest.java:16)

Name and version of the database you are using: Postgresql 7.3

batmat · **Posted:** Sun Apr 22, 2007 2:59 pm

I would advise using a custom UserType implementation to persist this type. That's easy to do and this will give a lot of flexibility.

As explained in this article, you don't have to write your own Hibernate custom type to map the PostgreSQL interval column to a Java Duration object. All you need to do is use the hibernate-types project.

So, after adding the proper Hibernate dependency:

Code:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.6.0</version>
</dependency>

You just have to use the @TypeDef annotation to register the PostgreSQLIntervalType:

Code:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    typeClass = PostgreSQLIntervalType.class,
    defaultForType = Duration.class
)
@TypeDef(
    typeClass = YearMonthDateType.class,
    defaultForType = YearMonth.class
)
public class Book {

    @Id
    @GeneratedValue
    private Long id;

    @NaturalId
    private String isbn;

    private String title;

    @Column(
        name = "published_on",
        columnDefinition = "date"
    )
    private YearMonth publishedOn;

    @Column(
        name = "presale_period",
        columnDefinition = "interval"
    )
    private Duration presalePeriod;

    public Long getId() {
        return id;
    }

    public Book setId(Long id) {
        this.id = id;
        return this;
    }

    public String getIsbn() {
        return isbn;
    }

    public Book setIsbn(String isbn) {
        this.isbn = isbn;
        return this;
    }

    public String getTitle() {
        return title;
    }

    public Book setTitle(String title) {
        this.title = title;
        return this;
    }

    public YearMonth getPublishedOn() {
        return publishedOn;
    }

    public Book setPublishedOn(YearMonth publishedOn) {
        this.publishedOn = publishedOn;
        return this;
    }

    public Duration getPresalePeriod() {
        return presalePeriod;
    }

    public Book setPresalePeriod(Duration presalePeriod) {
        this.presalePeriod = presalePeriod;
        return this;
    }
}

Now, when persisting the Book entity:

Code:

entityManager.persist(
    new Book()
        .setIsbn("978-9730228236")
        .setTitle("High-Performance Java Persistence")
        .setPublishedOn(YearMonth.of(2016, 10))
        .setPresalePeriod(
            Duration.between(
                LocalDate
                    .of(2015, Month.NOVEMBER, 2)
                    .atStartOfDay(),
                LocalDate
                    .of(2016, Month.AUGUST, 25)
                    .atStartOfDay()
            )
        )
);

Hibernate will execute the proper SQL INSERT statement:

Code:

INSERT INTO book (
    isbn,
    presale_period,
    published_on,
    title,
    id
)
VALUES (
    '978-9730228236',
    '0 years 0 mons 297 days 0 hours 0 mins 0.00 secs',
    '2016-10-01',
    'High-Performance Java Persistence',
    1
)

When fetching the Book entity, we can see that the Duration attribute is properly fetched from the database:

Code:

Book book = entityManager
    .unwrap(Session.class)
    .bySimpleNaturalId(Book.class)
    .load("978-9730228236");

assertEquals(
    Duration.between(
        LocalDate
            .of(2015, Month.NOVEMBER, 2)
            .atStartOfDay(),
        LocalDate
            .of(2016, Month.AUGUST, 25)
            .atStartOfDay()
    ),
    book.getPresalePeriod()
);

For more details, check out this article.

jake341 · **Joined:** Sun Apr 22, 2007 2:02 pm **Posts:** 3

I am unable to make this approach work due to the following problem:

The user type must return a list of types from the

Code:

public int[] sqlTypes() {

method, and all of the types i tried returned errors like:

Exception in thread "main" org.hibernate.HibernateException: Wrong column type: TIMETAKEN, expected: timestamp
at org.hibernate.mapping.Table.validateColumns(Table.java:261)
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1080)
...etc

i also tried to return Types.OTHER from the sqlTypes method but got the following error:

Exception in thread "main" org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
at org.hibernate.dialect.Dialect.getTypeName(Dialect.java:289)

Interval is a non-jdbc column type. Postgres's drivers return it as a String representation (the .getObject(...) method does not work). Is there a standard way of implementing user types for non-jdbc column types in hibernate?

jake341 · **Joined:** Sun Apr 22, 2007 2:02 pm **Posts:** 3

after a bit more head scratching & a look at the hibernate source code i came up with the below workaround. it is a pretty poor hack, and if anyone has any better ideas on how to do this then i would be interested to hear them.

first of all - you need to extend the postgres dialect to map the interval datatype to an unused entry from java.sql.Types, in this case i used Types.OBJECT. you must specify this class as the dialect when configuring hibernate.

Code:

package timesheets.postgres;

import java.sql.Types;

import org.hibernate.dialect.PostgreSQLDialect;

/**
 *   extend the postgres dialect with the interval data type 
 */
public class ExtendedPostgreSQLDialect extends PostgreSQLDialect {

   public ExtendedPostgreSQLDialect() {
      super();
      registerColumnType( Types.OTHER, "interval" );
   }
}

then you need create a custom user type. i have included my implementation below, but it is pretty limited, especially in that it only handles intervals of hours & minutes, however this shouldn't be too difficult to improve. the key factor is the it returns Types.OTHER from the sqlTypes() method.

Code:

package timesheets.postgres;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DecimalFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

/**
 *   maps the postgres-only type interval to time in minutes as an Integer  
 */
public class IntervalUserType implements UserType {
   
   private static Pattern intervalPattern = Pattern.compile(".*([0-9]{2}):([0-9]{2})");
   private static DecimalFormat intervalFormat = new DecimalFormat("00");

   public Object assemble(Serializable cached, Object owner) throws HibernateException {
      return cached;
   }

   public Object deepCopy(Object value) throws HibernateException {
      return value;
   }

   public Serializable disassemble(Object value) throws HibernateException {
      return (Serializable) value;
   }

   public boolean equals(Object arg0, Object arg1) throws HibernateException {
      return arg0.equals(arg1);
   }

   public int hashCode(Object object) throws HibernateException {
      return object.hashCode();
   }

   public boolean isMutable() {
      return false;
   }

   public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner) throws HibernateException, SQLException {
      String interval = resultSet.getString(names[0]);
      if (resultSet.wasNull() || interval == null) {
         return null;
      }
      Matcher intervalMatch = intervalPattern.matcher(interval);
      if (!intervalMatch.matches()) {
         throw new HibernateException("Interval field " + names[0] + " containing " + interval + " must match " + intervalPattern.pattern());
      }
      int hours = Integer.parseInt(intervalMatch.group(1));
      int minutes = Integer.parseInt(intervalMatch.group(2));
      return new Integer(hours * 60 + minutes);
   }

   public void nullSafeSet(PreparedStatement statement, Object value, int index) throws HibernateException, SQLException {
      if (value == null) {
         statement.setNull(index, Types.VARCHAR);
      } else {
         int interval = ((Integer)value).intValue();
         int hours = interval / 60;
         int minutes = interval - (hours * 60);
         statement.setString(index, intervalFormat.format(hours) + ":" + intervalFormat.format(minutes));
      }
   }
   

   public Object replace(Object original, Object target, Object owner) throws HibernateException {
      return original;
   }

   public Class returnedClass() {
      return Integer.class;
   }

   public int[] sqlTypes() {
      return new int[] {Types.OTHER};
   }

}

lastly you need to map the field as follows:

Code:

<property name="timetaken" column="TIMETAKEN">
    <type name="timesheets.postgres.IntervalUserType"/>
</property>

bpgergo · **Joined:** Fri Jun 29, 2007 5:59 am **Posts:** 4

In my application this code fails when the interval returned from the database is several days:

Code:

private static Pattern intervalPattern = Pattern.compile(".*([0-9]{2}):([0-9]{2})");

I think it is more reliable to use the PGInterval class http://jdbc.postgresql.org/development/privateapi/org/postgresql/util/PGInterval.html

This is my working solution:

Code:

package foo.bar.hibernate.type;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.postgresql.util.PGInterval;


/**
 * Postgres Interval type
 * 
 * @author bpgergo
 * 
 */
public class Interval implements UserType {
    private static final int[] SQL_TYPES = { Types.OTHER };

    @Override
    public int[] sqlTypes() {
        return SQL_TYPES;
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Integer.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException {
        String interval = rs.getString(names[0]);
        if (rs.wasNull() || interval == null) {
            return null;
        }
        PGInterval pgInterval = new PGInterval(interval);
        Date epoch = new Date(0l);
        pgInterval.add(epoch);
        return (Integer)(epoch.getTime() / 1000);
    }

    public static String getInterval(int value){
        return new PGInterval(0, 0, 0, 0, 0, value).getValue();
    }


    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.VARCHAR);
        } else {
            //this http://postgresql.1045698.n5.nabble.com/Inserting-Information-in-PostgreSQL-interval-td2175203.html#a2175205
            st.setObject(index, getInterval(((Integer) value).intValue()), Types.OTHER);
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        return original;
    }

}

also, see here how I use it: http://stackoverflow.com/questions/1945615/how-to-map-the-type-interval-in-hibernate/6139581#6139581