Processing Rows In Batches

by Steven F. Lott and Robert Lucente





Example 1:



SELECT X, Y, Z

FROM

 (SELECT X, Y, Z 

  FROM SOMETABLE

  ORDER BY X) TEMPVIEW

WHERE ROWNUM <= 100;





Example 2: 



SELECT X, Y, Z 

FROM SOMETABLE

ORDER BY X

LIMIT 100;





Example 3: 



void topNRows( Connection db ) {

    String someQuery = "SELECT X, Y, Z FROM SOMETABLE ORDER BY X";

    int N= 100;

    Statement firstN= db.prepareStatement( someQuery );

    ResultSet rs= firstN.executeQuery();

    for( int i= 0; i != N && rs.next(); ++i ) {

        // process the row

    }

    // assert (N rows processed) or (no more rows)

    rs.close();

    firstN.close();

}





Example 4:  



import java.util.*;



/**

* Collects the N rows with the largest key 

* values from a ResultSet. This version is 

* hard-wired to expect a String in column 1, 

* and sort key is an int in column 2.

* @author slott

*/

public class TopNRows {

    /** Number of rows to keep */

    int keep;

    /** Set of top N rows */                               (1)

    TreeMap topRows;

    /**

    * Creates a new instance of TopNRows.

    * @param keep int top number of values to keep.

    */

    public TopNRows( int keep ) {

        this.keep= keep;

    }

    /**

    * Scans the given result set, checking column 2, the integer key,

    * for the largest value.

    * @param rs ResultSet to scan

    */

    public void scan( ResultSet rs ) {

        topRows= new TreeMap();

        while( rs.next() ) {

            Integer rowKey= new Integer(rs.getInt(2));     (2)

            if( topRows.size() <= keep ) {

              topRows.put( rowKey, rs.getString(1) );

              continue;

            }

            Integer minKeepKey= (Integer)topRows.firstKey(); (3)

            if( rowKey.compareTo( minKeepKey ) > 0 ) {

                topRows.remove( minKeepKey );

                topRows.put( rowKey, rs.getString(1) );

            }

        }

    }

    /**

    * Returns an iterator over the selected results.

    * These will be Map.Entry objects. 

    * The key will be column 2 values, transformed to Integers. 

    * The entry will be column 1 values, still Strings.

    * @return Iterator over the Map.

    */

    public Iterator iterator( ) {

        return topRows.entrySet().iterator();

    }

}











2



