Mastering Date Range Queries in Hibernate: HQL, Criteria, and SQL

Introduction

Filtering data within a specific time window is a cornerstone of enterprise applications. Whether you’re generating monthly invoices, auditing user activity over the past week, or analyzing logs from the last hour, you need a reliable way to query records between two dates. Hibernate, as the leading JPA implementation, offers multiple approaches to handle these temporal queries: HQL (Hibernate Query Language), the Criteria API, and native SQL. In this article, we’ll explore each method, discuss their nuances, and provide clear examples—all while keeping your code portable and efficient.

Setting Up the Entity

To demonstrate date-range queries, we’ll use a simple Order entity. Modern Hibernate (5.x and later) natively supports Java 8 java.time types, such as LocalDateTime. This simplifies mapping and querying drastically:

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String trackingNumber;
    private LocalDateTime creationDate;
    // getters and setters
}

If you’re still working with legacy java.util.Date, you can use the @Temporal annotation to specify the precision—date, time, or timestamp:

@Temporal(TemporalType.TIMESTAMP)
private Date legacyCreationDate;

All examples below assume the Order entity with a creationDate field of type LocalDateTime.

Querying with Hibernate Query Language (HQL)

Using the BETWEEN Operator

The BETWEEN operator is the most intuitive way to express a date range in HQL. It is inclusive on both ends, so records with timestamps exactly equal to the start or end values are included:

String hql = "FROM Order o WHERE o.creationDate BETWEEN :startDate AND :endDate";
List<Order> orders = session.createQuery(hql, Order.class)
  .setParameter("startDate", startDate)
  .setParameter("endDate", endDate)
  .getResultList();

While this syntax is clean, it hides a common pitfall when working with LocalDateTime. Suppose you want all orders for January 31st. You set startDate to 2024-01-31T00:00:00 and endDate to 2024-01-31T00:00:00. The query will only return orders placed exactly at midnight—because BETWEEN is inclusive, a record with a timestamp of 10:30 AM on January 31st is greater than the end value and will be excluded. To capture the entire day, you would have to manually set the end time to 23:59:59.999, which is fragile.

Using Comparison Operators for Half-Open Intervals

A more robust pattern is to use a half-open interval: inclusive on the lower bound, exclusive on the upper bound. This avoids the midnight boundary trap entirely. For example, to get all orders from January 2024, use >= for the start of January and < for the start of February:

String hql = "FROM Order o WHERE o.creationDate >= :startDate AND o.creationDate < :endDate";
LocalDateTime start = LocalDateTime.of(2024, 1, 1, 0, 0);
LocalDateTime end = LocalDateTime.of(2024, 2, 1, 0, 0);
List<Order> orders = session.createQuery(hql, Order.class)
  .setParameter("startDate", start)
  .setParameter("endDate", end)
  .getResultList();

This pattern naturally captures the entire month without worrying about the last millisecond of the day. It works equally well for weeks, years, or any arbitrary period.

Using the Criteria API

The Criteria API provides a type-safe, programmatic way to build queries. It’s especially useful when the query structure depends on dynamic conditions. To perform a date-range query, you can use javax.persistence.criteria.Predicate with between or with comparison operators.

Here’s an example using the same half-open interval approach:

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Order> cr = cb.createQuery(Order.class);
Root<Order> root = cr.from(Order.class);

Predicate datePredicate = cb.and(
    cb.greaterThanOrEqualTo(root.get("creationDate"), start),
    cb.lessThan(root.get("creationDate"), end)
);
cr.select(root).where(datePredicate);
List<Order> orders = session.createQuery(cr).getResultList();

If you prefer the inclusive BETWEEN for cases where you control the exact timestamps, use cb.between(root.get("creationDate"), start, end). Just remember the midnight boundary limitation.

Using Native SQL

Sometimes you need database-specific date functions or performance optimizations not available in HQL. In such cases, native SQL is the way to go. Hibernate lets you execute raw SQL and map results to entities using addEntity().

String sql = "SELECT * FROM orders WHERE creation_date >= :start AND creation_date < :end";
List<Order> orders = session.createNativeQuery(sql, Order.class)
  .setParameter("start", start)
  .setParameter("end", end)
  .getResultList();

Note that the column name in the database (creation_date) may differ from the Java field name (creationDate), so use the database column name in the SQL query. Native SQL gives you full control over the query, but it ties your code to a specific database dialect.

Conclusion

Querying records between two dates in Hibernate is straightforward, but choosing the right approach depends on your needs. For most cases, HQL with a half-open interval (>= and <) offers the best balance of readability and correctness—it avoids the midnight boundary issue inherent in BETWEEN. The Criteria API provides type safety and dynamic query construction, while native SQL gives you maximum flexibility at the cost of portability. By understanding these options, you can write date-range queries that are both accurate and maintainable. For further reading, check out our guide on HQL best practices or explore the Criteria API for complex filters.