Can I use an object as a predicate for criteriaquery in JPA when some fields can be NULL?

Question

I have an object, we will call it Person:

@Entity
@Table(name = "PERSON")
public class Person extends PersistentObject {

    @Id
    @Column(name = "NAME")
    private String name;

    @Column(name = "AGE")
    private String age;

    @Column(name = "NICKNAME")
    private String nickname;

    @Column(name = "HAIR_COLOR")
        private String hairColor;
.
.
.

I want to use this object as a filter on a CriteriaQuery using predicates, but when some of the input Obj is NULL (for example, NickName) it breaks the query. Here is an example of the code I have at the moment.

public List<Person> getPeople(List<Person> peopleToGet) 
    {     
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Person> cq = cb.createQuery(Person.class);
        Root<Person> e = cq.from(Person.class);

        List<Predicate> predicates = new ArrayList<Predicate>();

        for(Person person : peopleToGet)
        {
            predicates.add(cb.or(cb.and(
                    cb.equal(e.get("name"), person.getName()),
                    cb.equal(e.get("age"), person.getAge()),
                    cb.equal(e.get("nickname"), person.getNickname()),
                    cb.equal(e.get("hairColor"), person.getHairColor())
                    )));
.
.
.

Is it possible to use an object as a filter when the objects values are not always the same?

Example input: [{"name":"Bob","age":"30"},{"name":"William","nickname":"Bill"}]

In this example, I want to get every person in the PERSON table where: the persons name is Bob AND the age is 30 OR the persons name is William AND the nickname is Bill

Sorry if this example is a little unclear, I have changed the code to make it as easy as possible to translate to other examples. My knowledge of JPA in general is basic, at best, being self taught (Read, Google) over the past few months. Any assistance with this would be much appreciated. Thanks!!

@KalaiselvanA sorry, my fault. I hadnt changed all my original code. For confidentiality reasons, I changed the exact code and replaced my object with an example "Person" object. I have fixed it, thanks! — user2867999, Nov 29 '17 at 13:38

score 0 · Accepted Answer · answered Nov 29 '17 at 14:31

From what you are saying you want a criterion to be applied, if the relevant property of the bean is not null and not to be applied, if the property is null. For that you have to create a list and then transform to an array to give as argument to and() (just like you do with List<Predicate> predicates):

for( Person person : peopleToGet ) {
    List<Predicate> andPredicates = new ArrayList<>();

    if( person.getName() != null ) andPredicates.add(cb.equal(e.get("name"), person.getName()));
    if( /* similar code for the other properties */ ) /* ... */;

    predicates.add(cb.or(cb.and( andPredicates.toArray(new Predicate[andPredicates.size()]) )));
}

The code above assumes that not all properties of Person are null! If they might be, you have to guard against this accordingly!

oh I see.... Thanks! I will test this out and update the question with the results. — user2867999, Nov 29 '17 at 14:58
This was exactly what I was looking for. This gives me a much more versatile way to get my data from the DB, allowing me to be specific in some things while being very broad in others. Thanks!! — user2867999, Nov 29 '17 at 15:50

Can I use an object as a predicate for criteriaquery in JPA when some fields can be NULL?

1 Answers1