Close

Spring Data JPA - Query By Example

[Last Updated: Oct 25, 2018]

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 Project

Dependencies 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

Getting started with Query By Example Select All Download
  • spring-data-jpa-query-by-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleClient.java
          • resources
            • META-INF

    See Also