Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:Hibernate 3.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:Postgres and Oracle

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I have read many comments on this subject and I have done some of the things that we are told to do but I have now reached an issue trying to paginate using setMaxResults and duplicate objects.

It is well known that if you left join fetch and you have many to many, one to many etc. That the result set that come back can have duplicate objects. The hibernate team has said that we should use the following:

Set set = new LinkedHashSet();
set.addAll(query.list());
return set;

This does work and my duplications are gone

but I need to be able to paginate using something like this:

Query q = dbSession.createQuery(queryStringBuff.toString());
q.setMaxResults(50);
q.setFirstResult(50 * startIndex);
Set set= new LinkedHashSet();
set.addAll(q.list());

This will first limit the query to 50 records, then next the duplicates are taken care of. The page is expecting 50 records but because the duplicates are taken care of after limiting the records, we can get anywhere from 1 to 50 records back because the duplicates are taken care of after the limit.

Is there any work around here that I can use to paginate queries where I am using the left join fetch and there is many to many or one to many etc. ? Basically any query where I get duplicate objects based on a query with left join fetch, pagination does not seem possible? is this correct. Do I need to get rid of left join fetching and start doing theta-style joins? Any comments, suggestions will be greatly appreciated. Thank you and have a good day.

I am not sure what exactly will happen with this option since I am also newbie.

Check out documentation for Criteria.setResultTransformer( CriteriaSpecification.DISTINCT_ROOT_ENTITY );

I appreciate your response but I am trying to avoid having to move away from HQL. I am hoping that I do not have to rewrite a lot of code just because HQL does not handle pagination when dealing with duplicate objects resulting from a left join fetch query. The documentation is such:

10.4.1.5. Pagination

If you need to specify bounds upon your result set (the maximum number of rows you want to retrieve and / or the first row you want to retrieve) you should use methods of the Query interface:

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

This works sweetly as long as you do not have queries that bring back duplicate objects. The problem is that I have many dynamicly built queries that often time bring back duplicate objects. The "set" thingy does work but now I have to paginate my calls because hibernate is running out of memory. I will look into Criteria stuff but I am hoping that the hibernate team has a solution here where I can keep my existing HQL query generator.

For reference: many ideas about the problem in
viewtopic.php?f=9&t=941669&start=15