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.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9