Close

Using Literals in JPQL

[Updated: Mar 31, 2018, Created: Mar 31, 2018]

In this example, we will see how to use different types of literal in JPQL.

The Example Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private long salary;
  private Dept dept;
  private Timestamp joinDate;
  private boolean fullTime;
    .............
}
public enum Dept {
  IT, ADMIN, SALE
}

Persisting entities

Let's persist some entities first:

   public static void persistEmployees() {
       Employee employee1 = Employee.create("Diana", Dept.IT, 3000, LocalDate.of(1999, 11, 15), true);
       Employee employee2 = Employee.create("Rose", Dept.ADMIN, 4000, LocalDate.of(2011, 5, 1), false);
       Employee employee3 = Employee.create("Denise", Dept.IT, 1500, LocalDate.of(2006, 1, 10), true);
       Employee employee4 = Employee.create("Mike", Dept.SALE, 2000, LocalDate.of(2015, 8, 20), false);
       EntityManager em = entityManagerFactory.createEntityManager();
       em.getTransaction().begin();
       em.persist(employee1);
       em.persist(employee2);
       em.persist(employee3);
       em.persist(employee4);
       em.getTransaction().commit();
       em.close();
   }

Using literals in Queries

Let's see what type of literals are supported one by one:

Using string literals

A string literal is enclosed in single quotes:

   private static void findEmployeeByName() {
       EntityManager em = entityManagerFactory.createEntityManager();
       Query query = em.createQuery("SELECT e FROM Employee e WHERE e.name = 'Rose'");
       List<Employee> resultList = query.getResultList();
       resultList.forEach(System.out::println);
       em.close();
   }

Output

Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}

Note that a string literal that contains a single quote itself should be escaped with another single quote, for example: Ro'se as literal 'Ro''s'

Using numeric literals

For numeric values, Java integer/long/float/double literal syntax is supported. Appropriate suffixes (e.g. 10L) can be used accordance with the Java Language Specification.

   private static void findEmployeeBySalary() {
       EntityManager em = entityManagerFactory.createEntityManager();
       Query query = em.createQuery("SELECT e FROM Employee e WHERE e.salary > 2000L");
       List<Employee> resultList = query.getResultList();
       resultList.forEach(System.out::println);
       em.close();
   }

Output

Employee{id=1, name='Diana', salary=3000, dept=IT, joinDate=1999-11-14 18:00:00.0, fullTime=true}
Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}

Other than Java literals, SQL numeric syntax is supported as well.

Using Enum literals

The fully qualified enum class name must be specified:

   private static void findEmployeeByDept() {
       EntityManager em = entityManagerFactory.createEntityManager();
       Query query = em.createQuery("SELECT e FROM Employee e WHERE e.dept = com.logicbig.example.Dept.IT");
       List<Employee> resultList = query.getResultList();
       resultList.forEach(System.out::println);
       em.close();
   }

Output

Employee{id=1, name='Diana', salary=3000, dept=IT, joinDate=1999-11-14 18:00:00.0, fullTime=true}
Employee{id=3, name='Denise', salary=1500, dept=IT, joinDate=2006-01-09 18:00:00.0, fullTime=true}

Using date/time literals

The JDBC escape syntax may be used for date, time, and timestamp literals. For example:
e.joinDate > {d '2008-12-31'}

Hibernate, however, does not seem to work with this syntax. In following example we are using hibernate native syntax for date literal:

   private static void findEmployeeByJoinDate() {
       EntityManager em = entityManagerFactory.createEntityManager();
       Query query = em.createQuery("SELECT e FROM Employee e WHERE e.joinDate > '2010-05-01'");
       List<Employee> resultList = query.getResultList();
       resultList.forEach(System.out::println);
       em.close();
   }

Output

Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}
Employee{id=4, name='Mike', salary=2000, dept=SALE, joinDate=2015-08-19 19:00:00.0, fullTime=false}

The JPA specification also says:

The portability of this syntax for date, time, and timestamp literals is dependent upon the JDBC driver in use. Persistence providers are not required to translate from this syntax into the native syntax of the database or driver.

So it might be H2 database which does not support this syntax. However, EclipseLink + H2 work with the JDBC escape syntax (example here).

Using boolean literals

TRUE and FALSE can be used for boolean literals;

   private static void findFullTimeEmployee() {
       EntityManager em = entityManagerFactory.createEntityManager();
       Query query = em.createQuery("SELECT e FROM Employee e WHERE e.fullTime = TRUE");
       List<Employee> resultList = query.getResultList();
       resultList.forEach(System.out::println);
       em.close();
   }

Output

Employee{id=1, name='Diana', salary=3000, dept=IT, joinDate=1999-11-14 18:00:00.0, fullTime=true}
Employee{id=3, name='Denise', salary=1500, dept=IT, joinDate=2006-01-09 18:00:00.0, fullTime=true}

Full Example

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          System.out.println("-- Finding Employee by name --");
          findEmployeeByName();
          System.out.println("-- Finding Employee by salary --");
          findEmployeeBySalary();
          System.out.println("-- Finding Employee by dept --");
          findEmployeeByDept();
          System.out.println("-- Finding Employee by join date --");
          findEmployeeByJoinDate();
          System.out.println("-- Finding full time Employee --");
          findFullTimeEmployee();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", Dept.IT, 3000, LocalDate.of(1999, 11, 15), true);
      Employee employee2 = Employee.create("Rose", Dept.ADMIN, 4000, LocalDate.of(2011, 5, 1), false);
      Employee employee3 = Employee.create("Denise", Dept.IT, 1500, LocalDate.of(2006, 1, 10), true);
      Employee employee4 = Employee.create("Mike", Dept.SALE, 2000, LocalDate.of(2015, 8, 20), false);
      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 findEmployeeByName() {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.name = 'Rose'");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeBySalary() {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.salary > 2000L");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeByDept() {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.dept = com.logicbig.example.Dept.IT");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeByJoinDate() {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.joinDate > '2010-05-01'");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findFullTimeEmployee() {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.fullTime = TRUE");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
-- Finding Employee by name --
Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}
-- Finding Employee by salary --
Employee{id=1, name='Diana', salary=3000, dept=IT, joinDate=1999-11-14 18:00:00.0, fullTime=true}
Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}
-- Finding Employee by dept --
Employee{id=1, name='Diana', salary=3000, dept=IT, joinDate=1999-11-14 18:00:00.0, fullTime=true}
Employee{id=3, name='Denise', salary=1500, dept=IT, joinDate=2006-01-09 18:00:00.0, fullTime=true}
-- Finding Employee by join date --
Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}
Employee{id=4, name='Mike', salary=2000, dept=SALE, joinDate=2015-08-19 19:00:00.0, fullTime=false}
-- Finding full time Employee --
Employee{id=1, name='Diana', salary=3000, dept=IT, joinDate=1999-11-14 18:00:00.0, fullTime=true}
Employee{id=3, name='Denise', salary=1500, dept=IT, joinDate=2006-01-09 18:00:00.0, fullTime=true}

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 Literals in JPQL Select All Download
  • jpql-literal-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
          • META-INF

See Also