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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]