Hibernate version: 3.2.3
Mapping documents:

Code between sessionFactory.openSession() and session.close():
I use spring to manage all these. And the environment is JUnit test(Spring provided AbstractDependencyInjectionSpringContextTests which manages all transaction and session. Just want to limit eager-fetched collection size. And have tried 3 approaches.
1.
2.
3.

Full stack trace of any exception that occurs:
1.
get this worning:<firstResult/maxResults specified with collection fetch; applying in memory!>
And it fetch full collection(in sql, no limit key word at the end)

2.
No error but two separate sql.Plus if I want to iterate through or even get size of the collection, it will issue another sql call to fetch all Comments(to fetch all element for this collection since current collection has setMaxResults).

3.
It generates an inner join!?! And again, if I try to iterate the collection, it will issue a new sql call for all Comments.
If I call this method more than once,it even throw: org.hibernate.QueryException: duplicate alias: comment.

Name and version of the database you are using:
MySQL5
The generated SQL (show_sql=true):
1.
select album0_.album_id as album1_1_0_, comments1_.comment_id as comment1_3_1_, album0_.album_name as album3_1_0_, comments1_.content as content3_1_, comments1_.user_id as user6_3_1_ from album album0_ left outer join comment comments1_ on album0_.album_id=comments1_.album_id where album0_.album_id=? order by comments1_.created_date desc

2.
select album0_.album_id as album1_1_0_, album0_.album_name as album3_1_0_ from album album0_ where album0_.album_id=?
select comment0_.comment_id as comment1_3_ from comment comment0_ where comment0_.album_id = ? order by comment0_.created_date limit ?

3.
select this_.album_id as album1_1_1_, this_.album_name as album3_1_1_, comment1_.comment_id as comment1_3_0_, comment1_.created_date as created4_3_0_, comment1_.content as content3_0_, comment1_.user_id as user6_3_0_, comment1_.album_id as album7_3_0_, from album this_ inner join comment comment1_ on this_.album_id=comment1_.album_id where this_.album_id = ? order by comment1_.created_date desc limit ?

Question
Is this doable? My idea is to get an Album object with eager fetched collection Comment which will be limited to a given size.In book Hibernate in action, I remember collection always use proxy, no matter what you set. So if I return the whole Album object with incomplete collection, to presentation layer. And try to touch the collection, will it always issue another sql? But there is no session associated with it at that time. Any one know how to deal with this besides using two separate fetch(one for Album,one for Comment with setMaxResult). Thank you.

0 reply, it must be my bad :(
But I figure out a way to achieve this with only one database call. Again correct me please if I do something stupid.


The resizing is actually taken place in memory other than in database. So if collection size is huge, better use two separate database roundtrip. I managed to use a custom collection loader to restrict collection size. But that will lose pagination ability.
Side effect of this approach is the collection is no longer a proxy. If album gets reattached to session, not sure if the collection update will get picked up. I will test this later. Or maybe someone can explain a bit more on this.