How to map a PostgreSQL Range column type with JPA and 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 range
column types with JPA and Hibernate.
Luckily, you don’t have to implement a custom Hibernate type for the PostgreSQL range
column type since the hibernate-types
project already provides support for it.
How to inherit properties from a base class entity using @MappedSuperclass with JPA and Hibernate @vlad_mihalcea https://t.co/1mM0bcwNFa pic.twitter.com/lZ9csG8hJS
— Java (@java) January 25, 2019
Range column types
PostgreSQL supports multiple range
types:
int4range
– Stores a range ofinteger
valuesint8range
– Stores a range ofbigint
(e.g.java.util.Long
) valuesnumrange
– Stores a range ofnumeric
(e.g.java.util.BigDecimal
) valuesdaterange
– Stores a range oftimestamp
(e.g.java.time.LocalDate
) valuestsrange
– Stores a range oftimestamp
(e.g.java.time.LocalDateTime
) valueststzrange
– Stores a range oftimestamp with timezone
(e.g.java.time.ZonedDateTime
) values
To represent ranges of values which can have open or closed lower and upper boundaries, we can use the Range
class coming with the hibernate-types
project.
Range Hibernate Type
When mapping a Hibernate custom Type you have two options:
- you can implement the
UserType
interface - you can extend the
AbstractSingleColumnStandardBasicType
Using the former strategy, the PostgreSQLRangeType
looks as follows:
public class PostgreSQLRangeType extends ImmutableType<Range> { public PostgreSQLRangeType() { super(Range.class); } @Override public int[] sqlTypes() { return new int[]{Types.OTHER}; } @Override protected Range get( ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException { Object pgObject = rs.getObject(names[0]); String type = ReflectionUtils.invokeGetter( pgObject, "type" ); String value = ReflectionUtils.invokeGetter( pgObject, "value" ); switch (type) { case "int4range": return Range.integerRange(value); case "int8range": return Range.longRange(value); case "numrange": return Range.bigDecimalRange(value); case "tsrange": return Range.localDateTimeRange(value); case "tstzrange": return Range.zonedDateTimeRange(value); case "daterange": return Range.localDateRange(value); default: throw new IllegalStateException( "The range type [" + type + "] is not supported!" ); } } @Override protected void set( PreparedStatement st, Range range, int index, SharedSessionContractImplementor session) throws SQLException { if (range == null) { st.setNull(index, Types.OTHER); } else { Object holder = ReflectionUtils.newInstance( "org.postgresql.util.PGobject" ); ReflectionUtils.invokeSetter( holder, "type", determineRangeType(range) ); ReflectionUtils.invokeSetter( holder, "value", range.asString() ); st.setObject(index, holder); } } private static String determineRangeType(Range<?> range) { Class<?> clazz = range.getClazz(); if (clazz.equals(Integer.class)) { return "int4range"; } else if (clazz.equals(Long.class)) { return "int8range"; } else if (clazz.equals(BigDecimal.class)) { return "numrange"; } else if (clazz.equals(LocalDateTime.class)) { return "tsrange"; } else if (clazz.equals(ZonedDateTime.class)) { return "tstzrange"; } else if (clazz.equals(LocalDate.class)) { return "daterange"; } throw new IllegalStateException( "The class [" + clazz.getName() + "] is not supported!" ); } }
If you want to implement a custom Hibernate type by implementing the
UserType
interface, it’s much easier if you just extend theImmutableType
offered by thehibernate-types
project.For more details, check out this article.
Maven dependency
As already mentioned, you don’t need to create the Range
or the PostgreSQLRangeType
classes. You can get them via the hibernate-types
Maven dependency:
<dependency> <groupId>com.vladmihalcea</groupId> <artifactId>hibernate-types-55</artifactId> <version>${hibernate-types.version}</version> </dependency>
If you’re using an older version of Hibernate, go to the
hibernate-types
GitHub repository and find the matchinghibernate-types
dependency for your current Hibernate version.
Domain Model
Let’s assume we are developing a bookstore application, and the Book
entities look as follows:
@Entity(name = "Book") @Table(name = "book") @TypeDef( typeClass = PostgreSQLRangeType.class, defaultForType = Range.class ) public class Book { @Id @GeneratedValue private Long id; @NaturalId private String isbn; private String title; @Column( name = "price_cent_range", columnDefinition = "numrange" ) private Range<BigDecimal> priceRange; @Column( name = "discount_date_range", columnDefinition = "daterange" ) private Range<LocalDate> discountDateRange; //Getters and setters omitted for brevity }
Notice the use of the @TypeDef
annotation which tells Hibernate to use the PostgreSQLRangeType
Hibernate Type for handling the Range
entity properties.
The isbn
property is marked with the @NaturalId
Hibernate-specific annotation which allows us to retrieve the Book
entity via its natural identifier. For more details about using natural identifiers, check out this article.
used for the which is very useful for mapping business keys.
Testing time
Now, when persisting the following two Book
entities:
Book book = new Book(); book.setIsbn("978-9730228236"); book.setTitle("High-Performance Java Persistence"); book.setPriceRange( Range.closed( BigDecimal.valueOf(39.95d), BigDecimal.valueOf(45.95d) ) ); book.setDiscountDateRange( Range.closedOpen( LocalDate.of(2019, 11, 29), LocalDate.of(2019, 12, 3) ) ); entityManager.persist(book);
Hibernate generates the following SQL INSERT statements:
INSERT INTO book ( discount_date_range, isbn, price_cent_range, title, id ) VALUES ( '[2019-11-29,2019-12-03)', '978-9730228236', '[39.95,45.95]', 'High-Performance Java Persistence', 1 )
When fetching the previously persisted Book
entity, we can see that the range
properties are properly retrieved from the underlying database columns:
Book book = entityManager .unwrap(Session.class) .bySimpleNaturalId(Book.class) .load("978-9730228236"); assertEquals( BigDecimal.valueOf(39.95d), book.getPriceRange().lower() ); assertEquals( BigDecimal.valueOf(45.95d), book.getPriceRange().upper() ); assertEquals( LocalDate.of(2019, 11, 29), book.getDiscountDateRange().lower() ); assertEquals( LocalDate.of(2019, 12, 3), book.getDiscountDateRange().upper() );
What’s nice about using the range
column types is that we can use range-specific operators like the @>
one, which verifies if the provided value is contained in the range interval:
List<Book> discountedBooks = entityManager .createNativeQuery( "SELECT * " + "FROM book b " + "WHERE " + " b.discount_date_range @> CAST(:today AS date) = true ", Book.class) .setParameter( "today", LocalDate.of(2019, 12, 1) ) .getResultList(); assertTrue( discountedBooks.stream().anyMatch( book -> book.getTitle().equals("High-Performance Java Persistence") ) );
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Mapping non-standard database column types is quite straightforward with Hibernate. However, with the help of the hibernate-types project, you don’t even have to write all these types.
Just add the Maven dependency to your project pom.xml
configuration file, and provide the @TypeDef
annotation to the entity in question, and start mapping JSON, ARRAY, PostgreSQL Enum or Inet addresses.
