Jpa QueryBuilder Multiple expressions in where clause not working

Question

I am having an issue with creating queries with javax.persistence.criteria.CriteriaBuilder. I am using EclipseLink 2.1 and an Oracle 10g database. When building a query with multiple restrictions it will use only the first restriction, not both of them.

Here is my code:

CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Assignment> query = cb.createQuery(Assignment.class);
Root<Assignment> assignment = query.from(Assignment.class);

query.where(
    cb.equal(assignment.get("request"), request),
    cb.isNull(assignment.get("endDate")));

return getEm().createQuery(query).getResultList();

The query producted is:

SELECT ASSX_ID, END_DATE, BEGIN_DATE, COMMENTS, 
       ASSX_OER_ASSIGNED_TO_ID, OER_OER_ID_ASSIGNED_BY, 
       ASSX_RQST_ID 
FROM TARTS.ASSIGNMENT_XREF 
WHERE (ASSX_RQST_ID = ?)

It looks good except for the where clause. I am expecting:

SELECT ASSX_ID, END_DATE, BEGIN_DATE, COMMENTS, 
       ASSX_OER_ASSIGNED_TO_ID, OER_OER_ID_ASSIGNED_BY, 
       ASSX_RQST_ID FROM TARTS.ASSIGNMENT_XREF 
WHERE (ASSX_RQST_ID = ? AND BEGIN_DATE IS NOT NULL)

It doesn't matter if I use cb.and(arg1, arg2) or not also. Am I doing something wrong? Any help will be appreciated.

Absorption

Your cb.isNull should be isNotNull. Did you try to add more restrictions and/or tried to explicitly define your conjunction? – Chew

Answer 1

Your query looks perfectly ok. As you've mentioned, CriteriaQuery.where(Predicate... restrictions) already uses the conjunction of the predicates so there's no need to use cb.and().

The only things I could imagine:

• bug in EclipseLink (try the same with Hibernate)
• some sort of optimization, maybe endDate may never be null?
• your getEm() method does some strange things

Monacid

After much research I found a bug bugs.eclipse.org/bugs/show_bug.cgi?id=316144 in EclipseLink with this very problem. After upgrading to 2.3 it works GREAT! – Absorption