Querying records between two dates is a common requirement in enterprise applications. Examples include retrieving orders placed within a specific period, fetching audit logs, generating reports, or filtering transactions by date range. Hibernate provides multiple ways to perform date-based queries, including Hibernate Query Language (HQL), the Criteria API, and Native SQL. In this article, we’ll create a simple example that retrieves employee records whose joining dates fall between two specified dates.
1. Overview
When working with databases, date range filtering is one of the most common querying requirements in business applications. Developers frequently need to retrieve records created within a specific period, generate monthly or yearly reports, analyze historical data, track user activity, monitor audit logs, or filter transactions based on a start and end date. Hibernate simplifies this process by automatically mapping Java date and time types such as LocalDate, LocalDateTime, and Instant to their corresponding database date and timestamp columns. Instead of writing complex JDBC code and manually handling type conversions, developers can leverage Hibernate’s rich querying capabilities to perform date-based searches using Hibernate Query Language (HQL), the Criteria API, or Native SQL. These approaches provide varying levels of abstraction, flexibility, and control, allowing applications to efficiently retrieve records that fall within a specified date range while maintaining clean, readable, and database-independent code.
2. Setup
2.1 Maven Dependencies
Before we start writing the Hibernate code, we need to add the required dependencies to our project. The following Maven dependencies provide Hibernate ORM, the H2 in-memory database, and the Jakarta Persistence API used throughout this example.
<dependencies>
<dependency>
<groupId>org.hibernate.orm</groupId>
<artifactId>hibernate-core</artifactId>
<version>stable__latest__jar__version</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>stable__latest__jar__version</version>
</dependency>
<dependency>
<groupId>jakarta.persistence</groupId>
<artifactId>jakarta.persistence-api</artifactId>
<version>stable__latest__jar__version</version>
</dependency>
</dependencies>
The Maven dependencies listed above provide all the libraries required to build and run the Hibernate application. The hibernate-core dependency is the primary Hibernate framework library that supplies the ORM (Object-Relational Mapping) functionality, session management, transaction handling, entity mapping, HQL support, Criteria API support, and database interaction capabilities used throughout the example. The h2 dependency provides the lightweight, in-memory H2 database that serves as the application’s database during development and testing, eliminating the need to install and configure an external database server. Since H2 can run entirely in memory, it allows the example to be executed quickly and independently. The jakarta.persistence-api dependency provides the Jakarta Persistence (JPA) annotations and interfaces used by Hibernate, including annotations such as @Entity, @Table, @Id, @GeneratedValue, and @Column. These annotations enable the mapping of Java classes and fields to database tables and columns. Together, these three dependencies provide the complete infrastructure needed to define entities, configure Hibernate, interact with the database, persist data, and execute HQL, Criteria API, and Native SQL queries within the application.
2.2 Hibernate Configuration
Before running the application, we need to configure Hibernate so it can connect to the database. In this example, we use the H2 in-memory database, which is lightweight, easy to set up, and ideal for development and testing purposes. The configuration file defines the database connection details, Hibernate dialect, schema generation strategy, and other runtime settings.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"https://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">
org.h2.Driver
</property>
<property name="hibernate.connection.url">
jdbc:h2:mem:testdb
</property>
<property name="hibernate.connection.username">
sa
</property>
<property name="hibernate.connection.password">
</property>
<property name="hibernate.dialect">
org.hibernate.dialect.H2Dialect
</property>
<property name="hibernate.hbm2ddl.auto">
create-drop
</property>
<property name="hibernate.show_sql">
true
</property>
<property name="hibernate.format_sql">
true
</property>
</session-factory>
</hibernate-configuration>
The hibernate.connection.driver_class property specifies the JDBC driver used to communicate with the database. The hibernate.connection.url property defines the database connection URL and points to an H2 in-memory database named testdb. Since H2 runs entirely in memory, no external database installation is required. The username is set to sa, which is the default H2 administrator account, while the password is left empty. The hibernate.dialect property tells Hibernate to generate SQL optimized for H2. The hibernate.hbm2ddl.auto property is configured as create-drop, causing Hibernate to automatically create the database schema when the application starts and drop it when the application terminates. The hibernate.show_sql and hibernate.format_sql properties instruct Hibernate to display formatted SQL statements in the console, making it easier to understand the SQL generated by HQL, Criteria API, and Native SQL queries.
2.3 Complete Example
The following example demonstrates how to query records between two dates using Hibernate. It inserts sample employee records into an H2 database and then retrieves matching employees using HQL, the Criteria API, and Native SQL.
// QueryRecordsBetweenDatesExample.java
package com.example.hibernate;
import jakarta.persistence.*;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import java.time.LocalDate;
import java.util.List;
// Maps this class to the employees table
@Entity
@Table(name = "employees")
class Employee {
// Primary key
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// Employee name
private String name;
// Maps the joiningDate field to the joining_date column
@Column(name = "joining_date")
private LocalDate joiningDate;
// Default constructor required by Hibernate
public Employee() {
}
// Convenience constructor
public Employee(String name, LocalDate joiningDate) {
this.name = name;
this.joiningDate = joiningDate;
}
public Long getId() {
return id;
}
public String getName() {
return name;
}
public LocalDate getJoiningDate() {
return joiningDate;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", name='" + name + '\'' +
", joiningDate=" + joiningDate +
'}';
}
}
public class QueryRecordsBetweenDatesExample {
public static void main(String[] args) {
// Build the SessionFactory using hibernate.cfg.xml
SessionFactory sessionFactory =
new Configuration()
.configure()
.addAnnotatedClass(Employee.class)
.buildSessionFactory();
try {
// Insert sample records into the database
insertSampleData(sessionFactory);
// Define the date range used by all queries
LocalDate startDate =
LocalDate.of(2023, 1, 1);
LocalDate endDate =
LocalDate.of(2023, 12, 31);
try (Session session = sessionFactory.openSession()) {
// Execute the HQL query
System.out.println("=== HQL Results ===");
List<Employee> hqlResults =
findEmployeesUsingHQL(
session,
startDate,
endDate);
hqlResults.forEach(System.out::println);
System.out.println();
// Execute the Criteria API query
System.out.println("=== Criteria API Results ===");
List<Employee> criteriaResults =
findEmployeesUsingCriteria(
session,
startDate,
endDate);
criteriaResults.forEach(System.out::println);
System.out.println();
// Execute the Native SQL query
System.out.println("=== Native SQL Results ===");
List<Employee> nativeResults =
findEmployeesUsingNativeSql(
session,
startDate,
endDate);
nativeResults.forEach(System.out::println);
}
} finally {
// Release Hibernate resources
sessionFactory.close();
}
}
/**
* Inserts sample employee records into the database.
*/
private static void insertSampleData(
SessionFactory sessionFactory) {
try (Session session =
sessionFactory.openSession()) {
Transaction tx =
session.beginTransaction();
session.persist(
new Employee(
"John",
LocalDate.of(2023, 1, 10)));
session.persist(
new Employee(
"Alice",
LocalDate.of(2023, 4, 15)));
session.persist(
new Employee(
"David",
LocalDate.of(2023, 8, 20)));
session.persist(
new Employee(
"Emma",
LocalDate.of(2024, 2, 5)));
tx.commit();
}
}
/**
* Queries employees between two dates using HQL.
*/
private static List<Employee> findEmployeesUsingHQL(
Session session,
LocalDate startDate,
LocalDate endDate) {
// HQL works with entity names and properties
String hql = """
FROM Employee e
WHERE e.joiningDate
BETWEEN :startDate AND :endDate
""";
return session.createQuery(
hql,
Employee.class)
.setParameter(
"startDate",
startDate)
.setParameter(
"endDate",
endDate)
.getResultList();
}
/**
* Queries employees between two dates using the Criteria API.
*/
private static List<Employee> findEmployeesUsingCriteria(
Session session,
LocalDate startDate,
LocalDate endDate) {
// Factory for creating criteria queries
CriteriaBuilder cb =
session.getCriteriaBuilder();
// Create a query returning Employee entities
CriteriaQuery<Employee> query =
cb.createQuery(Employee.class);
// Define the root entity
Root<Employee> root =
query.from(Employee.class);
// Add a BETWEEN condition on joiningDate
query.select(root)
.where(
cb.between(
root.get("joiningDate"),
startDate,
endDate));
return session.createQuery(query)
.getResultList();
}
/**
* Queries employees between two dates using Native SQL.
*/
private static List<Employee> findEmployeesUsingNativeSql(
Session session,
LocalDate startDate,
LocalDate endDate) {
// Native SQL works directly with table and column names
String sql = """
SELECT *
FROM employees
WHERE joining_date
BETWEEN :startDate
AND :endDate
""";
return session.createNativeQuery(
sql,
Employee.class)
.setParameter(
"startDate",
startDate)
.setParameter(
"endDate",
endDate)
.getResultList();
}
}
2.3.1 Code Explanation
This example demonstrates how to query records between two dates using Hibernate through three different approaches: Hibernate Query Language (HQL), the Criteria API, and Native SQL. The Employee class is a JPA entity mapped to the employees database table using the @Entity and @Table annotations. The id field serves as the primary key and is automatically generated by the database using the @GeneratedValue annotation, while the name field stores the employee name and the joiningDate field is mapped to the joining_date database column using @Column. The entity contains a default constructor required by Hibernate, a parameterized constructor for convenient object creation, getter methods, and an overridden toString() method that provides a readable representation of employee objects.
The QueryRecordsBetweenDatesExample class acts as the application’s entry point. Inside the main() method, a SessionFactory is created using Hibernate’s Configuration class, which loads settings from the hibernate.cfg.xml file and registers the Employee entity. The application then invokes the insertSampleData() method to populate the database with sample employee records. This method opens a Hibernate session, starts a transaction, persists four employee objects into the database, and commits the transaction to make the changes permanent. After inserting the sample data, the application defines a date range from January 1, 2023, to December 31, 2023, which is used as the filter criterion for all subsequent queries.
The first query is executed through the findEmployeesUsingHQL() method, which uses Hibernate Query Language to retrieve employees whose joiningDate falls between the supplied start and end dates. HQL is object-oriented and operates on entity names and properties rather than database tables and columns. The query uses named parameters, which are bound through the setParameter() method before the results are fetched as a list of Employee objects.
The second approach is implemented in the findEmployeesUsingCriteria() method using Hibernate’s Criteria API. This method obtains a CriteriaBuilder, creates a CriteriaQuery targeting the Employee entity, defines a Root object representing the entity being queried, and dynamically constructs a BETWEEN condition on the joiningDate field. The Criteria API provides type-safe and programmatic query construction, making it particularly useful when query conditions need to be built dynamically at runtime.
The third approach is implemented in the findEmployeesUsingNativeSql() method, which executes a raw SQL statement directly against the database. Unlike HQL and the Criteria API, Native SQL uses the actual table name (employees) and column name (joining_date). The query is executed using createNativeQuery(), and Hibernate automatically maps the resulting rows back into Employee entities.
Finally, the results from all three query mechanisms are printed to the console using forEach(System.out::println), which internally invokes the entity’s toString() method. Since the date range covers only the year 2023, the employees John, Alice, and David are returned by each query, while Emma is excluded because her joining date falls in 2024. This example highlights how Hibernate supports multiple querying techniques while producing the same result set, allowing developers to choose the most appropriate approach based on readability, flexibility, portability, and database-specific requirements.
2.3.2 Code Output
After executing the application, Hibernate inserts the sample records and runs all three query implementations against the specified date range. The following output shows the employees returned by each query approach.
=== HQL Results ===
Employee{id=1, name='John', joiningDate=2023-01-10}
Employee{id=2, name='Alice', joiningDate=2023-04-15}
Employee{id=3, name='David', joiningDate=2023-08-20}
=== Criteria API Results ===
Employee{id=1, name='John', joiningDate=2023-01-10}
Employee{id=2, name='Alice', joiningDate=2023-04-15}
Employee{id=3, name='David', joiningDate=2023-08-20}
=== Native SQL Results ===
Employee{id=1, name='John', joiningDate=2023-01-10}
Employee{id=2, name='Alice', joiningDate=2023-04-15}
Employee{id=3, name='David', joiningDate=2023-08-20}
The output demonstrates that all three querying approaches—Hibernate Query Language (HQL), the Criteria API, and Native SQL—return the same set of employee records when filtering by the specified date range of January 1, 2023 through December 31, 2023. The result includes three employees: John, who joined on January 10, 2023; Alice, who joined on April 15, 2023; and David, who joined on August 20, 2023. These records satisfy the BETWEEN condition applied in each query and are therefore included in the result set.
Although the queries are written differently, Hibernate generates equivalent database operations behind the scenes, which is why the returned data is identical across all three approaches. Notice that the employee Emma does not appear in any of the results because her joining date is February 5, 2024, which falls outside the specified date range. This output confirms that HQL, the Criteria API, and Native SQL can all be used effectively to retrieve records between two dates while producing the same business outcome, allowing developers to choose the querying style that best suits their application’s requirements.
3. Conclusion
Hibernate offers multiple approaches for querying records between two dates. HQL provides a clean object-oriented syntax, the Criteria API enables dynamic and type-safe query construction, and Native SQL offers maximum flexibility for database-specific requirements. For most applications, HQL or the Criteria API are preferred because they maintain database independence while still generating efficient SQL. Native SQL remains a powerful option when advanced database features are needed.
