Lazy Criteria Query Result Sets

Top Announcement: 10-August-2008 EmForge-0.25 with Windows Installer released! See News for details.

During our working with Data Table Scroller from RichFaces we realized one not very good thing: even if we are displaying only 10 elements from the whole records, Hibernate (we used Hibernate for accessing database loaded whole items into memory. You know, sometimes, it may be really a lot of records in one database!

code we used is:

DetachedCriteria criteria = DetachedCriteria.forClass(someDO.class).add(Expression.eq("statusCode", statusCode));
List<someDO> resultList = getHibernateTemplate().findByCriteria(criteria);
return resultList;

and later this resultList was used for displaying in the table.

As soon, as we started to have a lot of items in result of criteria - our application slows down drammatically.

Easiest solution was found was to implement own collection, based on criteria. Its main features are:

• the load on-demand of "pages" of records in a way the desired record can be loaded with a maximum fetch of page-size records;
• the size() method does not load all the records to get the result set size;
• it is compatible with the collections framework so the code changes are minor ones.

Note

Solution provided here will work correctly only in case, if created collection is used inside one http-request - or, better say, if lifetime of this collection is not longer then lifetime of hibernate session, used in this collection!.

package ru.emdev.EmForge;

import java.io.ByteArrayOutputStream;
import java.io.ObjectOutputStream;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;
import java.util.NoSuchElementException;

import org.hibernate.Criteria;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.HibernateTemplate;

public class LazySearchResult<E> implements List<E> {

    private E[][] dataPages;
    private int pageSize;
    private DetachedCriteria criteria;
    private HibernateTemplate hibernateTemplate;
    private int size = -1;

    public class LazySearchResultIterator implements ListIterator<E> {

        LazySearchResult<E> result;
        int nextIndex;

        private LazySearchResultIterator() {
        }

        private LazySearchResultIterator(LazySearchResult<E> result) {
            this.result = result;
            nextIndex = 0;
        }

        private LazySearchResultIterator(LazySearchResult<E> result, int nextIndex) {
            this.result = result;
            this.nextIndex = nextIndex;
        }

        public boolean hasNext() {
            return nextIndex < result.size();
        }

        public E next() {
            if (hasNext()) {
                return result.get(nextIndex++);
            } else {
                throw new NoSuchElementException();
            }
        }

        public boolean hasPrevious() {
            return nextIndex > 0;
        }

        public int nextIndex() {
            return nextIndex;
        }

        public E previous() {
            if (hasPrevious()) {
                return result.get(--nextIndex);
            } else {
                throw new NoSuchElementException();
            }
        }

        public int previousIndex() {
            return nextIndex - 1;
        }

        public void set(Object arg0) {
            throw new UnsupportedOperationException();
        }

        public void remove() {
            throw new UnsupportedOperationException();
        }

        public void add(Object arg0) {
            throw new UnsupportedOperationException();
        }

    }

    public LazySearchResult(HibernateTemplate hibernateTemplate, DetachedCriteria criteria, int pageSize) {
        super();
        this.hibernateTemplate = hibernateTemplate;
        this.criteria = criteria;
        setPageSize(pageSize); // must be called in constructor
    }

    public LazySearchResult(HibernateTemplate hibernateTemplate, DetachedCriteria criteria) {
        super();
        this.hibernateTemplate = hibernateTemplate;
        this.criteria = criteria;
        setPageSize(20); // must be called in constructor, 20 is just a
                            // default value
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
        clear();
    }

    @SuppressWarnings("unchecked")
    private E[] loadPage(int pageNum) {
        final Criteria sessionCriteria = criteria.getExecutableCriteria(hibernateTemplate.getSessionFactory().getCurrentSession());
        
        sessionCriteria.setFirstResult(pageNum * pageSize);
        sessionCriteria.setMaxResults(pageSize);

        final E[] page = (E[]) sessionCriteria.list().toArray();

        dataPages[pageNum] = page;

        return page;
    }

    private E[] getPage(int pageNum) {
        if (dataPages[pageNum] == null) {
            return loadPage(pageNum);
        } else {
            return dataPages[pageNum];
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.Collection#clear()
     */
    @SuppressWarnings("unchecked")
    public void clear() {
        dataPages = (E[][])new Object[(size() / pageSize) + 1][];
        for (int i = 0; i < dataPages.length; i++) {
            dataPages[i] = null;
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.Collection#isEmpty()
     */
    public boolean isEmpty() {
        return size() == 0;
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.Collection#iterator()
     */
    public Iterator<E> iterator() {
        return listIterator();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.Collection#size()
     */
    public int size() {
        if (size == -1) {
            final ByteArrayOutputStream bos = new ByteArrayOutputStream(); 
            final DetachedCriteria countCriteria;
            try {
                new ObjectOutputStream(bos).writeObject(criteria);
                countCriteria = (DetachedCriteria)new java.io.ObjectInputStream(new java.io.ByteArrayInputStream(bos.toByteArray())).readObject();
                countCriteria.setProjection(Projections.rowCount());
                
                size = (Integer) hibernateTemplate.findByCriteria(countCriteria).get(0);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return size;
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#get(int)
     */
    public E get(int index) {
        if (index >= size() || index < 0) {
            throw new NoSuchElementException();
        }

        final int page = index / pageSize;
        final int pageIndex = index % pageSize;
        return getPage(page)[pageIndex];
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#listIterator()
     */
    public ListIterator<E> listIterator() {
        return new LazySearchResultIterator(this);
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#listIterator(int)
     */
    public ListIterator<E> listIterator(int index) {
        return new LazySearchResultIterator(this, index);
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#subList(int, int)
     */
    public List<E> subList(int fromIndex, int toIndex) {
        final List<E> list = new java.util.ArrayList<E>((toIndex - fromIndex) + 1);
        for (int i = fromIndex; i <= toIndex; i++) {
            list.add(get(i));
        }
        return list;
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#add(java.lang.Object)
     */
    public boolean add(E arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#add(int, java.lang.Object)
     */
    public void add(int arg0, E arg1) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#addAll(java.util.Collection)
     */
    public boolean addAll(Collection<? extends E> arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#addAll(int, java.util.Collection)
     */
    public boolean addAll(int arg0, Collection<? extends E> arg1) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#contains(java.lang.Object)
     */
    public boolean contains(Object arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#containsAll(java.util.Collection)
     */
    public boolean containsAll(Collection<?> arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#indexOf(java.lang.Object)
     */
    public int indexOf(Object arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#lastIndexOf(java.lang.Object)
     */
    public int lastIndexOf(Object arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#remove(java.lang.Object)
     */
    public boolean remove(Object arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#remove(int)
     */
    public E remove(int arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#removeAll(java.util.Collection)
     */
    public boolean removeAll(Collection<?> arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#retainAll(java.util.Collection)
     */
    public boolean retainAll(Collection<?> arg0) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#set(int, java.lang.Object)
     */
    public E set(int arg0, E arg1) {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#toArray()
     */
    public Object[] toArray() {
        throw new UnsupportedOperationException();
    }

    /*
     * (non-Javadoc)
     * 
     * @see java.util.List#toArray(T[])
     */
    public <T> T[] toArray(T[] arg0) {
        throw new UnsupportedOperationException();
    }

}

A tipical code using the LazySearchResult:

DetachedCriteria criteria = DetachedCriteria.forClass(someDO.class).add(Expression.eq("statusCode", statusCode));
List<someDO> resultList = new LazySearchResult<someDO>(getHibernateTemplate(),criteria);
return resultList;