Hello,

In our existing application there are entities(master) with many (about 10) collections of subentities(details) of different types, all persisted on table-per-class basis.
A typical query returns tens of thousands of entities so pagination is used. Whenever we retreive the entities, we are always interested in retreiving all the subentities contained in them.
Currently, we are using Sybase JDBC. To reach the required response time and limit the number of database calls we:
1. query entity table according to a criteria and store id's(primary keys) along with IDENTITY column to a temp table
2. join the temp table with the entity table and order by primary key
3. for every collection, do a join of temp table with sub entity tables and order by primary key.
4. For pagination, we use the IDENTITY column in the temp table to restrict the query result stored in the table table.
5. So if there N different subentities in an entity, for each page, we make 1 (to populate temp table) + 1 (entity resultset) + N (for subentities resultsets) calls to the Sybase server. We co-relate the N+1 result sets ourselves in the application process.

We are satisfied with the performance. Now we are interested in using Hibernate for flexibility. What is a good solution to this problem using Hibernate?

Thank you