Introduction

In this article, we are going to see how to map the PostgreSQL interval column type to a Java Duration object using Hibernate and the Hypersistence Utils project.

Another very useful feature introduced by the Hypersistence Utils project is that all types extending the ImmutableType can now be treated as standard org.hibernate.type.Type, therefore enabling a much better Hibernate Core API integration.

Domain Model

Assuming we have the following book database table that defines a presale_period column of the interval type.

We can map the book table to a Book JPA entity using the PostgreSQLIntervalType and YearMonthDateType from Hypersistence Utils.

For Hibernate 6, the mapping will look as follows:

@Entity(name = "Book")
@Table(name = "book")
public class Book {

    @Id
    @GeneratedValue
    private Long id;

    @NaturalId
    private String isbn;

    private String title;

    @Type(YearMonthDateType.class)
    @Column(
        name = "published_on", 
        columnDefinition = "date"
    )
    private YearMonth publishedOn;

    @Type(PostgreSQLIntervalType.class)
    @Column(
        name = "presale_period", 
        columnDefinition = "interval"
    )
    private Duration presalePeriod;
}

And for Hibernate 5, like this:

@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;
}

The first thing to notice is that the entity property setter methods follow the Fluent interface design pattern.

The second thing you can notice is that we are defining a @NaturalId business key that allows us to fetch the entity based on the natural identifier even if we don’t know the Primary Key value of the associated table record.

The third thing you will notice is that we either defined multiple @Type or @TypeDef annotations to register the custom Hibernate Types.

To map the Java YearMonth type, we can use the YearMonthDateType as explained in this article.

To map a PostgreSQL interval column to a Java Duration, we need to use the PostgreSQLIntervalType offered by the Hypersistence Utils project.

Java Duration to PostgreSQL interval column mapping

When persisting a Book entity:

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()
            )
        )
);

We can see that Hibernate generate the proper SQL INSERT statement:

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 presalePeriod Java Duration attribute is properly populated with the associated PostgreSQL interval column value.

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()
);

While persisting and fetching the Book entity, as well as executing any JPQL and Criteria API query is rather straightforward, handling native SQL query result sets is more challenging when dealing with column types that are not natively supported by Hibernate.

If Hibernate encounters a JDBC column type for which it does not have a registered Hibernate Type, then a No Dialect mapping for JDBC type exception is thrown.

As I explained in this article, you can address this issue by specifying the right Hibernate Type to handle a given JDBC column type.

In the following native SQL query example, you can see that the published_on result set column alias is configured to use the YearMonthDateType while the presale_period column alias is handled by the PostgreSQLIntervalType.

Tuple result = (Tuple) entityManager.createNativeQuery("""
    SELECT
       b.published_on AS published_on,
       b.presale_period  AS presale_period
    FROM
       book b
    WHERE
       b.isbn = :isbn
    """, Tuple.class)
.setParameter("isbn", "978-9730228236")
.unwrap(NativeQuery.class)
.addScalar("published_on", YearMonthDateType.INSTANCE)
.addScalar("presale_period", PostgreSQLIntervalType.INSTANCE)
.getSingleResult();

assertEquals(
    YearMonth.of(2016, 10),
    result.get("published_on")
);

assertEquals(
    Duration.between(
        LocalDate.of(2015, Month.NOVEMBER, 2).atStartOfDay(),
        LocalDate.of(2016, Month.AUGUST, 25).atStartOfDay()
    ),
    result.get("presale_period")
);

The addScalar method of the Hibernate NativeQuery interface takes a org.hibernate.type.Type Object reference, while the YearMonthDateType and PostgreSQLIntervalType implement the UserType interface.

Prior to the 2.6 release of the Hypersistence Utils, it was not possible to use an ImmutableType, which extends the UserType interface, in the addScalar method calls. However, since version 2.6, the ImmutableType abstract class implements both UserType and org.hibernate.type.Type, so passing an ImmutableType (which is the base class of both YearMonthDateType and PostgreSQLIntervalType) to the addScalar method is no longer an issue.

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The Hypersistence Utils project has grown to accommodate a great variety of Hibernate types that are not supported natively. For instance, you can now use JSON, ARRAY, HStore, Range, Inet, YearMonth, nullable Character, and PostgreSQL-specific Enum types.

While you could also implement all these types yourself, it’s much more convenient to define the Hypersistence Utils dependency in your project pom.xml Maven configuration file and focus on the application business logic instead of writing Hibernate-specific types.

Insert details about how the information is going to be processed

DOWNLOAD NOW