In this example, the collection is a java.util.List object, and is an implementation of the java.lang.Iterable interface. If you return a materialized List of Customer objects, you can ask multiple questions about that data without re-accessing the connected data source.
Again, suppose that you want to find out how many customers are in the San Francisco Bay Area sales region. Your code might look like this:
Connection con = DriverManager.getConnection(...); 1
Data db = DataFactory.getData(con); 2
Data qocdata = DataFactory.getData(); 3
List<Customer> customers = db.queryList( 4
"SELECT * from SALES.CUSTOMER", Customer.class);
db.close(); 5
int sanFranCode = ... 6
Integer countCity = qocdata.queryFirst( 7
"SELECT COUNT(*) FROM ?1.com.company.Customer AS cr WHERE cr.storeRegion = ?2",
Integer.class, customers, sanFranCode );
qocdata.close(); 8
The code performs the following steps:
Instead of closing qocdata, you could perform additional analysis on the customers, such as that demonstrated in the next example.
For a report to management, suppose that you need the number of distinct cities in the San Francisco Bay Area sales region, and the five biggest cities (in terms of their number of different addresses) in that region. Then, given management's go-ahead, a mailing campaign will target customers in those cities.
You can use one query against a connected data source and a small amount of application logic that uses annotated methods to tie in three queries over collections.
First, you need a staging class, used both by the calling application and the annotated methods:
public class CitySize {
public String city;
public Integer size;
}
Because you cannot use the methods in an implementation class both for queries over collections with queries against databases, you must create two separate interfaces.
The CustomerQuery interface defines the method for querying the data source. The getCustomersInRegion() method returns a list of Customer objects that represent customers who are in a specified sales region.
import com.company.Customer;
public interface CustomerQuery
{
@Select(sql=
"SELECT custId, name, ... FROM Customer WHERE region=?1")
List<Customer> getCustomersInRegion(int r);
}
The RegionReport interface defines the methods for querying the collection object.
import com.company.Customer;
import com.company.Campaign.CitySize;
public interface RegionReport
{
@Select(sql="SELECT COUNT(DISTINCT city) FROM ?1")
public Integer countCities (List<Customer> curRegion);
@Select(sql=
"SELECT city, COUNT(DISTINCT addressL1) AS size " +
" FROM ?1 GROUP BY city ORDER BY size DESC")
public CitySize[] getCitySize(List<Customer> curRegion);
@Select(sql="SELECT cr.this FROM ?1 AS cr, ?2 AS t5 " +
" WHERE cr.city = t5.city ")
List<Customer> getMailingList(List<Customer> curRegion,
CitySize[] topFive);
}
After you generate implementation classes for the interfaces, you can produce the report to management, and the list of targeted customers, with application logic that looks like this:
Connection con = DriverManager.getConnection(...); 1
CustomerQuery cQuery =
DataFactory.getData( CustomerQuery.class, con ); 2
RegionReport inMem =
DataFactory.getData( RegionReport.class ); 3
int sanFranCode = ...; 4
List<Customer> customers = cQuery.getCustomersInRegion(sanFranCode); 5
cQuery.close(); 6
Integer cityCount = inMem.countCities(customers); 7
System.out.println ( 8
" There are " + cityCount + " cities in region " + sanFranCode );
System.out.println (
" The largest 5, and their number of addresses are: " );
CitySize[] allCityList = inMem.getCitySize(customers); 9
CitySize[] topFive = new CitySize[5]; 10
for (int i=0; i<5; i++) 11
{ topFive[i] = allCityList[i];
System.out.println (
" " + topFive[i].city + " " + topFive[i].size);
}
List<Customer> mailingCust = 12
inMem.getMailingList(customers, topFive);
inMem.close(); 13
The code performs the following steps:
It is worth pointing out that if management determines that the target mailing would be either too small, or too large, a trivial change in the allocated size of the CitySize array, and the loop that populates it, is all that you would need to add or remove Customer objects from the eventual mailingCust list. The getMailingList() method is not dependent on the size of its input array.