JPA Criteria builder for dynamic query

Tags: , , ,



I have to write a method where I pass it some arguments and depending on which arguments are empty it modifies the query I need to execute at the end. E.g. if I want to select from a table Customer that has an ID, name, surname, address the starting query would be SELECT * FROM Customer WHERE ID=myId AND name=myName AND surname=mySurname (and now we check if the address I sent is empty, if it is NOT, add it to the query). I know how to do this in C#, by simply doing a starting string and then just adding to it and using ExecuteStoreQuery directly, but how would this look in Java and is Criteria Builder a viable tool. Thank you so much!

Answer

Using a CriteriaQuery, it is possible to do something like that (using Static JPA Metamodel Classes):

public List<Customer> findCustomer(Long myId, String myName, String mySurName, String myAddress) {
    CriteriaBuilder cb = enitityManager.getCriteriaBuilder();
    CriteriaQuery<Customer> query = cb.createQuery(Customer.class);
    Root<Customer> root = query.from(Customer.class);

    List<Predicate> predicates = new ArrayList<>();
    predicates.add(cb.equal(root.get(Customer_.ID), myId));
    predicates.add(cb.equal(root.get(Customer_.NAME), myName));
    predicates.add(cb.equal(root.get(Customer_.SURNAME), mySurName));

    if(address != null) {
        predicates.add(cb.equal(root.get(Customer_.ADDRESS), myAddress));
    }

    query.select(root).where(cb.and(predicates.toArray(new Predicate[0])));

    return entityManager.createQuery(query).getResultList();
}

In this case, the address will only be checked if it is not null.

Another example without using Static JPA Metamodel (not 100% sure about this):

public List<Customer> findCustomer(Long myId, String myName, String mySurName, String myAddress) {
    CriteriaBuilder cb = enitityManager.getCriteriaBuilder();
    CriteriaQuery<Customer> query = cb.createQuery(Customer.class);
    Root<Customer> root = query.from(Customer.class);

    List<Predicate> predicates = new ArrayList<>();
    predicates.add(cb.equal(root.get("ID"), myId));
    predicates.add(cb.equal(root.get("name"), myName));
    predicates.add(cb.equal(root.get("surename"), mySurName));

    if(address != null) {
        predicates.add(cb.equal(root.get("address"), myAddress));
    }

    query.select(root).where(cb.and(predicates.toArray(new Predicate[0])));

    return entityManager.createQuery(query).getResultList();
}


Source: stackoverflow