The function works but i get the following in the stdout.log:
firstResult/maxResults specified with collection fetch; applying in memory!

A SaleItem has media and media has a list of pictures (max 3 atm)

How can i get 50 SaleItems without getting the warning?
And if there is no other solution...will the pictures be cached if i use eg a ehcache ?

//Robert

Hibernate version:
3.2.6
Mapping documents:
SaleItem has is
<many-to-one name="media" class="Media" column="media_id" cascade="save-update,persist"/>

The only collection Media has:
<list name="pictures" cascade="all" fetch="join" batch-size="50" >
<key column="parent_id" not-null="true"/>
<list-index column="idx"/>
<one-to-many class="Picture"/>
</list>

Code between sessionFactory.openSession() and session.close():
Query q = session.createQuery("from SaleItem si inner join fetch si.price inner join fetch si.oldPrice inner join fetch si.seller inner join fetch si.category inner join fetch si.location inner join fetch si.location.city inner join fetch si.location.city.country inner join fetch si.media left outer join fetch si.media.pictures WHERE "+prepareQuery(search));
q.setFirstResult(1);
q.setMaxResults(50);
q.setReadOnly(true);
List<SaleItem> li = (List<SaleItem>) q.list();
Name and version of the database you are using:
MySQL 5.0.44
The generated SQL (show_sql=true):
Hibernate:
select saleitem0_.id as id8_0_,
price1_.id as id1_1_,
price2_.id as id1_2_,
seller3_.id as id9_3_,
category4_.id as id5_4_,
itemlocati5_.id as id7_5_,
city7_.id as id4_6_,
country10_.id as id0_7_,
media11_.id as id3_8_,
pictures13_.id as id2_9_,
saleitem0_.category_id as category2_8_0_,
saleitem0_.dateAdded as dateAdded8_0_,
saleitem0_.itemName as itemName8_0_,
saleitem0_.seller_id as seller5_8_0_,
saleitem0_.itemDescription as itemDesc6_8_0_,
saleitem0_.price_id as price7_8_0_,
saleitem0_.oldPrice_id as oldPrice8_8_0_,
saleitem0_.location_id as location9_8_0_,
saleitem0_.password as password8_0_,
saleitem0_.type as type8_0_,
saleitem0_.media_id as media12_8_0_,
saleitem0_.active as active8_0_,
price1_.currency as currency1_1_,
price1_.amount as amount1_1_,
price2_.currency as currency1_2_,
price2_.amount as amount1_2_,
seller3_.company as company9_3_,
seller3_.name as name9_3_,
seller3_.email as email9_3_,
seller3_.phone as phone9_3_,
seller3_.hidephone as hidephone9_3_,
category4_.group_id as group2_5_4_,
category4_.name as name5_4_,
itemlocati5_.city_id as city2_7_5_,
city7_.country_id as country2_4_6_,
city7_.name as name4_6_,
country10_.name as name0_7_,
pictures13_.idx as idx2_9_,
pictures13_.parent_id as parent3_2_9_,
pictures13_.filename as filename2_9_,
pictures13_.parent_id as parent3_0__,
pictures13_.id as id0__,
pictures13_.idx as idx0__ from saleitems saleitem0_
inner join prices price1_ on saleitem0_.price_id=price1_.id
inner join prices price2_ on saleitem0_.oldPrice_id=price2_.id
inner join sellers seller3_ on saleitem0_.seller_id=seller3_.id
inner join categories category4_ on saleitem0_.category_id=category4_.id inner join item_locations itemlocati5_ on saleitem0_.location_id=itemlocati5_.id
inner join cities city7_ on itemlocati5_.city_id=city7_.id
inner join countries country10_ on city7_.country_id=country10_.id
inner join media media11_ on saleitem0_.media_id=media11_.id
left outer join pictures pictures13_ on media11_.id=pictures13_.parent_id where saleitem0_.type=1 and (saleitem0_.itemName like '%%') order by dateAdded DESC

anyone? at least tell me if im doing this completly wrong

//Robert

Bringing this back from a few months ago........

Try reading this article on pagination and using the @BatchSize annotation:

http://java.dzone.com/articles/hibernat ... page=0%2C0

Now, I'm getting the same warning, but this is happening when I save an object (using the merge function), which means hibernate is doing this internally! I have no option to modify the query... because there is no query. :-)

This is with two objects, a User, and their Authorities. There is a Many-to-Many relationship between the two, and the Authorities that a User has gets JOIN FETCH(ed) when the User is initially grabbed from the database.

The first SQL statement that Hibernate generates on an update of a detached User object looks something like this:

(QueryTranslatorImpl.java:351) - firstResult/maxResults specified with collection fetch; applying in memory!

(SQLStatementLogger.java:111) - select distinct user0_.id as id1_0_, authority2_.authority as authority0_1_, authoritie1_.User_id as User1_0__, authoritie1_.authorities_authority as authorit2_0__ from User user0_ inner join User_Authority authoritie1_ on user0_.id=authoritie1_.User_id inner join Authority authority2_ on authoritie1_.authorities_authority=authority2_.authority where user0_.id=?

Versions: Hibernate 3.3.1, Annotations 3.4.0, EntityManager 3.4.0

Suggestions/Comments?

Well... it looks like this problem solved itself. After changing all my existing queries that were causing the warning to deal with this issue, the saving no longer causes the warning either.

Yet the sql for the save appears to be exactly the same as before. *shrugs*

(SQLStatementLogger.java:111) - select distinct user0_.id as id1_0_, authority2_.authority as authority0_1_, authoritie1_.User_id as User1_0__, authoritie1_.authorities_authority as authorit2_0__ from User user0_ inner join User_Authority authoritie1_ on user0_.id=authoritie1_.User_id inner join Authority authority2_ on authoritie1_.authorities_authority=authority2_.authority where user0_.id=?

FYI my regular queries were using getSingleResult() at the end. I changed that to getResultList(), and then did a ((nameoflist)).get(0) to get the single object I was after. In all cases where this warning was given, I only expect a single result anyway, so this worked out very well.