Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » CriteriaQuery<Boolean> fails with NoResultException but works when running generated SQL manua
CriteriaQuery<Boolean> fails with NoResultException but works when running generated SQL manua [message #651301] Fri, 28 January 2011 19:32 Go to next message
Matthew Denison is currently offline Matthew Denison
Messages: 6
Registered: January 2011
Junior Member
Hi All,

Hope someone can shed some light on a problem I have been having.

I have two objects - Object1 and Object2. Object1 and Object2 have a bidirectional OneToOne mapping to each other. Object1 owns the relationship. Object2 has a number of Boolean fields that are protected fields of a MappedSuperclass. Those boolean fields are what I want to query for.

Here are two queries I have tried.

Query 1: (This is the query I want to work)

CriteriaQuery<Boolean> cq = cb.createQuery(Boolean.class);
Root<Object1> obj1 = cq.from(Object1.class);
Join<Object1, Object2> obj2 = obj1.join(Object1_.object2);
cq.select(obj2.get(A_METHOD_ARG));
cq.where(
cb.and(
cb.equal(obj1.get(Object1_.field1), A_METHOD_ARG),
cb.equal(obj1.get(Object1_.field2), A_METHOD_ARG)
)
);
TypedQuery<Boolean> query = em.createQuery(cq);

I get a NoResultException when I run this. The SQL that is being generated by EclipseLink 2.2 is "SELECT t0.MYBOOLEAN FROM Object2 t0, Object1 t1 WHERE (((t1.FIELD1 = 1) AND (t1.FIELD2 = 1)) AND (t0.ID = t1.OBJECT2_ID))" and if I run this directly on the MySQL 5.5 database I get a single column with a 1 value in it so the query is working when run manually. Confused on that...

Query 2: (I can make this work but not optimal)

CriteriaQuery<Object1> cq = cb.createQuery(Object1.class);
Root<Object1> obj1 = cq.from(Object1.class);
cq.select(obj1);
cq.where(
cb.and(
cb.equal(obj1.get(Object1_.field1), A_METHOD_ARG),
cb.equal(obj1.get(Object1_.field2), A_METHOD_ARG)
)
);
TypedQuery< Object1> query = em.createQuery(cq);

This works and retrieves my Object1 using the same parameters. I can then retrieve Object2 and manually get the Boolean value that is stored in the DB. Is the join causing problems somehow?

Thanks for any help!
Re: CriteriaQuery<Boolean> fails with NoResultException but works when running generated SQL m [message #651445 is a reply to message #651301] Mon, 31 January 2011 00:02 Go to previous messageGo to next message
Matthew Denison is currently offline Matthew Denison
Messages: 6
Registered: January 2011
Junior Member
After more digging, it looks like my native SQL runs as a DataReadQuery and my CriteriaQuery runs as a ReportQuery. Does anybody know why a DataReadQuery would return my boolean but the ReportQuery would return nothing?
Re: CriteriaQuery<Boolean> fails with NoResultException but works when running generated SQL m [message #651506 is a reply to message #651445] Mon, 31 January 2011 13:23 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1023
Registered: July 2009
Senior Member
Hello,

I see you have filed bug 335797 for this issue. Can you also check if the same problem occurs if you use JPQL?

Best Regards,
Chris
Re: CriteriaQuery<Boolean> fails with NoResultException but works when running generated SQL m [message #653791 is a reply to message #651506] Fri, 11 February 2011 08:59 Go to previous message
Matthew Denison is currently offline Matthew Denison
Messages: 6
Registered: January 2011
Junior Member
Hi Chris,

Yes same result for JPQL. Valid SQL that returns a result but the ReportQuery that is returned will contain no rows. If I query for the object where my boolean is true and then count the rows I can work around it that way.

Hope that helps,
Matthew
Previous Topic:DriverManager connect failed
Next Topic:Eager OneToOne relationship not using JOIN
Goto Forum:
  


Current Time: Tue Sep 23 02:19:35 GMT 2014

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

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