java - Efficient JPQL query for retrieving complex entities -

i'm quite rookie jpa/jpql, please excuse me if question not crystal clear.

i trying find efficient jqpl query in order records of complex object.

(ie. represented multiple tables, several one-to-many relationships - see simplified example below):

class complexobject {     private set< suboject1> so1 ...     .....     @onetomany(fetch = fetchtype.lazy)     public set< suboject1>... }  class subobject1 {     private set< suboject2> so2 ...     .....     @onetomany(fetch = fetchtype.lazy)     public set< suboject2>... } 

i using following jpql query :

select distinct co  complexobject co  left join fetch co.so1 so1  left join fetch so1.so2 

the query run on stateless session, in order de facto snapshot of current data in db, detached entity manager (hence usage of left join fetch).

unfortunately, i've encountered 2 problems :

1. since complex object contains multiple instances of so1, , each so1 instance contains multiple instances of so2, underlying translation sql queries generates specific select query per row of product of table joins - wasteful solution. there way reduce number of internal select queries? (this seems dreaded n+1 queries problem).

2. the jpql query returns complexobject instance per internal sql query on product of table joins - means multiple references complexobject instances. why happen on 'select distinct' query?

edit : clarify - jpa framework using hibernate, , db hypersql.

edit : (1) issue turned out related using p6spy logging framework, printed out results large db table. logging format led incorrect assumption many queries being executed.

while trying fine tune performance, using native queries did not appear have better performance using jpql queries. using native query resulted in object typed results, required post processing.

you can use view objects receive columns want:

stringbuilder sb = new stringbuilder(); sb.append(" select new ").append(objectvo.class.getname()).append("(co.someinfo1, co.someinfo2, so1.someinfo )"); sb.append(" complexobject co "); sb.append(" join co.suboject1s so1 "); sb.append(" left join so1.so2 so2 "); sb.append(" so1.id = :idso1 , so2 = :something");  query q = em.createquery(sb.tostring()); q.setparameter("idso1", idso1); q.setparameter("something", something);  list<objectvo> listresult = q.getresultlist(); 

the objectvo class:

public class objectvo {      private string info1;     private long info2;      private string info3;      public pedidomodel(string info1, long info2, string info3){         this.info1 = info1;         this.info2 = info2;         this.info3 = info3;     }  }