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.
CREATE TABLE company.Customer( CUSTID INTEGER NOT NULL, NAME VARCHAR(75), ADDRESSL1 VARCHAR(60), CITY VARCHAR(35), REGION INTEGER, PRIMARY KEY(CUSTID))
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 Customer { public Integer custId; public String name; public String addressL1; public String city; @Column(name="REGION") public Integer storeRegion }
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 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.