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

Overview

Querying records that fall between two dates is a fundamental operation in nearly every enterprise application. Whether you need to generate monthly financial reports, filter logs from the last 24 hours, or retrieve orders placed during a specific promotion, Hibernate offers multiple flexible approaches to handle these temporal queries efficiently.

This guide walks you through three primary methods—Hibernate Query Language (HQL), the Criteria API, and Native SQL—with complete code examples, best practices, and common pitfalls to avoid.

Prerequisites

Before diving in, ensure you have the following:

• Java 8 or later (to leverage the java.time API)
• Hibernate 5.x or 6.x (preferred for native LocalDateTime support)
• A relational database (e.g., PostgreSQL, MySQL, H2)
• Basic familiarity with JPA/Hibernate and Maven/Gradle build tools

If you’re using older java.util.Date, the examples will note the necessary adjustments.

Step 1: Define the Entity

We’ll use an Order entity as our working example. In modern Hibernate, java.time.LocalDateTime is supported natively—no extra annotations needed.

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String trackingNumber;

    private LocalDateTime creationDate;

    // Constructors, getters, setters
}

If you’re stuck with java.util.Date, add the @Temporal annotation:

@Temporal(TemporalType.TIMESTAMP)
private Date legacyCreationDate;

Step 2: Query with HQL

Using the BETWEEN Keyword

The BETWEEN operator is the most readable way to express an inclusive date range:

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

Inclusive behavior: Both boundaries are included. If startDate = 2024-01-01 00:00:00 and endDate = 2024-01-31 00:00:00, orders placed exactly at midnight on Jan 31 are included, but any order after that second is excluded. This often leads to missing data when you intend to capture an entire day.

Using Comparison Operators (Half‑Open Interval)

A safer pattern for calendar days is to use a half‑open interval: >= on the start and < on the end. For January 2024, set endDate to 2024-02-01 00:00:00, not the last millisecond of Jan 31.

String hql = "FROM Order o WHERE o.creationDate >= :startDate AND o.creationDate < :endDate";
List<Order> orders = session.createQuery(hql, Order.class)
  .setParameter("startDate", startDate)   // 2024-01-01 00:00:00
  .setParameter("endDate", endDate)       // 2024-02-01 00:00:00
  .getResultList();

This guarantees you get every order from January without worrying about time component precision.

Step 3: Query with the Criteria API

The Criteria API is ideal for dynamic queries where parameters may be optional.

Using between()

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

Predicate datePredicate = cb.between(root.get("creationDate"), startDate, endDate);
cr.select(root).where(datePredicate);

List<Order> orders = session.createQuery(cr).getResultList();

Using Comparison Predicates

Predicate datePredicate = cb.and(
    cb.greaterThanOrEqualTo(root.get("creationDate"), startDate),
    cb.lessThan(root.get("creationDate"), endDate)
);

Both approaches follow the same inclusivity rules as HQL. The Criteria version makes it easy to combine with other filters dynamically.

Step 4: Query with Native SQL

When you need database‑specific features (like PostgreSQL’s DATERANGE operator) or have complex queries, native SQL is your escape hatch.

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

Remember: native SQL bypasses the Hibernate cache and may be less portable across databases. For half‑open intervals, adjust the WHERE clause accordingly (e.g., creation_date >= ? AND creation_date < ?).

Common Mistakes

• Inclusive vs. exclusive confusion: Using BETWEEN to match whole days without adjusting the time component leads to missing records. Always prefer the half‑open pattern for intervals.
• Timezone mishandling: Storing dates in local time without a time zone can break queries when the application server and database are in different regions. Store UTC timestamps or use ZonedDateTime.
• Legacy java.util.Date pitfalls: Forgetting the @Temporal annotation may cause unexpected behavior. Modernize to java.time when possible.
• Performance degradation: Date‑range queries without an index on the date column will be slow. Add a database index on the date column, and avoid functions like DATE(column) that prevent index usage.

Summary

Date range queries are a staple of data‑driven applications, and Hibernate provides robust tools to handle them: HQL for readability, the Criteria API for dynamic construction, and native SQL for database‑specific power. The key takeaway is to use half‑open intervals (>= and <) instead of BETWEEN when you need full days, always account for timezones, and ensure your date columns are indexed. By mastering these techniques, you’ll write accurate, high‑performing temporal queries every time.