How to map a PostgreSQL Range column type with JPA and Hibernate

Last modified:

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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 of integer values
int8range – Stores a range of bigint (e.g. java.util.Long) values
numrange – Stores a range of numeric (e.g. java.util.BigDecimal) values
daterange – Stores a range of timestamp (e.g. java.time.LocalDate) values
tsrange – Stores a range of timestamp (e.g. java.time.LocalDateTime) values
tstzrange – Stores a range of timestamp 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 the ImmutableType offered by the hibernate-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-52</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 matching hibernate-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?

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!

Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September

High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino

High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet

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.