Return type for inline methods that query databases: Array, List, and Iterator objects

You can use the Data interface's overloaded queryArray(), queryIterator(), and queryList() methods to return the entire query result of a query as an Array, Iterator, or List object.

Examples

Because the queryArray(), queryIterator(), and queryList() methods have a nearly identical pattern of overloading, the examples that follow are based only on queryIterator() method. Unlike the queryArray() and queryList() methods, however, the queryIterator() methods fetch one row at a time and do not materialize query results. Although Array and List objects hold all the resulting rows, Iterator objects hold only one row at a time.

Each of the examples uses this definition for a table named Customer:
CREATE TABLE company.Customer(   
  CUSTID INTEGER NOT NULL,   
  NAME VARCHAR(75),    
  ADDRESSL1 VARCHAR(60),   
  CITY VARCHAR(35),   
  REGION INTEGER,   
  PRIMARY KEY(CUSTID))
public Iterator<Map<String, Object>> queryIterator (java.lang.String sql, Object... parameters);
You might be interested in knowing the total number of customers in each sales region, with information on the largest regions presented first. The following code returns the aggregate summary as an Iterator object containing Map objects, each Map having keys of region and customer_count.
Connection con = DriverManager.getConnection(...);
Data db = DataFactory.getData(con);
Iterator<Map<String, Object>> regionSize =  
   db.queryIterator(
       "SELECT REGION, COUNT(*) AS CUSTOMER_COUNT " +
       " FROM company.Customer GROUP BY REGION " +
       " ORDER BY CUSTOMER_COUNT DESC" );
public <T> Iterator<T> queryIterator (java.lang.String sql, Class<T> returnClass, Object... parameters);
Assume the following bean definition, com.company.Customer, for a customer:
public Customer {
  public Integer custId;
  public String name;
  public String addressL1;
  public String city;
  @Column(name="REGION")
  public Integer storeRegion
}
You might need detailed information regarding the customers in two regions, perhaps the two largest regions. In the following code, you could set regionOne and regionTwo to the regions of interest before the call to the queryIterator(). The values of regionOne and regionTwo would be passed into the SELECT statement, when it is run, to take the place of the two parameter markers in the WHERE clause.
Connection con = DriverManager.getConnection(...);
Data db = DataFactory.getData(con);
Integer regionOne = ...;
Integer regionTwo = ...;
Iterator<com.company.Customer> twoRegions = db.queryIterator(
   "SELECT CUSTID, NAME, ADDRESSL1, CITY, REGION" +
   " FROM company.Customer WHERE REGION IN (?, ?)"
   com.company.Customer.class, regionOne, regionTwo );
You might need information to create a mailing list. Assume that you have available a hash map that uses a key of CITY and REGION to locate a city's corresponding state and ZIP code.

You could write a RowHandler (not shown in this example) that uses that hash map and all columns of the Customer table to create instances of the com.company.CustomerAddress bean for customers.

public CustomerAddress {
  public Integer custId;
  public String name;
  public String addressL1;
  public String city;
  public String state;
  public Integer zipCode;
}

The RowHandler, class com.company.Utils.HandleAddress, implements RowHandler. To create instances of the bean with the RowHandler, you could write code that looks like this:

Connection con = DriverManager.getConnection(...);
Data db = DataFactory.getData(con);
Integer regionOne = ...;
Integer regionTwo = ...;
RowHandler<com.company.CustomerAddress> addAddress =
                     new com.company.Utils.HandleAddress();
 
Iterator<com.company.CustomerAddress> twoRegions = 
   db.queryIterator(
     "SELECT CUSTID, NAME, ADDRESSL1, CITY, REGION" +
     " FROM company.Customer WHERE REGION IN (?, ?)"
     addAddress, regionOne, regionTwo );

There are numerous other ways to produce the same result. For example, there might be a table on the data source that maps CITY and REGION to STATE and ZIP. In this situation, you can write the query as a join between company.Customer table and that mapping table.

The example simply shows that RowHandlers can provide application logic at the point that the data is extracted from the data source, to produce objects of a needed class when all of the information required to produce those objects is not available on the data source.


Feedback