Mapping PostgreSQL Interval to Java Duration with Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!
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 hibernate-types project.
Another very useful feature introduced by the hibernate-types 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 as follows:
@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;
}
}
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 define multiple @TypeDef annotations. Both are for Object types introduced by Java 8.
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 hibernate-types 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 types 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 hibernate-types, 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.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
The hibernate-types 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 hibernate-types dependency in your project pom.xml Maven configuration file and focus on the application business logic instead of writing Hibernate-specific types.
