CriteriaEnumeration

[Thoralf Rickert <th...@cadooz.de>]

Hi!

I'm not sure, if this was discussed before, but I want to send you this
"extension". 

Currently I'm working on somekind of "backup-procedure" that reads some
data from a database and creates a complex XML file (for example: stores
with addresses and products). To do this, I have to get all "stores"
from the database. If there are a lot of stores and I make just a
doSelect(new Criteria()) I have (of course) trouble with the memory
(number of stores > 100000).

I could implement a special algorithm, but I decided to implement a
mechanism that takes a Criteria and set offset/limit internally in a
loop. In this way I can get 100 stores with one SELECT and do my work.
If the "pool" is empty, I can read the next 100 stores.

I've enclosed this generic algorithm in a class called
CriteriaEnumeration:

---------------
public class CriteriaEnumeration<E> implements java.util.Enumeration {
  private static final Log log =
MyLog.getLogger(CriteriaEnumeration.class);
  private final java.util.Vector<E> fifo = new java.util.Vector<E>();
  
  private Criteria criteria = null;
  private CriteriaPopulator<E> populator = null;
  private Connection connection = null;
  
  private int offset = 0;
  private int limit = 100;
  private boolean eot = false;
  private boolean useTransaction = false;

  /**
   * Sets the criteria for the Enumeration
   */
  public void setCriteria(Criteria criteria) {
    this.criteria = criteria;
  }
  
  /**
   * Sets the populator that calls the database and creates the
   * Torque BaseObject
   */
  public void setPopulator(CriteriaPopulator<E> populator) {
    this.populator = populator;
  }
  
  /**
   * If true, the Enumeration uses a Transaction Connection
   * internally.
   */
  public void setTransaction(boolean useTransaction) {
    this.useTransaction = useTransaction;
  }
  
  /**
   * Sets the size of the Buffer.
   */
  public void setLimit(int limit) {
    this.limit = limit;
  }
  
  /**
   * @see java.util.Enumeration#hasMoreElements()
   */
  public boolean hasMoreElements() {
    if (!fifo.isEmpty()) return true;
    if (eot) return false;
    
    criteria.setLimit(limit);
    criteria.setOffset(offset);
    offset+=limit;
    
    if (useTransaction && connection == null) {
      try {
        connection = Transaction.begin();
      } catch (Exception exception) {
        log.error("Cannot use transactions.",exception);
        connection = null;
      }
    }
    
    List<E> elements = populator.getRows(connection,criteria);
    if (Utilities.isEmpty(elements)) {
      eot = true;
      if (connection != null) {
        try {
          Transaction.commit(connection);
        } catch (Exception exception) {
          log.error(exception);
        }
      }
    }
    if (eot) return false;
    
    fifo.addAll(elements);
    
    return true;
  }
  
  /**
   * @see java.util.Enumeration#nextElement()
   */
  public E nextElement() {
    if (!hasMoreElements()) return null;
    return fifo.remove(0);
  }
}
---------------

Note: This implementation uses a Transaction class that wraps the Torque
Transaction class

Also you need to implement a class that implements the interface
CriteriaPopulator<E>

--------------
public interface CriteriaPopulator<E> {
  public List<E> getRows(Connection connection, Criteria criteria);
}
-------------

With this code you can iterate through the result without reading
everything at once. There are of course some problems. First the
database should accept offset and limit settings of the Criteria (like
MySQL). And second the data that you read should not change during the
enumeration. Of course there is a transaction-safe implementation, but
I'm not sure if it works in every environment. And of course this is
nothing for performance-junkies. ;-)


Here is an example with Stores

public class StorePeer extends BaseStorePeer {
  public static Enumeration<Store> getStores() {
    Criteria criteria = new Criteria();
    criteria.addAscendingOrderByColumn(ID);
    
    CriteriaEnumeration<Store> enumeration = new
CriteriaEnumeration<Store>();
    enumeration.setCriteria(criteria);
    enumeration.setPopulator(new StorePopulator());
    return enumeration;
  }

  static class StorePopulator implements CriteriaPopulator<Store> {
    public List<Store> getRows(Connection connection, Criteria criteria)
{
      try {
        log.debug(createQueryString(criteria));
        return doSelectJoinAllExceptLogin(criteria,connection);
      } catch (Exception exception) {
        log.error(exception);
        return null;
      }
    }
  }
}


bye
Thoralf


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: CriteriaEnumeration

[Thomas Vandahl <tv...@apache.org>]

Thoralf Rickert wrote:
> Hi!
> 
> I'm not sure, if this was discussed before, but I want to send you this
> "extension". 
> 
> Currently I'm working on somekind of "backup-procedure" that reads some
> data from a database and creates a complex XML file (for example: stores
> with addresses and products). To do this, I have to get all "stores"
> from the database. If there are a lot of stores and I make just a
> doSelect(new Criteria()) I have (of course) trouble with the memory
> (number of stores > 100000).
> 
> I could implement a special algorithm, but I decided to implement a
> mechanism that takes a Criteria and set offset/limit internally in a
> loop. In this way I can get 100 stores with one SELECT and do my work.
> If the "pool" is empty, I can read the next 100 stores.
> 

This is more or less what LargeSelect does. I used to use it for such
things - reading data one page at a time. It provides the additional
advantage of a "read-ahead-cache", means it continues reading your data
in the background while you can process the first page of records.

If you are interested, I created a small LargeSelectHelper class which
allows you to continuously read one page at a time and let LargeSelect
do the "chunking".

Bye, Thomas.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org