Mapping PostgreSQL Interval to Java Duration with Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
