Hibernate HQL BETWEEN Expression

In this post I will show you how to use the Hibernate HQL between expression to write and execute some simple HQL queries.

HQL is the Hibernate Query Language that allows you to query your Hibernate data layer using similar syntax to the relational database query language SQL. SQL offers a number of operators and expressions that are also found in HQL. Luckily, between is an operator that is found in both SQL and HQL. Let’s see some examples of when and how you can use HQL between and list a few considerations.

Just like the SQL between expression the correct syntax for HQL between is:

...where value between rangeLimit1 and rangeLimit2...

As you can see you use the between expression in a where clause to find those values that lie inside a range (indicated by it’s limits separated by the and keyword).

In case of HQL, value, rangeLimit1 and rangeLimit2 can be any of the following: a primitive value (including strings and chars), a primitive field of an object, an object field that can be resolved to a primitive (eg. Integer) or a date (Date object). So you can query for values that fall between date field values or other types.

A few remarks related to the HQL between expression

Hibernate between operator syntax

rangeLimit1 is always the lower limit and rangeLimit2 is always the upper limit and the limit check is always inclusive, meaning that the following checks are equivalent

...where value between rangeLimit1 and rangeLimit2...

...where value >= rangeLimit1 and value <= rangeLimit2...

Using HQL between to compare dates

In case you are using the HQL between expression for comparing date values building the query will look something like

"from Event e where e.eventDate between :start and :end"

for further details on how to bind the parameters you can read HQL Date and Datetime Quick Tip

Using NOT with HQL between operator

Just like in SQL you can use the NOT keyword in front of the HQL between expression to find the values outside the range indicated; for example

"from Employees e where e.salary not between :low and :high"

will return all employees for which the salary is outside low,high range.

Note: applying the NOT keyword in front of the HQL between expression will transform the limit check to an exclusive check, meaning that the following are equivalent

...where value not between rangeLimit1 and rangeLimit2...

...where value < rangeLimit1 or value > rangeLimit2...

There are number of other HQL expressions that you can use and you can find a more extensive list together with some examples in the HQL reference.