user2867999 user2867999 - 1 year ago 78
reST (reStructuredText) Question

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

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

Answer Source

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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download