RimuDB Logo
Database Access Framework for Java

Iterative Query

The GenericQuery class works for small result sets, but if a large number of rows is returned, the application may run out of memory. An alternative method is to use IterativeQuery to process queries interatively. This class allows the caller to retrieve the records from the server one at a time, or in chunks.

IterativeQuery is normally constructed as follows:

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select value from {OrderTransaction} where name = ?";
IterativeQuery query = new IterativeQuery(db, sql);

Like GenericQuery, there are different methods used depending upon the type of result the SQL statement is expected to return.

Note that the Iterator holds an open connection to the database. Make sure that the iterator is closed under all conditions. In the following examples, a try/finally clause is used to ensure the iterator.close() method is called each time.

IterativeQuery.createObjectIterator()

Creates an iterator that will return multiple rows, each row containing a single column.

ObjectIterator createObjectIterator(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
DataObjectListBinder binder = new DataObjectListBinder(OrderTransactionDO.class);
String sql = "select orderNr from {OrderTransaction} where name = ?";
IterativeQuery query = new IterativeQuery(db, sql);

ObjectIterator iterator = query.createObjectIterator("john");
try {
   while (true) {
      Integer orderNr = (Integer)iterator.next();
      if (orderNr == null) {
         break;
      }
      System.out.println("orderNr="+orderNr);
   }
} finally {
   iterator.close();
}

IterativeQuery.createDataObjectIterator()

Creates an iterator that will return multiple rows, each row containing a DataObject.

DataObjectIterator createDataObjectIterator(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select * from {OrderTransaction} ";
IterativeQuery query = new IterativeQuery(db, OrderTransactionDO.class, sql);

DataObjectIterator iterator = query.createDataObjectIterator();
try {
   while (true) {
      OrderTransactionDO orderTransaction = (OrderTransactionDO)iterator.next();
      if (orderTransaction == null) {
         break;
      }
      System.out.println("orderNr="+orderTransaction.getOrderNr()+" name="+orderTransaction.getName());
   }
} finally {
   iterator.close();
}

IterativeQuery.createDataObjectArrayIterator()

Creates an iterator that will return multiple rows, each row containing an array of DataObjects.

DataObjectArrayIterator createDataObjectArrayIterator(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select * from {OrderHeader} A join {OrderLine} B on A.order_nr = B.order_nr";

Class[] clazzes = new Class[2];
clazzes[0] = OrderHeaderDO.class;
clazzes[1] = OrderLineDO.class;

IterativeQuery query = new IterativeQuery(db, clazzes, sql);

DataObjectArrayIterator iterator = query.createDataObjectArrayIterator();
try {
   while (true) {
      DataObject[] objs = iterator.next();
      if (objs == null) {
         break;
      }
      OrderHeaderDO orderHeader = (OrderHeaderDO)objs[0];
      OrderLineDO orderLine = (OrderLineDO)objs[1];

      System.out.println("OrderNr="+orderHeader.getOrderNr()+" item="+orderLine.getItemNumber());
   }
} finally {
   iterator.close();
}

IterativeQuery.createObjectIteratorWithBinder()

Creates an iterator that will return multiple rows, each row containing a bean produced by the binder. Use this method if the SQL statement will return multiple rows each containing a selection of the available columns or scalar results. This is the most flexible form of the methods, allowing any values that can be returned by a select statement to be returned. It does however, require that a bean class is created for handling the selection. In most cases the DefaultIterativeResultSetBinder can be used to bind the result set with the beans. If the DefaultIterativeResultSetBinder is used, then the bean class must implement IResultSetBean.

ObjectIterator createObjectIteratorWithBinder(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select \"name\", count(*) from {OrderTransaction} group by \"name\"";
DefaultIterativeResultSetBinder binder = new DefaultIterativeResultSetBinder(OrderTransactionSummaryBean.class);
IterativeQuery query = new IterativeQuery(db, binder, sql);

ObjectIterator iterator = query.createObjectIteratorWithBinder();
try {
   while (true) {
      OrderTransactionSummaryBean summaryBean = (OrderTransactionSummaryBean)iterator.next();
      if (summaryBean == null) {
         break;
      }
      System.out.println("Name="+summaryBean.getName()+" count="+summaryBean.getCount());
   }
} finally {
   iterator.close();
}


public class OrderTransactionSummaryBean implements IResultSetBean {
private String name = null;
   private int count = 0;

   public void bindResultSet(ResultSet rs) throws RimuDBException {
      try {
         setName(rs.getString(1));
         setCount(rs.getInt(2));
      } catch (SQLException e) {
         throw new RimuDBException(e);
      }
   }

   public int getCount() {
      return count;
   }

   public void setCount(int count) {
      this.count = count;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }
}

Improving Performance - Reading Multiple Rows at Once

The examples above demonstrate how the IterativeQuery can be used to iterate through the results one row at a time. Using an IterativeQuery allows the program to iterate through enormous result sets without having to have them all in memory at once. However, processing one row at a time is not as efficient as it could be since the program has to request the next row from the database each time. Usually the database is on a different server, so there is some network performance overhead in issuing this many requests. By setting the FetchSize and requesting more rows from the iterator at once, the framework will make fewer requests to the database and performance can be dramatically improved.

The following example shows how the FetchSize is set to 100 and up to 100 rows are retrieved from the iterator at once.


String sql = "SELECT * FROM {OrderHeaderDO} H JOIN {OrderLineDO} L ON H.ORDER_ID = L.ORDER_ID ORDER BY H.ORDER_NUMBER, L.ITEM_NUMBER";
Class[] clazzes = new Class[] {OrderHeaderDO.class, OrderLineDO.class};
IterativeQuery query = new IterativeQuery(fryDB, clazzes, fry_sql);
query.setFetchSize(100);
DataObjectArrayIterator iterator = query.createDataObjectArrayIterator((Object[])null);
try {
   while (true) {
      List<DataObject[]> list = iterator.next(100);
      if (list == null) {
         break;
      }
      for (DataObject[] objs : list) {
         OrderHeaderDO orderHeader = (OrderHeaderDO)objs[0];
         OrderLineDO orderLine = (OrderLineDO)objs[1];

         System.out.println("OrderNr="+orderHeader.getOrderNr()+" item="+orderLine.getItemNumber());

      }
   }
} finally {
   iterator.close();
}