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

By ✦ min read

Introduction

Querying data within a specific time interval is a fundamental operation in many enterprise applications. Whether you need to generate monthly financial summaries, retrieve logs for the past 24 hours, or filter records for a custom date range, Hibernate offers multiple approaches to handle temporal queries efficiently. In this guide, we'll explore three primary methods for querying records between two dates: Hibernate Query Language (HQL), the Criteria API, and Native SQL. Each technique has its strengths, and understanding them will help you choose the best fit for your use case.

Mastering Date Range Queries in Hibernate: HQL, Criteria API, and Native SQL — Source: www.baeldung.com

Setting Up the Entity

To demonstrate these operations, we'll use a simple Order entity mapped to an orders table. Modern Hibernate versions (5.x and later) provide first-class support for Java 8 date/time types like LocalDateTime, so no special annotations are required. Here's the entity definition:

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

@Temporal(TemporalType.TIMESTAMP)
private Date legacyCreationDate;

This setup provides a clean base for our date-range queries.

Using Hibernate Query Language (HQL)

HQL is the most commonly used approach because it abstracts away database nuances and lets you work directly with your entity model. It's both readable and portable across databases.

Using the BETWEEN Keyword

The BETWEEN operator is the simplest way to query records within an inclusive range. For example, to find orders created between two specific dates:

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 concise, BETWEEN can be tricky when working with LocalDateTime. Because BETWEEN is inclusive on both ends, if you want all orders for January 31st and you set endDate to 2024-01-31T00:00:00, orders placed later that day will be excluded. To capture an entire day, you'd need to set endDate to the last millisecond (2024-01-31T23:59:59.999). This manual calculation is fragile and error-prone.

Using Comparison Operators for Half-Open Intervals

A more robust pattern is to use a half-open interval: inclusive on the lower bound and exclusive on the upper bound. This avoids the need to calculate the final moment of a day. For example, to get all orders from January 2024, use February 1st as the exclusive end:

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 works consistently across time zones and avoids boundary issues.

Using the Criteria API

The Criteria API provides a type-safe, programmatic way to build queries. It's especially useful when parts of the query are conditional or dynamic. Here's how to implement a date range query with criteria:

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

Predicate startPred = cb.greaterThanOrEqualTo(root.get("creationDate"), startDate);
Predicate endPred = cb.lessThan(root.get("creationDate"), endDate);
cr.where(cb.and(startPred, endPred));

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

Notice we again use the half-open pattern (>= and <) to avoid the BETWEEN pitfalls. The Criteria API allows for clean composition of predicates, making it ideal for complex filters.

Using Native SQL

Sometimes you need database-specific functions (like date truncation or time zone conversion) that HQL or Criteria don't expose. In those cases, Native SQL is the answer. You can write raw SQL queries and still map results to entities. For example:

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

Native SQL gives you full control, but it ties your query to a specific database dialect. Use it sparingly when portability isn't a concern.

Conclusion

Querying records between two dates in Hibernate is straightforward once you understand the available tools. HQL offers simplicity and portability, while the Criteria API adds type safety and dynamic construction. For database-specific needs, Native SQL provides flexibility. Regardless of the method, always prefer a half-open interval (>= start, < end) over BETWEEN to avoid boundary bugs. By applying these patterns, you'll write robust date-range queries that work reliably across all your Hibernate projects.

Tags: