Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problem with criteria(Is empty for subquery with wrong result)
Problem with criteria [message #899195] Tue, 31 July 2012 01:36 Go to next message
jake frog is currently offline 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.
Re: Problem with criteria [message #899380 is a reply to message #899195] Tue, 31 July 2012 15:57 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1023
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
Re: Problem with criteria [message #899388 is a reply to message #899380] Tue, 31 July 2012 16:25 Go to previous messageGo to next message
jake frog is currently offline 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.
Re: Problem with criteria [message #899416 is a reply to message #899388] Tue, 31 July 2012 18:14 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1023
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
Re: Problem with criteria [message #899423 is a reply to message #899416] Tue, 31 July 2012 18:37 Go to previous messageGo to next message
jake frog is currently offline 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.
Re: Problem with criteria [message #899429 is a reply to message #899423] Tue, 31 July 2012 19:00 Go to previous messageGo to next message
jake frog is currently offline 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
Re: Problem with criteria [message #899447 is a reply to message #899429] Tue, 31 July 2012 21:13 Go to previous message
jake frog is currently offline 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
Previous Topic:Creating XML where the namespaces differ in different packages
Next Topic:Use Spring's MarshallingHttpMessageConverter and MOXy without @XmlRootElement
Goto Forum:
  


Current Time: Fri Sep 19 20:03:28 GMT 2014

Powered by FUDForum. Page generated in 0.02911 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software