Following examples shows how to use JPQL LIKE keyword to apply wildcard pattern matching.
% wildcard is used to match zero or more characters.
_ wildcard is used to match any single character.
The optional ESCAPE keyword at the end specifies an escape character of our choice. It is used if we want to use % or _ as literal rather than wildcards.
Examples
Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private String dept;
private long salary;
.............
}
Using LIKE expression
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findEmployeeBySalary();
findEmployeeByName();
//not like and using parameter
findEmployeeByName2();
//escaping
findEmployeeByDept();
} finally {
entityManagerFactory.close();
}
}
public static void persistEmployees() {
Employee employee1 = Employee.create("Diana", "IT", 3000);
Employee employee2 = Employee.create("Rose", "Sales", 2000);
Employee employee3 = Employee.create("Denise", "Admin", 4000);
Employee employee4 = Employee.create("Mike", "H_R", 3500);
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("-- Employees with salary LIKE _500 --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE e.salary LIKE '_500' order by e.salary");
List<Employee> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findEmployeeByName() {
System.out.println("-- Employees name LIKE D% --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE e.name LIKE 'D%'");
List<Employee> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findEmployeeByName2() {
System.out.println("-- Employees name NOT LIKE D% --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE e.name NOT LIKE :nameStartsWith");
query.setParameter("nameStartsWith", "D%");
List<Employee> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findEmployeeByDept() {
System.out.println("-- Employees dept LIKE '%@_%' ESCAPE '@' --");
EntityManager em = entityManagerFactory.createEntityManager();
//our escape character is '@'
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE e.dept LIKE '%@_%' ESCAPE '@'");
List<Employee> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
} -- Employees with salary LIKE _500 -- Employee{id=4, name='Mike', dept='H_R', salary=3500} -- Employees name LIKE D% -- Employee{id=1, name='Diana', dept='IT', salary=3000} Employee{id=3, name='Denise', dept='Admin', salary=4000} -- Employees name NOT LIKE D% -- Employee{id=2, name='Rose', dept='Sales', salary=2000} Employee{id=4, name='Mike', dept='H_R', salary=3500} -- Employees dept LIKE '%@_%' ESCAPE '@' -- Employee{id=4, name='Mike', dept='H_R', salary=3500}
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
|
|