Query by Example (QBE) is a method of query creation that allows us to execute queries based on an example entity instance. The fields of the entity instance need to be populated with the desired criteria values.
In Spring Data JPA we can use org.springframework.data.domain.Example instance which takes an entity instance (called 'probe' in this context). For example:
Employee employee = new Employee();
employee.setName("Erika");
Example<Employee> employeeExample = Example.of(employee);
By default, fields having null values are ignored in the underlying query, so above Example will be equivalent to the following JPQL:
SELECT t from Employee t where t.name = 'Erika';
To execute query based on Example instance, we also need to extend our Repository with QueryByExampleExecutor which allows execution of query by Example.
Following is the QueryByExampleExecutor snippet:
package org.springframework.data.repository.query;
....
public interface QueryByExampleExecutor<T> {
<S extends T> Optional<S> findOne(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example, Sort sort);
<S extends T> Page<S> findAll(Example<S> example, Pageable pageable);
<S extends T> long count(Example<S> example);
<S extends T> boolean exists(Example<S> example);
}
Example
Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private Long id;
private String name;
private String dept;
.............
}
As
Query by Example technique relies on null values to be omitted in the query generation, we should not use primitive identity (like int, long) in our Entity (as in above example) because it will default to 0 instead of null.
Repository
public interface EmployeeRepository extends CrudRepository<Employee, Long>,
QueryByExampleExecutor<Employee> {
}
Example Client
@Component
public class ExampleClient {
@Autowired
private EmployeeRepository repo;
public void run() {
List<Employee> employees = createEmployees();
repo.saveAll(employees);
findAllEmployees();
findEmployeesByName();
findEmployeesByNameAndDept();
}
private void findEmployeesByName() {
System.out.println("-- finding employees with name Tim --");
Employee employee = new Employee();
employee.setName("Tim");
Example<Employee> employeeExample = Example.of(employee);
//calling QueryByExampleExecutor#findAll(Example)
Iterable<Employee> employees = repo.findAll(employeeExample);
for (Employee e : employees) {
System.out.println(e);
}
}
private void findEmployeesByNameAndDept() {
System.out.println("-- finding employees with name Jack and dept IT --");
Employee employee = new Employee();
employee.setName("Jack");
employee.setDept("IT");
Example<Employee> employeeExample = Example.of(employee);
//calling QueryByExampleExecutor#findAll(Example)
Iterable<Employee> employees = repo.findAll(employeeExample);
for (Employee e : employees) {
System.out.println(e);
}
}
private void findAllEmployees() {
System.out.println(" -- getting all Employees --");
Iterable<Employee> iterable = repo.findAll();
for (Employee employee : iterable) {
System.out.println(employee);
}
}
private static List<Employee> createEmployees() {
return Arrays.asList(Employee.create("Diana", "IT"),
Employee.create("Mike", "Admin"),
Employee.create("Tim", "QA"),
Employee.create("Jack", "IT"));
}
}
Main class
public class ExampleMain {
public static void main(String[] args) {
AnnotationConfigApplicationContext context =
new AnnotationConfigApplicationContext(AppConfig.class);
ExampleClient exampleClient = context.getBean(ExampleClient.class);
exampleClient.run();
EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
emf.close();
}
} -- getting all Employees -- Employee{id=1, name='Diana', dept='IT'} Employee{id=2, name='Mike', dept='Admin'} Employee{id=3, name='Tim', dept='QA'} Employee{id=4, name='Jack', dept='IT'} -- finding employees with name Tim -- Employee{id=3, name='Tim', dept='QA'} -- finding employees with name Jack and dept IT -- Employee{id=4, name='Jack', dept='IT'}
Example ProjectDependencies and Technologies Used: - spring-data-jpa 2.1.1.RELEASE: Spring Data module for JPA repositories.
Uses org.springframework:spring-context version 5.1.1.RELEASE - hibernate-core 5.3.7.Final: Hibernate's core ORM functionality.
Implements javax.persistence:javax.persistence-api version 2.2 - h2 1.4.197: H2 Database Engine.
- JDK 1.8
- Maven 3.5.4
|