Using PostgreSQL range types

Hibernate ORM
1

I´m wondering how best PostgreSQL range types could be used from Hibernate.

  • As a first step I’m working on a pull request for pgjdbc to support range types.
  • A second step would probably to map to a domain type, I don’t think you want to have database driver types in your entities. Are there any good candidates?
  • A third step would be to support range type operators and functions in HQL. Can this be done? Are there examples or documentation on how to do this?

I am aware of vladmihalcea/hibernate-types.

2

As a first step, I think we need to have support for this feature in pgjdbc.

Afterward, if these range types are not supported by the vast majority of RDBMS, I doubt these types could be added in hibernate-core. That’s why I added support for JSON and ARRAY in hibernate-types.

So, if you want to use PostgreSQL range types with Hibernate, just use the hibernate-types project and map your ranges like this:

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

For more details about using the PostgreSQLRangeType custom type, check out this article.

A third step would be to support range type operators and functions in HQL. Can this be done? Are there examples or documentation on how to do this?

That might be easier to implement in 6.0. In 5.x, it’s not that easy to extend HQL to support new operators. If those operators are not widely supported, it’s probably better to left them out.

If you want to use Range types with Hibernate