CODEWITHSUNDEEP
spring-bootspring-dataspring-data-jpajpa-criteria
smeeb
smeeb

Reputation: 29577

JPA Specifications by Example

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?!

Upvotes: 19

Views: 35067

Answers (4)

jaleel
jaleel

Reputation: 1187

public List<Transaction> getAlltransactionsBy(RecordFilter filter){
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Transaction> cq = cb.createQuery(Transaction.class);

    Root<Transaction> transaction = cq.from(Transaction.class);
    
    List<Predicate> predicatesList = new ArrayList<Predicate>();
    
    if(filter.userid > 0 ) {
        Predicate uesrIdPredicate = cb.greaterThan(transaction.get("userid"), filter.userid);
         predicatesList.add(uesrIdPredicate);
    }
    
    Predicate tranAmtBetweenPredicate = cb.between(transaction.get("tranAmt"), filter.minAmt, filter.maxAmt);
    predicatesList.add(tranAmtBetweenPredicate);
   
    
    Predicate[] tranPredicateArray = predicatesList.toArray(new Predicate[predicatesList.size()]);

    
    cq.where(tranPredicateArray);

    TypedQuery<Transaction> query = entityManager.createQuery(cq);
    return query.getResultList();
}

This code working in my case for my Transaction Entity.

Upvotes: 0

razvanone
razvanone

Reputation: 1479

If you need to also use joins, you will have to write something like this:



    @Override
    public Predicate toPredicate(Root<Opportunity> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        Predicate predicate = cb.conjunction();
        ...
        predicate.getExpressions().add(cb.equal(root.join("address").get("streetName"), person.getAddress().getStreetName()));
        ...
        return predicate;
    }

Upvotes: 1

wheeleruniverse
wheeleruniverse

Reputation: 1635

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 !

Upvotes: 24

Jens Schauder
Jens Schauder

Reputation: 81998

what do the Root<Person> and CriteriaQuery represent?

Root is the root of your query, basically What you are querying for. In a Specification, you might use it to react dynamically on this. This would allow you, for example, to build one OlderThanSpecification to handle Cars with a modelYear and Drivers with a dateOfBirth by detecting the type and using the appropriate property.

Similiar CriteriaQuery is the complete query which you again might use to inspect it and adapt the Predicate you are constructing based on it.

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 an AgeGreaterThanSpecification<Person>. Can someone confirm or clarify this for me?!

I think you have that wrong. The Spring Data interfaces accepting Specifications only accept a single Specification. So if you want to find all Persons with a certain name and a certain age you would create one Specification. Similar to the example you quote which also combines two constraints.

But you may create separate Specifications and then create another one combining those if you want to use each separately, but also combined.

Upvotes: 7

Related Questions