Close

JPA Criteria API - Applying Between Predicate

[Updated: Oct 10, 2018, Created: Oct 10, 2018]

Following example shows how to use CriteriaBuilder.between() methods.

There are two methods defined in CriteriaBuilder to apply between operator:

Predicate between(Expression<? extends Y> v, Y x, Y y);

Quick Example:

  CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
  CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
  Root<Employee> employee = query.from(Employee.class);
  query.select(employee)
       .where(criteriaBuilder.between(employee.get(Employee_.salary), 2000L, 4000L));
  List<Employee> resultList = em.createQuery(query).getResultList();
Predicate between(Expression<? extends Y> v, Expression<? extends Y> x, Expression<? extends Y> y);

Quick Example:

  CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
  CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
  Root<Employee> employee = query.from(Employee.class);
  Join<Employee, Employee> supervisor = employee.join(Employee_.supervisor);
  query.select(employee)
       .where(criteriaBuilder.between(employee.get(Employee_.salary),
                supervisor.get(Employee_.salary), criteriaBuilder.literal(4000L)));
  List<Employee> resultList = em.createQuery(query).getResultList();

Complete Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private Timestamp joinDate;
  private long salary;
  @OneToOne
  private Employee supervisor;
    .............
}

Applying Between predicate

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeBySalaryRange();
          findEmployeeBySalaryRange2();
          findEmployeeByJoinDate();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findEmployeeBySalaryRange() {
      System.out.println("-- Employee with salary BETWEEN 2000 and 4000 --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(employee)
           .where(criteriaBuilder.between(employee.get(Employee_.salary), 2000L, 4000L));
      List<Employee> resultList = em.createQuery(query).getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeBySalaryRange2() {
      System.out.println("-- Employee with salary BETWEEN supervisor's salary and 4000 --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      Join<Employee, Employee> supervisor = employee.join(Employee_.supervisor);
      query.select(employee)
           .where(criteriaBuilder.between(employee.get(Employee_.salary),
                   supervisor.get(Employee_.salary), criteriaBuilder.literal(4000L)));
      List<Employee> resultList = em.createQuery(query).getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeByJoinDate() {
      System.out.println("-- Employee with join date BETWEEN '1990-01-01' and '2010-05-01' --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(employee)
           .where(criteriaBuilder.between(employee.get(Employee_.joinDate),
                   localToTimeStamp(LocalDate.of(1990, 01, 01)),
                   localToTimeStamp(LocalDate.of(2010, 05, 01))))
           .orderBy(criteriaBuilder.desc(employee.get(Employee_.joinDate)));
      List<Employee> resultList = em.createQuery(query).getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static Timestamp localToTimeStamp(LocalDate date) {
      return Timestamp.from(date.atStartOfDay().toInstant(ZoneOffset.UTC));
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 3000, LocalDate.of(1999, 11, 15));
      Employee employee2 = Employee.create("Rose", 4000, LocalDate.of(2011, 5, 1));
      Employee employee3 = Employee.create("Denise", 1500, LocalDate.of(2006, 1, 10));
      Employee employee4 = Employee.create("Mike", 2000, LocalDate.of(2015, 8, 20));
      employee1.setSupervisor(employee4);
      employee2.setSupervisor(employee1);
      employee3.setSupervisor(employee1);
      employee4.setSupervisor(null);


      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
      System.out.println("-- employee persisted --");
      System.out.println(employee1);
      System.out.println(employee2);
      System.out.println(employee3);
      System.out.println(employee4);
  }
}
-- employee persisted --
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000, supervisor=Mike}
Employee{id=2, name='Rose', joinDate=2011-04-30 19:00:00.0, salary=4000, supervisor=Diana}
Employee{id=3, name='Denise', joinDate=2006-01-09 18:00:00.0, salary=1500, supervisor=Diana}
Employee{id=4, name='Mike', joinDate=2015-08-19 19:00:00.0, salary=2000, supervisor=null}
-- Employee with salary BETWEEN 2000 and 4000 --
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000, supervisor=Mike}
Employee{id=2, name='Rose', joinDate=2011-04-30 19:00:00.0, salary=4000, supervisor=Diana}
Employee{id=4, name='Mike', joinDate=2015-08-19 19:00:00.0, salary=2000, supervisor=null}
-- Employee with salary BETWEEN supervisor's salary and 4000 --
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000, supervisor=Mike}
Employee{id=2, name='Rose', joinDate=2011-04-30 19:00:00.0, salary=4000, supervisor=Diana}
-- Employee with join date BETWEEN '1990-01-01' and '2010-05-01' --
Employee{id=3, name='Denise', joinDate=2006-01-09 18:00:00.0, salary=1500, supervisor=Diana}
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000, supervisor=Mike}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • hibernate-jpamodelgen 5.3.6.Final: Annotation Processor to generate JPA 2 static metamodel classes.
  • JDK 1.8
  • Maven 3.5.4

CriteriaBuilder.between() Examples Select All Download
  • jpa-criteria-api-between-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also