| JPQL where condition problem [message #631189] |
Wed, 06 October 2010 14:11  |
 No real name
Messages: 5
Registered: April 2010 |
Junior Member |
|
|
Hi all,
I have a problem with where condition in my query.
The situation:
There is a table A and a table B, the relation between these tables (A to B) is many-to-one. I want to fetch all A entities, whitch have a relation to B or a column of the A table has a specific value. So I wrote this query:
select a from A a
left join fetch a.b
....{other joins}
where ... and (a.b is not null or a.type in(1,2,3,4)) and ....
This query fetches only entities with relation to B, but not the entities without this relation and with type 1,2... When I remove the part "a.b is not null or", the query fetches entites with type 1,2,3... as expected. So there is a problem in or. I'm not very experienced in JPLQ, so maybe i'm doing something wrong.
I'm using Eclipselink 2.0.2, JDK 1.6.20, query tested with JPA query tool.
Thank you for any help.
Michael
|
|
|
| Re: JPQL where condition problem [message #631266 is a reply to message #631189] |
Wed, 06 October 2010 18:32  |
Chris Delahunt
Messages: 1389
Registered: July 2009 |
Senior Member |
|
|
This is a common problem, as using A.b in JPQL forces an inner join to be used on the tables. Even though you have defined an explicit outer join in the fetch clause, you are again defining a different A->B relationship in the where clause, and it must use the inner join due to JPQL rules. If you see the generated SQL, you will likely see two joins occuring; 1 for the fetching of the related B objects, and then an inner join associated to the conditions in the where clause.
What you will need to do is specify an explicit outer join, something like:
"select a from A a left join a.b b ... where ... and (b is not null or ...)"
This will give you the results you want, but will not fetch join the B relationship. You can either add another left join fetch for the a.b relationship, or use the EclipseLink fetch query hint to keep the JPQL query a bit cleaner:
query.setHint(QueryHints.LEFT_FETCH, "a.b");
Best Regards,
Chris
|
|
|
|
Powered by
FUDForum. Page generated in 0.03767 seconds
] 
Search
Help
Register
Login
Home
