JPA Specifications by Example

Question

Spring Boot here. I'm trying to wrap my head around JpaRepositories and Specifications when used in the context of implementing complex queries and am struggling to see the "forest through the trees" on several items.

A canonical example of a Specification is as follows:

public class PersonSpecification implements Specification<Person> {
    private Person filter;

    public PersonSpecification(Person filter) {
        super();
        this.filter = filter;
    }

    public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> cq,
            CriteriaBuilder cb) {
        Predicate p = cb.disjunction();

        if (filter.getName() != null) {
            p.getExpressions()
                    .add(cb.equal(root.get("name"), filter.getName()));
        }

        if (filter.getSurname() != null && filter.getAge() != null) {
            p.getExpressions().add(
                    cb.and(cb.equal(root.get("surname"), filter.getSurname()),
                            cb.equal(root.get("age"), filter.getAge())));
        }

        return p;
    }
}

In this toPredicate(...) method, what do the Root<Person> and CriteriaQuery represent? Most importantly, it sounds like you need to create one Specification impl for each type of filter you want to apply, because each spec gets translated into one and only one predicate...so for instance if I wanted to find all people with a surname of "Smeeb" and an age greater than 25, it sounds like I would need to write a LastnameMatchingSpecification<Person> as well as a AgeGreaterThanSpecification<Person>. Can someone confirm or clarify this for me?!

Answer 1

This was hard for me too at first, but now I'm making dynamic queries with ease and a single Specification per Table (when Advanced Searching is necessary)

Think of these objects like:

1. Root is your table.
2. CriteriaQuery is your query, good for applying distinct, subqueries, order by, etc.
3. CriteriaBuilder is your conditions, good for creating your where clauses

--

Always start with a List then condense them at the end with either AND/OR conditions based on your needs.

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

    if(filter.getName() != null) {
        predicates.add(cb.equal(root.get("name"), filter.getName());
    }
    if(filter.getSurname() != null) {
        predicates.add(cb.equal(root.get("surname"), filter.getSurname());
    }
    if(filter.getAge() != null) {
        predicates.add(cb.equal(root.get("age"), filter.getAge());
    }
    if(predicates.isEmpty()){
        predicates.add(cb.equal(root.get("id"), -1);
        /* 
         I like to add this because without it if no criteria is specified then 
         everything is returned. Because that's how queries work without where 
         clauses. However, if my user doesn't provide any criteria I want to 
         say no results found. 
        */
    }

    return query.where(cb.and(predicates.toArray(new Predicate[0])))
                .distinct(true).orderBy(cb.desc(root.get("name")).getRestriction();
}

Now my user can pass any combination of these 3 fields here and this logic would dynamically build the query to include conditions for them.

e.g. name = John and surname = Doe and age = 41 or name = John and age = 41 or name = John etc.

Lastly, when searching strings I would recommend using cb.like and not cb.equal so that it would make your search capable of partial searching with % is passed by user or frontend system.

Keep in mind cb.like is not case sensitive by default it needs to be used in conjunction with cb.lower or cb.upper such as:

 predicates.add(cb.like(cb.lower(root.get("name"), filter.getName().toLowercase());

Hope this helps !