Spring Data JPA: Query by Example?

Question

Using Spring Data JPA can I do a query by example where a particular entity instance is used as the search criteria?

For example (no pun intended), if I have a Person entity that looks like:

@Entity
public class Person {
  private String firstName;
  private String lastName;
  private boolean employed;
  private LocalDate dob;
  ...
}

I could find all employed persons with a last name of Smith born on January 1, 1977 with an example:

Person example = new Person();
example.setEmployed(true);
example.setLastName("Smith");
example.setDob(LocalDate.of(1977, Month.JANUARY, 1));
List<Person> foundPersons = personRepository.findByExample(example);

Your questions is not clear. What exactly are you looking for? — Uresh K, Dec 23 '14 at 19:51
No you cannot as that is limited to hibernate, Spring Data is designed for JPA. You can always create a `Specification` and execute that which you could then use to do something similar. — M. Deinum, Dec 23 '14 at 20:08

score 78 · Answer 1 · edited Jun 08 '16 at 12:13

78

This is now possible with Spring Data. Check out http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

Person person = new Person();                         
person.setLastname("Smith");                          
Example<Person> example = Example.of(person);
List<Person> results = personRepository.findAll(example);

Note that this requires very recent 2016 versions

    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
        <version>1.10.1.RELEASE</version>       
    </dependency>
    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-commons</artifactId>
        <version>1.12.1.RELEASE</version>
    </dependency>

see https://github.com/paulvi/com.example.spring.findbyexample

edited Jun 08 '16 at 12:13

Paul Verest

60,022
51
208
332

answered May 03 '16 at 19:45

Adam Erstelle

2,454
3
21
35

What about a more complex domain. I mean when we have associations how can I use the Example ? Any idea ? – Mahdi Shabani Jan 08 '17 at 07:26
1

For a more complex domain, I ended up using QueryDSL with Spring Data JPA. Took me a bit to learn and wrap my head around, but we ended up actually refactoring our code base and eliminating the Example usage completely. – Adam Erstelle Jan 09 '17 at 19:05
There's no problem with complex nested domains. Create a domain object with all the associations you need and Example.of(x) will do the job. Sadly I am in trouble with 'between' on date objects. – Pascal Jan 12 '17 at 07:35
can't do between, lessThan greaterThan.... spring teamp are not interested to add this feature... or accept pull request... – robert trudel Aug 02 '17 at 12:44

Brice Roncace · Answer 2 · 2015-01-06T04:07:26.563

Using Spring data's Specification interface I was able to approximate the use of query by example. Here's a PersonSpec class which implements Specification and requires an "example" person in order to setup the Predicate returned by the Specification:

public class PersonSpec implements Specification<Person> {

  private final Person example;

  public PersonSpec(Person example) {
    this.example = example;
  }

  @Override
  public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
    List<Predicate> predicates = new ArrayList<>();

    if (StringUtils.isNotBlank(example.getLastName())) {
      predicates.add(cb.like(cb.lower(root.get(Person_.lastName)), example.getLastName().toLowerCase() + "%"));
    }

    if (StringUtils.isNotBlank(example.getFirstName())) {
      predicates.add(cb.like(cb.lower(root.get(Person_.firstName)), example.getFirstName().toLowerCase() + "%"));
    }

    if (example.getEmployed() != null) {
      predicates.add(cb.equal(root.get(Person_.employed), example.getEmployed()));
    }

    if (example.getDob() != null) {
      predicates.add(cb.equal(root.get(Person_.dob), example.getDob()));
    }

    return andTogether(predicates, cb);
  }

  private Predicate andTogether(List<Predicate> predicates, CriteriaBuilder cb) {
    return cb.and(predicates.toArray(new Predicate[0]));
  }
}

The repository is simply:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor {}

Usage example:

Person example = new Person();
example.setLastName("James");
example.setEmployed(true);
PersonSpec personSpec = new PersonSpec(example);
List<Person> persons = personRepository.findAll(personSpec);

Good but still I need to create an implementation class for each Query I want to write. — Muhammad Hewedy, Jan 22 '16 at 16:14
specification is nice a bit long to write... could be more interesting to be able to search on all field with reflection without writing all this code... — robert trudel, Jul 24 '17 at 13:12
@PaulVerest `Person_` can be generated with a library like e.g. [hibernate-jpamodelgen](https://hibernate.org/orm/tooling/). Nevertheless, I use instead the Lombok annotation `FieldNameConstants`. — t0r0X, Nov 10 '21 at 11:47

David Rabinowitz · Accepted Answer · 2015-01-14T08:59:18.223

13

Spring data relies on top of JPA and EntityManager, not Hibernate and Session, and as such you do not have findByExample out of the box. You can use the spring data automatic query creation and write a method in your repository with the following signature:

List<Person> findByEmployedAndLastNameAndDob(boolean employed, String lastName, LocalDate dob);

edited Jan 14 '15 at 08:59

answered Dec 23 '14 at 20:08

David Rabinowitz

29,904
14
93
125

JPA != Hibernate. Makes sense. I think going the `Specification` route as mentioned by @M. Deinum is the way to go here as I'd like to be able to perform more ad hoc queries. – Brice Roncace Dec 23 '14 at 21:14
1

You can have a look at the examples here http://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/. It may serve your needs – David Rabinowitz Dec 23 '14 at 22:05
I opened [DATAJPA-844](https://jira.spring.io/browse/DATAJPA-844) to request this feature in Spring Data JPA. It seems like it's on their roadmap! – Mauro Molinari Jan 28 '16 at 10:07
1

this answer is now out-of-date – robjwilkins Mar 09 '18 at 15:39

Spring Data JPA: Query by Example?

3 Answers3

Linked