Eclipse Community Forums: EclipseLink » Problem with criteria

Home

Home » Eclipse Projects » EclipseLink » Problem with criteria(Is empty for subquery with wrong result)

Problem with criteria [message #899195]

Mon, 30 July 2012 21:36

Eclipse User

Hello.

The JPQL bellow bring me only one result, that is the correct result:

select distinct c from Car c join c.person p where p.dogs is empty

The result is:
[Car name: Yellow Submarine]

But when I create the Criteria bellow:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Car> criteriaQuery = criteriaBuilder.createQuery(Car.class);
Root<Car> mainRoot = criteriaQuery.from(Car.class);
criteriaQuery.select(mainRoot);
criteriaQuery.distinct(true);
Join<Person, Car> join = mainRoot.join("person", JoinType.INNER);
// Is this right?
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.isEmpty(join.get("dogs").as(List.class))));

The wrong result is given:[Car name: Black Horse, Car name: White Thunder, Car name: Herbie, Car name: Yellow Submarine]

It brings all the data from the database. Am I missing anything?

Thanks for the help.

Re: Problem with criteria [message #899380 is a reply to message #899195]

Tue, 31 July 2012 11:57

Eclipse User

The criteriaBuilder.and(expression) portion is unneccessary and should be removed. Try removing that, and if it doesn't work, post the SQL that is executed for the two types of queries. The SQL is logged under the EclipseLink Fine/Finest log level: <property name="eclipselink.logging.level" value="FINEST"/>

Best Regards,
Chris

Re: Problem with criteria [message #899388 is a reply to message #899380]

Tue, 31 July 2012 12:25

Eclipse User

Hello Chris Delahunt, thanks for your reply.

I use the criteria with the "criteriaBuilder.and()" because more parameters are added to the criteria. BUT, I removed the "criteriaBuilder.and()" the wrong result came back again. =/

The generated query is listed bellow:
select with criteria

SELECT DISTINCT t1.ID, t1.COLOR, t1.NAME FROM PERSON t0, CAR t1 WHERE ((SELECT COUNT(t2.ID) FROM DOG t2 WHERE ((t2.PERSON_ID = t0.ID) AND (t0.CAR_ID = t1.ID))) = ?)

bind => [0]

select with jpql

SELECT DISTINCT t1.ID, t1.COLOR, t1.NAME FROM PERSON t0, CAR t1 WHERE (((SELECT COUNT(t2.ID) FROM DOG t2 WHERE (t2.PERSON_ID = t0.ID)) = ?) AND (t0.CAR_ID = t1.ID))

bind => [0]

Thanks for the help.

Re: Problem with criteria [message #899416 is a reply to message #899388]

Tue, 31 July 2012 14:14

Eclipse User

Please file a bug and vote for it. The JPQL "select distinct c from Car c where c.person.dogs is empty" results in the same SQL as the criteria query.

Best Regards,
Chris

Re: Problem with criteria [message #899423 is a reply to message #899416]

Tue, 31 July 2012 14:37

Eclipse User

Hello Chris Delahunt, thanks for your reply.

Is the query created the same? I thought it was different.

The ? symbol is in a different location.

I was thinking in opening the bug with the name:
The JPQL "select distinct c from Car c where c.person.dogs is empty" not results in the same SQL as the criteria query.

Re: Problem with criteria [message #899429 is a reply to message #899423]

Tue, 31 July 2012 15:00

Eclipse User

I do believe is a bug. If I run the criteria like:

criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.isNotEmpty(join.get("dogs").as(List.class))));

and the JPQL like:

select distinct c from Car c join c.person p where p.dogs is not empty

The result is the expected. Very Happy

Re: Problem with criteria [message #899447 is a reply to message #899429]

Tue, 31 July 2012 17:13

Eclipse User

The last question, I opened the bug, how do I vote for it?

https://bugs.eclipse.org/bugs/show_bug.cgi?id=386354

Previous Topic:	Creating XML where the namespaces differ in different packages
Next Topic:	Use Spring's MarshallingHttpMessageConverter and MOXy without @XmlRootElement

Goto Forum:

-=] Back to Top [=-

Current Time: Mon Jul 07 16:41:56 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter