Create queries with Spring Data JPA query By Example technique

Spring Boot Data JPA query By Example tutorial shows the way to produce queries with Spring Data JPA query By Example technique.

Spring is a well-known Java application framework for making enterprise applications. Spring Boot is an evolution of the Spring framework that helps produce stand-alone, production-grade Spring-based applications with minimum effort.

Introduction

Query by Example (QBE) is an easy querying technique with an easy-to-use interface. It permits dynamic Query creation and doesn't require you to write down queries that contain field names. In fact, Query by Example doesn't need you to write down queries by using store-specific query languages in the least.

In Spring data JPA we will use org.springframework.data.domain.Example instance that takes an entity instance (called 'probe' during this context). For example:

Employee employee = new Employee();

employee.setName("Erika");

Example<Employee> employeeExample = Example.of(employee);

By default, fields having null values are neglected in the underlying query, therefore above example will be equivalent to the subsequent JPQL:

SELECT t from Employee t where t.name = 'Erika';

Usage

The query by Example API consists of 3 parts:

1. Probe: The particular example of a domain object with populated fields.

2. ExampleMatcher: The ExampleMatcher carries details on the way to match specific fields. It may be reused across multiple Examples.

3. Example: An Example consists of the probe and also the ExampleMatcher. It's used to produce the query.

To execute query supported example instance, we need to extend our Repository with QueryByExampleExecutor that permits execution of the 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);

}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0

http://maven.apache.org/xsd/maven-4.0.0.xsd">

<modelVersion>4.0.0</modelVersion>

<groupId>com.zetcode</groupId>

<artifactId>springbootquerybyexample</artifactId>

<version>1.0-SNAPSHOT</version>

<packaging>jar</packaging>

<properties>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<maven.compiler.source>11</maven.compiler.source>

<maven.compiler.target>11</maven.compiler.target>

</properties>

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.1.1.RELEASE</version>

</parent>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<dependency>

<groupId>com.h2database</groupId>

<artifactId>h2</artifactId>

<scope>runtime</scope>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

</plugins>

</build>

</project>

Entity

public class Person {

@Id

private String id;

private String firstname;

private String lastname;

private Address address;

// … getters and setters omitted

}

Simple Example

Person person = new Person();

person.setFirstname("Dave");

Example<Person> example = Example.of(person);

Example Matcher

Person person = new Person();

person.setFirstname("Dave");

ExampleMatcher matcher = ExampleMatcher.matching()

.withIgnorePaths("lastname")

.withIncludeNullValues()

.withStringMatcherEnding();

Example<Person> example = Example.of(person, matcher);

Configuring matcher options

ExampleMatcher matcher = ExampleMatcher.matching()

.withMatcher("firstname", endsWith())

.withMatcher("lastname", startsWith().ignoreCase());

QBE is useful in querying data that follows a set of static data or dynamic data. It's also useful when we change the entity time and again without worrying about breaking existing queries. We are a Java Development Company that focuses on custom development services for your enterprise software suite. Get in touch with us to learn more about how you can streamline various business operations with a host of technological tools from Oodles.