Re: [eclipselink-users] loading collection problem

[Leon Derks <leon.derks@xxxxxxxxxx>]

Hi Tom,

Yes indeed, with the outer join syntax I get the correct result.

What is the workaround for this?

Leon


Tom Ware wrote:
> Hi Leon,
>
>   If you run slightly changed SQL do you get the correct results. 
> (change t0.PRODUCT_TYPE = ? to use the outer join syntax - 
> t0.PRODUCT_TYPE (+) = 'Gear')
>
>   If that change gives you better results, it likely points to a 
> result that I can give you a workaround for.
>
>   The other thing you can try is to make your @JoinFetch on 
> IndustrialFamily to use an OuterJoin
>
> @JoinFetch(JoinFetchType.OUTER)
>
> -Tom
>
> Leon Derks wrote:
> > Hi Tom,
> >
> > Named query: select l from Lamp l left join fetch l.gears where l.id 
> > = :lampId
> >
> > This is the SQL:
> > SELECT t1.ID, t1.PRODUCT_TYPE, t1.CODE, t1.SHORT_CODE, t1.NAME, 
> > t1.family_id, t0.ID, t0.PRODUCT_TYPE, t0.CODE, t0.SHORT_CODE, 
> > t0.NAME, t0.family_id, t0.GEAR_TYPE, t2.ID, t2.CODE, t2.NAME, 
> > t2.PROD_CAT FROM PLI_LAMP_GEAR t3, PLI.PLI_INDUSTRIAL_FAMILIES t2, 
> > PLI.PLI_CATALOG_PRODUCTS t1, PLI.PLI_CATALOG_PRODUCTS t0 WHERE 
> > (((t1.ID = ?) AND (t1.PRODUCT_TYPE = ?)) AND ((((t3.lamp_id (+) = 
> > t1.ID) AND (t0.ID (+) = t3.gear_id)) AND (t0.PRODUCT_TYPE = ?)) AND 
> > (t2.ID = t1.family_id)))
> >    bind => [75159, Lamp, Gear]
> >
> > A Product has a @JoinFetch to an IndustrialFamily
> >
> > When I remove the "left join fetch l.gears" from the named query, a 
> > Lamp with its Family is returned
> >
> > Database: Oracle 10G XE
> > jdbc brand version: 10.2.0.4/ojdbc14.jar
> >
> > Leon
> >
> > Tom Ware wrote:
> > > Hi Leon,
> > >
> > >   Can you post the generated SQL?
> > >
> > >   Also, can you post your database and jdbc driver brand and version?
> > >
> > > Thanks,
> > > Tom
> > >
> > > Leon Derks wrote:
> > > > Hi Tom,
> > > >
> > > > When I run the SQL against my database it also doesn't return 
> > > > something. So that is correct.
> > > >
> > > > I think I have to change my named query, but don't know how.
> > > >
> > > > I want to load a Lamp object with its collection of gears. If it 
> > > > has no gears, then the collection has to be empty.
> > > > The query I run now, return no Lamp at all.
> > > >
> > > > Leon
> > > >
> > > >
> > > >
> > > > Tom Ware wrote:
> > > > > Hi Leon,
> > > > >
> > > > >   What SQL is your query producing?  If you run the SQL against 
> > > > > your database, does it return a result?
> > > > >
> > > > >   To see the SQL, you can increase the logging level.  I suggest 
> > > > > using the maximum logging level when debugging.  To do that, 
> > > > > specify the persistence unit property in your persistence.xml
> > > > >
> > > > > <property name="eclipselink.logging.level" value="FINEST"/>
> > > > >
> > > > > -Tom
> > > > >
> > > > > Leon Derks wrote:
> > > > > > Hello
> > > > > >
> > > > > > Thanks for the answer on my previous question. That works well!
> > > > > >
> > > > > > But now I have another problem.
> > > > > > I want to load an object with a collection that may be empty.
> > > > > >
> > > > > > For example my Lamp object has a @ManyToMany relation with Gear.
> > > > > >
> > > > > > public class Lamp extends Product {
> > > > > >
> > > > > >    @ManyToMany
> > > > > >    @JoinTable(name="PLI_LAMP_GEAR", joinColumns = 
> > > > > > @JoinColumn(name="lamp_id", referencedColumnName = "ID"), 
> > > > > > inverseJoinColumns = @JoinColumn(name="gear_id", 
> > > > > > referencedColumnName = "ID"))
> > > > > >    private List<Gear> gears = new ArrayList<Gear>()
> > > > > >
> > > > > > }
> > > > > >
> > > > > > public class Gear extends Product {
> > > > > > }
> > > > > >
> > > > > > In my situation, the Lamp has no Gears, so the join table is 
> > > > > > empty for this lamp.
> > > > > >
> > > > > > But when I try to load the Lamp it returns nothing. I want it to 
> > > > > > return a Lamp (with name, description etc) and an empty 
> > > > > > collection of Gears.
> > > > > >
> > > > > > This is my named query: "select l from Lamp l left join fetch 
> > > > > > l.gears where l.id = :lampId"
> > > > > >
> > > > > > I tried several queries, but always get the message: 
> > > > > > "javax.persistence.NoResultException: getSingleResult() did not 
> > > > > > retrieve any entities."
> > > > > >
> > > > > > Am I doing something wrong?
> > > > > >
> > > > > > greetings,
> > > > > > Leon
> > > > > >
> > > > > > _______________________________________________
> > > > > > eclipselink-users mailing list
> > > > > > eclipselink-users@xxxxxxxxxxx
> > > > > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> > > > > _______________________________________________
> > > > > eclipselink-users mailing list
> > > > > eclipselink-users@xxxxxxxxxxx
> > > > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> > > >
> > > > _______________________________________________
> > > > eclipselink-users mailing list
> > > > eclipselink-users@xxxxxxxxxxx
> > > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> > > _______________________________________________
> > > eclipselink-users mailing list
> > > eclipselink-users@xxxxxxxxxxx
> > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> >
> > _______________________________________________
> > eclipselink-users mailing list
> > eclipselink-users@xxxxxxxxxxx
> > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users