JPA 2 Criteria API: why is isNull being ignored when in conjunction with equal?
Posted
by Vítor Souza
on Stack Overflow
See other posts from Stack Overflow
or by Vítor Souza
Published on 2010-06-10T12:33:10Z
Indexed on
2010/06/10
23:12 UTC
Read the original article
Hit count: 463
I have the following entity class (ID inherited from PersistentObjectSupport class):
@Entity
public class AmbulanceDeactivation extends PersistentObjectSupport implements Serializable {
private static final long serialVersionUID = 1L;
@Temporal(TemporalType.DATE) @NotNull
private Date beginDate;
@Temporal(TemporalType.DATE)
private Date endDate;
@Size(max = 250)
private String reason;
@ManyToOne @NotNull
private Ambulance ambulance;
/* Get/set methods, etc. */
}
If I do the following query using the Criteria API:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<AmbulanceDeactivation> cq = cb.createQuery(AmbulanceDeactivation.class);
Root<AmbulanceDeactivation> root = cq.from(AmbulanceDeactivation.class);
EntityType<AmbulanceDeactivation> model = root.getModel();
cq.where(cb.isNull(root.get(model.getSingularAttribute("endDate", Date.class))));
return em.createQuery(cq).getResultList();
I get the following SQL printed in the log:
FINE: SELECT ID, REASON, ENDDATE, UUID, BEGINDATE, VERSION, AMBULANCE_ID FROM AMBULANCEDEACTIVATION WHERE (ENDDATE IS NULL)
However, if I change the where() line in the previous code to this one:
cq.where(cb.isNull(root.get(model.getSingularAttribute("endDate", Date.class))),
cb.equal(root.get(model.getSingularAttribute("ambulance", Ambulance.class)), ambulance));
I get the following SQL:
FINE: SELECT ID, REASON, ENDDATE, UUID, BEGINDATE, VERSION, AMBULANCE_ID FROM AMBULANCEDEACTIVATION WHERE (AMBULANCE_ID = ?)
That is, the isNull criterion is totally ignored. It is as if it wasn't even there (if I provide only the equal criterion to the where() method I get the same SQL printed).
Why is that? Is it a bug or am I missing something?
© Stack Overflow or respective owner