Mastering Date Range Queries in Hibernate: A Q&A Guide

This guide answers common questions about querying records between two dates using Hibernate. Whether you're building financial reports or filtering recent logs, Hibernate offers several approaches: HQL, Criteria API, and Native SQL. We'll focus on the most common method—HQL—and address pitfalls like the midnight boundary issue when using BETWEEN. You'll learn best practices for half-open intervals with comparison operators to ensure accurate results.

1. What are the main approaches to query records between two dates in Hibernate?

Hibernate provides three primary ways to perform date-range queries:

• HQL (Hibernate Query Language): The most readable and portable approach. It works with your entity model and uses keywords like BETWEEN or comparison operators.
• Criteria API: A programmatic, type-safe way to build queries dynamically. It leverages javax.persistence.criteria classes and is ideal for complex criteria.
• Native SQL: When you need database-specific features or optimizations, you can write raw SQL queries passed through session.createNativeQuery().

Each method supports date parameters, but HQL is often the first choice due to its simplicity and database independence.

2. How do I set up an entity with date fields for date-range queries?

Define an entity like Order with a date field. In modern Hibernate (5+), Java 8 java.time types are supported natively:

@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 using legacy java.util.Date, you must add the @Temporal annotation to specify storage precision:

@Temporal(TemporalType.TIMESTAMP)
private Date legacyCreationDate;

Always use LocalDateTime (or LocalDate) for new projects to avoid temporal boilerplate.

3. How do I use HQL's BETWEEN operator for date ranges?

The BETWEEN keyword in HQL is inclusive on both ends. A typical query looks like:

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 is straightforward for many use cases, but beware of a subtle pitfall with LocalDateTime values that include time components.

4. What is the midnight boundary pitfall with BETWEEN and how can I avoid it?

When using BETWEEN with LocalDateTime, records are matched up to the exact millisecond of the end parameter. For example, if you want all orders on January 31, 2024, and you set endDate to 2024-01-31 00:00:00, then any order placed at 10:30 AM on that day is greater than the end value and excluded. The operator is inclusive only up to that exact boundary.

To capture a full day, you'd need to manually set the time to 23:59:59.999, which is fragile. A better approach is to use comparison operators to create a half-open interval (see next question).

5. How do I use comparison operators for a half-open interval in HQL?

The safest pattern for querying full calendar boundaries (e.g., all orders in January 2024) is to use >= for the start and < for the end, making the start inclusive and the end exclusive. For January 2024, you would set:

• startDate = 2024-01-01 00:00:00
• endDate = 2024-02-01 00:00:00

Then the HQL becomes:

String hql = "FROM Order o WHERE o.creationDate >= :startDate AND o.creationDate < :endDate";

This automatically captures all times on January 31st up to but not including midnight February 1st. No manual millisecond calculations are needed, making queries robust and maintainable.