Master Spring Data JPA Query by Example: Practical Guide & Code Samples

Environment: SpringBoot 2.7.16

1. Introduction

Query by Example (QBE) is a query method provided by Spring Data JPA that lets you build queries by creating a prototype object containing the desired criteria; Spring Data JPA automatically translates these criteria into SQL.

QBE offers a declarative way to query without writing SQL manually: you set object properties as query conditions and the framework generates the corresponding SQL.

Why use Query by Example

Simplify query construction : For complex conditions, defining them via object properties is more intuitive and concise than hand‑crafting SQL.

Type safety : Conditions are expressed through typed object fields, allowing the compiler to catch mismatches that raw SQL cannot.

Readability and maintainability : Object‑based criteria are easier to understand than long SQL strings, and changing a condition only requires updating the prototype object.

2. How to use

QBE queries consist of four parts:

Probe : an instance of the domain object with fields populated as the example.

ExampleMatcher : defines how each field should be matched and can be reused across examples.

Example : combines a probe and an ExampleMatcher to create the query.

FetchableFluentQuery : provides a fluent API for further customizing the query (sorting, projection, result handling, etc.).

Scenarios suitable for QBE :

Querying data with a set of static or dynamic constraints.

Frequent refactoring of domain objects without breaking existing queries.

Operating independently of the underlying data‑store API.

QBE limitations :

Does not support nested or grouped property constraints (e.g., firstname = ?0 or (firstname = ?1 AND lastname = ?2)).

Only supports start/contains/end/regex matching for strings and exact matching for other types.

3. Practical examples

Prepare data

Domain entity

<code>@Entity
@Table(name = "emp")
public class Emp {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;
  private Integer age;
  private String name;
  private String address;
  private Integer state;
}
</code>

Define repository interface

<code>public interface EmpRepository extends JpaRepository<Emp, Integer>, QueryByExampleExecutor<Emp> {
}
</code>

Example 1

<code>@Resource
private EmpRepository empRepository;

public Optional<Emp> queryEmp1(Integer age) {
  Emp emp = new Emp();
  emp.setAge(age);
  emp.setName("张三");
  Example<Emp> example = Example.of(emp);
  return this.empRepository.findOne(example);
}
</code>

Generated SQL:

<code>Hibernate: select emp0_.id as id1_0_, emp0_.address as address2_0_, emp0_.age as age3_0_, emp0_.name as name4_0_, emp0_.state as state5_0_ from emp emp0_ where emp0_.age=20 and emp0_.name=? limit ?
</code>

The WHERE clause contains only the non‑null fields (age and name), demonstrating QBE's automatic omission of null properties.

Example 2

<code>public Optional<Emp> queryEmp2(Emp emp) {
  Example<Emp> example = Example.of(emp);
  return this.empRepository.findOne(example);
}
</code>

Controller receiving the object:

<code>@PostMapping("/q2")
public Emp q2(@RequestBody Emp emp) {
  return this.empService.queryEmp2(emp).orElse(new Emp());
}
</code>

Resulting SQL is identical to Example 1 because only age and name are provided.

Example 3 – Modify default behavior

<code>public Optional<Emp> queryEmp3(Emp emp) {
  ExampleMatcher matcher = ExampleMatcher.matching()
      .withIgnorePaths("age")
      .withIncludeNullValues()
      .withStringMatcher(StringMatcher.CONTAINING);
  Example<Emp> example = Example.of(emp, matcher);
  return this.empRepository.findOne(example);
}
</code>

Generated SQL includes null‑valued fields and ignores the age field:

<code>Hibernate: select emp0_.id as id1_0_, emp0_.address as address2_0_, emp0_.age as age3_0_, emp0_.name as name4_0_, emp0_.state as state5_0_ from emp emp0_ where emp0_.id=105 and (emp0_.name like ? escape ?) and (emp0_.state is null) and (emp0_.address is null) limit ?
</code>

Example 4 – Custom property matching

Ending match on address:

<code>import static org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers.endsWith;

public List<Emp> queryEmp4(Emp emp) {
  ExampleMatcher matcher = ExampleMatcher.matching()
      .withMatcher("address", endsWith());
  Example<Emp> example = Example.of(emp, matcher);
  return this.empRepository.findAll(example);
}
</code>

Prefix match version:

<code>public List<Emp> queryEmp4(Emp emp) {
  ExampleMatcher matcher = ExampleMatcher.matching()
      .withMatcher("address", startsWith());
  Example<Emp> example = Example.of(emp, matcher);
  return this.empRepository.findAll(example);
}
</code>

Example 5 – Fluent API usage

<code>public List<Emp> queryEmp5(Emp emp) {
  Example<Emp> example = Example.of(emp);
  return this.empRepository.findBy(example, q -> q
      .sortBy(Sort.by("id").descending())
      .all());
}
</code>

The second argument of findBy is a Function<FetchableFluentQuery<Emp>, List<Emp>> that can apply sorting, paging, counting, etc.; here it sorts by id descending and returns all matches.

StringMatcher options (e.g., EXACT, STARTING, ENDING, CONTAINING, REGEX) are shown in the following screenshot:

Understanding Spring Data JPA's Query by Example reveals its potential to simplify query construction, improve code readability, and leverage type safety, making it a valuable tool for backend developers.