Eclipse Community Forums: EclipseLink » Problem with criteria

Help

Home

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

Show: Today's Messages :: Show Polls :: Message Navigator

Problem with criteria [message #899195]

Tue, 31 July 2012 01:36

jake frog

Messages: 8
Registered: July 2012

Junior Member

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.

Report message to a moderator

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

Tue, 31 July 2012 15:57

Chris Delahunt

Messages: 1342
Registered: July 2009

Senior Member

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

Report message to a moderator

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

Tue, 31 July 2012 16:25

jake frog

Messages: 8
Registered: July 2012

Junior Member

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.

Report message to a moderator

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

Tue, 31 July 2012 18:14

Chris Delahunt

Messages: 1342
Registered: July 2009

Senior Member

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

Report message to a moderator

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

Tue, 31 July 2012 18:37

jake frog

Messages: 8
Registered: July 2012

Junior Member

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.

Report message to a moderator

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

Tue, 31 July 2012 19:00

jake frog

Messages: 8
Registered: July 2012

Junior Member

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

Report message to a moderator

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

Tue, 31 July 2012 21:13

jake frog

Messages: 8
Registered: July 2012

Junior Member

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

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

Report message to a moderator

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 [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 07 00:31:52 GMT 2020

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter