Close

JPQL BETWEEN Expression Examples

[Updated: May 16, 2018, Created: May 15, 2018]

Following example shows how to use JPQL BETWEEN keyword to apply a range of values in WHERE clause.

Example

Entity

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

Using BETWEEN expression

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeBySalary();
          findEmployeeByJoinDate();
          //setting parameter
          findEmployeeByJoinDate2();
          //not between
          findEmployeeByJoinDate3();
      } finally {
          entityManagerFactory.close();
      }
  }

  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));
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
  }

  private static void findEmployeeBySalary() {
      System.out.println("-- Employee with salary BETWEEN 2000 and 4000 --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e FROM Employee e WHERE e.salary BETWEEN 2000L AND 4000L order by e.salary");
      List<Employee> resultList = 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();
      Query query = em.createQuery(
              "SELECT e FROM Employee e WHERE e.joinDate BETWEEN '1990-01-01' AND '2010-05-01' order "
                      + "by e.joinDate");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeByJoinDate2() {
      System.out.println("-- Employee with join date BETWEEN '2005-01-01' and '2018-01-01' --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e FROM Employee e WHERE e.joinDate BETWEEN :startDate AND :endDate order "
                      + "by e.joinDate");
      query.setParameter("startDate", localToTimeStamp(LocalDate.of(2005,1,1)));
      query.setParameter("endDate", localToTimeStamp(LocalDate.of(2018,1,1)));
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeByJoinDate3() {
      System.out.println("-- Employee with join date NOT BETWEEN'2005-01-01' and '2018-01-01' --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e FROM Employee e WHERE e.joinDate NOT BETWEEN :startDate AND :endDate order "
                      + "by e.joinDate");
      query.setParameter("startDate", localToTimeStamp(LocalDate.of(2005,1,1)));
      query.setParameter("endDate", localToTimeStamp(LocalDate.of(2018,1,1)));
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }


  private static Timestamp localToTimeStamp(LocalDate date){
      return Timestamp.from(date.atStartOfDay().toInstant(ZoneOffset.UTC));
  }
}
-- Employee with salary BETWEEN 2000 and 4000 --
Employee{id=4, name='Mike', joinDate=2015-08-19 19:00:00.0, salary=2000}
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000}
Employee{id=2, name='Rose', joinDate=2011-04-30 19:00:00.0, salary=4000}
-- Employee with join date BETWEEN '1990-01-01' and '2010-05-01' --
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000}
Employee{id=3, name='Denise', joinDate=2006-01-09 18:00:00.0, salary=1500}
-- Employee with join date BETWEEN '2005-01-01' and '2018-01-01' --
Employee{id=3, name='Denise', joinDate=2006-01-09 18:00:00.0, salary=1500}
Employee{id=2, name='Rose', joinDate=2011-04-30 19:00:00.0, salary=4000}
Employee{id=4, name='Mike', joinDate=2015-08-19 19:00:00.0, salary=2000}
-- Employee with join date NOT BETWEEN'2005-01-01' and '2018-01-01' --
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  • JDK 1.8
  • Maven 3.3.9

Using BETWEEN clause Select All Download
  • jpql-between-expression
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
          • META-INF

See Also