CODEWITHSUNDEEP
springhibernatecriteria
DaFoot
DaFoot

Reputation: 1567

How do I query Hibernate for object where property may be null or specific value?

I have the following objects I'm working with:

RawRead RawRead.Checkpoint

Checkpoint.EndCustomer

Guard

Where Checkpoint and Guard are properties of RawRead, EndCustomer is a property of Checkpoint. All are objects.

My current Hibernate gubbins:

Criteria crit = sess.createCriteria(RawRead.class);
crit.add(
  Restrictions.or(
    Restrictions.eq("checkpoint", null),
    Restrictions.in("checkpoint.parentEndCustomer",collectionOfEndCustomers)
  )
);

So Checkpoint can be null, but if it is there I only want the RawRead objects where the parentEndCustomer object is in the checkpoint.parentEndCustomer property.

I hope that makes sense.

My guesstimate above produces an error that (to me) suggests that my criteria are incorrectly specified:

[Request processing failed; nested exception is org.hibernate.QueryException: could not resolve property: checkpoint.parentEndCustomer of: uk.co.romar.guardian.objects.RawRead] with root cause org.hibernate.QueryException: 
could not resolve property: checkpoint.parentEndCustomer of: uk.co.romar.guardian.objects.RawRead at 
org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:81) at 
org.hibernate.persister.entity.AbstractPropertyMapping.toColumns(AbstractPropertyMapping.java:96)   at  
org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:62) at 
org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1457) at 
org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:483)

Relevant bits of RawRead:

@ManyToOne
@JoinColumn(name="CHECKPOINT_OID")
@NotFound(action=NotFoundAction.IGNORE)
public Checkpoint checkpoint = null;
public void setCheckpoint(Checkpoint in) {this.checkpoint = in;}
public Checkpoint getCheckpoint() {return this.checkpoint;}

@ManyToOne
@JoinColumn(name="GUARD_OID")
@NotFound(action=NotFoundAction.IGNORE)
private Guard guard = null;
public void setGuard(Guard in) {this.guard = in;}
public Guard getGuard() {return this.guard;}

And from Checkpoint:

    @ManyToOne
@JoinColumn(name="ENDCUSTOMER_OID")
@NotFound(action=NotFoundAction.IGNORE)
private EndCustomer parentEndCustomer = null;
public EndCustomer getParentEndCustomer() {return this.parentEndCustomer;}
public void setParentEndCustomer(EndCustomer ownerCustomer) {this.parentEndCustomer = ownerCustomer;}

EDIT Follows after implementing first answer from below.

If I have some data like this in the database (I hope the notation makes sense!):

RawRead {
 ID=1
 checkpoint={id=1,parentEndCustomer={ID=1}}
}
RawRead {
 ID=2
 checkpoint={id=4,parentEndCustomer={ID=4}}
}
RawRead {
 ID=3
 checkpoint={id=7,parentEndCustomer={ID=31}}
}
RawRead {
 ID=4
 checkpoint={null}
}

and the collectionOfEndCustomers given in the Restriction is like this: EndCustomer={ID=31}

The I would want to retrieve RawReads 3 and 4 only. RawRead 1 & 2 are rejected because the parentEndCustomer of the child checkpoint property does't match the one passed in to the restriction in collectionOfEndCustomers.

RawRead.3 is should be selected because the parentEndCustomer matches one in the collection passed in. RawRead.4 should be selected because the checkpoint is null.

Following the guidance in the first answer below results in all of the above RawReads being returned rather than the subset I'm after.

Upvotes: 0

Views: 4132

Answers (1)

JB Nizet
JB Nizet

Reputation: 692211

You can't chain properties like you would do in HQL. You must use joins, with Criteria. And since checkpoint can be null, you must use a left join. Moreover, Restrictions.eq() can't be used to compare something with null (as in HQL and SQL). You must use Restrictions.isNull() to do that.

So your query should look like this:

Criteria crit = sess.createCriteria(RawRead.class, "rawRead");
crit.createAlias("rawRead.checkpoint", "checkpoint", Criteria.LEFT_JOIN)
crit.add(
    Restrictions.or(
        Restrictions.isNull("checkpoint.id"),
        Restrictions.in("checkpoint.parentEndCustomer", collectionOfEndCustomers)
    )
);

Upvotes: 2

Related Questions