Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » QueryHints.LEFT_FETCH not working
QueryHints.LEFT_FETCH not working [message #778458] Thu, 12 January 2012 16:34 Go to next message
Jonathan Jones is currently offline Jonathan JonesFriend
Messages: 4
Registered: January 2012
Junior Member
First of all apologies if this has been posted before or its in the wrong place. I have had a good trawl through these forums but cant seem to solve it.

INFO: EclipseLink, version: Eclipse Persistence Services - 2.3.0.v20110604-r9504
INFO: Running GlassFish Version: GlassFish Server Open Source Edition 3.1.1 (build 12)

Here is my scenario


@Entity
public class ObjectA{
/.......... other code ........./
private Integer subvalueid;
private String textvalue;

@OneToOne(optional=true,fetch=FetchType.EAGER)
@JoinColumn(name="subvalueid",unique=true,nullable=false,updatable=false,insertable=false)
private ObjectB subvalue;
}

@Entity
public class ObjectB{
/.......... other code ........./
private Integer id;
private String description;
}

Query query = entityManager.createQuery("SELECT A FROM ObjectA a WHERE a.textvalue = :val1 OR a.subvalue.description = :val2");
query.setHint(QueryHints.LEFT_FETCH, "a.subvalue");
query.setParameter("val1", "/..something ../");
query.setParameter("val2", "/..something ../");
query.getResultList();



My query produces
SELECT /*lots of fields*/ FROM ObjectA a, ObjectB b WHERE ((a.textvalue = ? OR (b.description) = ?) AND (b.ID = a.subvalueid))

b.ID = a.subvalueid means that only records where there is a match between the relationship are returned.

I want to try and enforce the query to produce a LEFT JOIN so that it includes records in table / ObjectA even if there is no entry in table / ObjectB

I have tried allsorts of QueryHints and JOIN FETCH on the query but am having no such look.

Any help is greatly appreciated.

Many thanks in advance

Jonathan Jones
Re: QueryHints.LEFT_FETCH not working [message #778642 is a reply to message #778458] Fri, 13 January 2012 16:08 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
The fetch join is working, the problem is that your query is using an inner join on the same a.subvalue relationship which is more restrictive than the left outer join. The query hint is independent of the expression, and will not override asspects of it.

Either remove the "OR a.subvalue.description = :val2" from the where clause, or define it so that it also uses an outer left join. ie
Query query = entityManager.createQuery(""SELECT A FROM ObjectA a outer join a.subvalue s where a.textvalue = :val1 OR s.description =: val2");
query.setHint(QueryHints.LEFT_FETCH, "a.subvalue");


Best Regards,
Chris
Re: QueryHints.LEFT_FETCH not working [message #778643 is a reply to message #778458] Fri, 13 January 2012 16:09 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
system posted duplicates

[Updated on: Fri, 13 January 2012 17:14]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778645 is a reply to message #778458] Fri, 13 January 2012 16:10 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:14]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778646 is a reply to message #778458] Fri, 13 January 2012 16:08 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:14]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778647 is a reply to message #778458] Fri, 13 January 2012 16:09 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:14]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778648 is a reply to message #778458] Fri, 13 January 2012 16:10 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:15]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778650 is a reply to message #778458] Fri, 13 January 2012 16:12 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:16]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778659 is a reply to message #778458] Fri, 13 January 2012 16:08 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:15]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778660 is a reply to message #778458] Fri, 13 January 2012 16:09 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:15]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778661 is a reply to message #778458] Fri, 13 January 2012 16:10 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:15]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778662 is a reply to message #778458] Fri, 13 January 2012 16:12 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:15]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778675 is a reply to message #778458] Fri, 13 January 2012 16:08 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:15]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778676 is a reply to message #778458] Fri, 13 January 2012 16:09 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:16]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778677 is a reply to message #778458] Fri, 13 January 2012 16:10 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
No Message Body

[Updated on: Fri, 13 January 2012 17:16]

Report message to a moderator

Re: QueryHints.LEFT_FETCH not working [message #778678 is a reply to message #778458] Fri, 13 January 2012 16:12 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
The fetch join is working, the problem is that your query is using an inner join on the same a.subvalue relationship which is more restrictive than the left outer join. The query hint is independent of the expression, and will not override asspects of it.

Either remove the "OR a.subvalue.description = :val2" from the where clause, or define it so that it also uses an outer left join. ie
Query query = entityManager.createQuery(""SELECT A FROM ObjectA a outer join a.subvalue s where a.textvalue = :val1 OR s.description =: val2");
query.setHint(QueryHints.LEFT_FETCH, "a.subvalue");

Best Regards,
Chris
Previous Topic:set schema name for stored procedures using JPA
Next Topic:java.lang.NoSuchMethodError: javax.persistence.EntityManager.getCriteriaBuilder()Ljavax/persistence/
Goto Forum:
  


Current Time: Sat Dec 20 12:20:20 GMT 2014

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

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