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}
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 ProjectDependencies 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
|
|
