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

When building enterprise applications, querying records that fall between two dates is a fundamental task. Whether you're generating monthly reports, filtering recent activity, or analyzing trends, Hibernate offers multiple approaches to handle temporal queries efficiently. In this guide, we'll explore three powerful methods: Hibernate Query Language (HQL), the Criteria API, and native SQL. Each has its strengths, and understanding them will help you choose the best tool for your specific needs. We'll use an Order entity with a creationDate field of type LocalDateTime (modern Hibernate supports Java 8 time types natively) to demonstrate real-world examples. Let's dive into the details.

1. What is the most straightforward way to query records between two dates using HQL?

The most intuitive method in HQL is the BETWEEN keyword. It allows you to specify a range with a clean syntax:

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();

This operator is inclusive on both ends, meaning records exactly equal to either boundary are included. However, a common pitfall arises when working with LocalDateTime. If you intend to capture an entire day (e.g., January 31st) but set the end date as 2024-01-31 00:00:00, the query excludes all events after midnight because they are technically greater than the bound. To avoid this, many developers switch to comparison operators for half-open intervals.

2. How can you avoid the logical pitfalls of BETWEEN when querying full days?

The safest pattern for calendar boundaries is the half-open interval: use >= for the start and < for the end. This way, you avoid manual time calculations. For example, to get all orders from January 2024, use February 1st as the exclusive upper bound:

String hql = "FROM Order o WHERE o.creationDate >= :startDate AND o.creationDate < :endDate";
// Set startDate = 2024-01-01 00:00:00, endDate = 2024-02-01 00:00:00

This eliminates the need to compute the last millisecond of the day. The same logic applies when using the Criteria API or native SQL. This approach is robust, database-independent, and easy to maintain.

3. How do you perform date range queries using the Criteria API in Hibernate?

The Criteria API provides a programmatic, type-safe way to build queries. For date ranges, you can use cb.between() or comparison methods. Here's an example using cb.greaterThanOrEqualTo() and cb.lessThan() for a half-open interval:

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Order> cr = cb.createQuery(Order.class);
Root<Order> root = cr.from(Order.class);
cr.select(root).where(
    cb.greaterThanOrEqualTo(root.get("creationDate"), startDate),
    cb.lessThan(root.get("creationDate"), endDate)
);
List<Order> orders = session.createQuery(cr).getResultList();

This approach is excellent for dynamic queries where conditions may change at runtime. It also benefits from compile-time checking of attribute names, reducing errors.

4. When should you use native SQL instead of HQL or Criteria API for date range queries?

Native SQL is best when you need database-specific date functions, need to optimize performance with raw SQL, or when your query is too complex for HQL. For example, if you want to use PostgreSQL's DATE_TRUNC to group by month, native SQL gives you full control. However, it ties your code to a specific database and bypasses Hibernate's caching and portability. Use it sparingly and typically for reporting or legacy systems. Example:

String sql = "SELECT * FROM orders WHERE creation_date >= ? AND creation_date < ?";
List<Object[]> results = session.createNativeQuery(sql)
  .setParameter(1, startDate)
  .setParameter(2, endDate)
  .getResultList();

You can also map results to entities with addEntity(Order.class).

5. How does Hibernate handle legacy java.util.Date fields for date range queries?

In older versions of Hibernate or when using java.util.Date, you must annotate the field with @Temporal to specify the precision. For example:

@Temporal(TemporalType.TIMESTAMP)
private Date legacyCreationDate;

When querying with HQL or Criteria API, the same patterns apply, but be aware that java.util.Date includes both date and time. The BETWEEN operator behaves identically, so the half-open interval recommendation still holds. For best results in modern applications, migrate to Java 8 time types like LocalDateTime or Instant to avoid @Temporal and gain better timezone handling.

6. Which method is recommended for most date range queries in Hibernate?

For most scenarios, HQL with comparison operators (>= and <) offers readability, portability, and safety from the inclusive-boundary trap. The Criteria API is a strong alternative when building dynamic filters programmatically. Native SQL should be reserved for database-specific functionality or extreme performance needs. As a best practice, always prefer half-open intervals to avoid off-by-one errors when querying calendar boundaries. This approach works consistently across HQL, Criteria, and native SQL, making it a universal pattern for temporal queries.