HibernateTemplate's setFetchSize() and setMaxResults()

[hezjing]

Hi

I'm using Spring 2.5.3, Hibernate and MySQL, and encounter out of memory exception when trying to load 100,000 objects (or records) using HibernateTemplate.loadAll().

I should really do it in the pageable fashion, but how?

It sound to me that HibernateTemplate.setFetchSize() and setMaxResults() could resolve my problem, but what is the different between setFetchSize() and setMaxResults()?

Should I set the fetch size to the total of record per page? and set the max results to the total of records (e.g. 100,000)?

[hezjing]

Hi

I tried to set the fetch size to 20, but it still return all the records

HibernateTemplate template = getHibernateTemplate();
template.setFetchSize(20);
// the users list is more than 20
List<Users> users = (List<User>) template.loadAll(User.class);

Hmmm ... what is the fetch size does?
Does it has any effect to loadAll()?

[hezjing]

Hi

I think the best way to retrieve a pageable records is the following,

    /**
     *
     * @param pageSize the total record in one page
     * @param pageNumber the page number starts from 0
     */
    public List<User> getAllByPage(final int pageSize, final int pageNumber) {
        HibernateTemplate template = getHibernateTemplate();
        return (List<User>) template.executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery("from User");
                query.setMaxResults(pageSize);
                query.setFirstResult(pageSize * pageNumber);
                return query.list();
            }
        });
    }

This is also described in Hibernate reference document.

[gregd]

setFetchSize is an optimization query hint for the database driver. If the driver doesn’t implement this functionality you have no value of calling it.
If it does, it just improve the communication between the JDBC client
and the database. setMaxResults is what you need in this case.

[stephaneeybert]

http://benjisimon.blogspot.com/2007/...fetchsize.html

Has any one some additional comments on that ?