Examples of queries over in-memory Java collections

The following examples demonstrate how to implement queries over in-memory collections in pureQuery.

Example of a simple query on a collection

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:

  1. Create a connection to the database.
  2. Create an instance of the implementation of the Data interface. Because a connection object is passed to the constructor, you can use the methods in the implementation for running SQL against a database.
  3. Create another implementation of the Data interface. Because no connection object is passed to the constructor, you can use the methods in this implementation only for queries over collections.
  4. Query the database for all customers and return the customer records as Customer objects in the List customers.
  5. Close the connection to the database.
  6. Set the value for the San Francisco Bay Area sales region.
  7. Query the Customer objects in the list and return a count of the customers who are in the target sales region.
  8. Close and release all resources that are used by qocdata.

Instead of closing qocdata, you could perform additional analysis on the customers, such as that demonstrated in the next example.

Example of a complex query on a collection

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);
}
The methods perform the following functions:
public Integer countCities (List<Customer> curRegion)
From a list of the current region's customers, count the number of cities in which the customers are located.
public CitySize[] getCitySize(List<Customer> curRegion)
From a list of the current Region's Customers, determine the size of each city, based on its number of different addresses, and return an array of CitySize objects that are in descending order of size.
List<Customer> getMailingList(List<Customer> curRegion, CitySize[] topFive)
From a list of the current Region's Customers, select all those whose city is in a given list of cities. The use of "this" causes the actual object queried, in this case from the List<Customer> curRegion, to be selected into the List<Customer> returned by getMailingList. A similar query that selects "cr.*" usually results in a copy of the object queried being returned

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:

  1. Create a connection to the database.
  2. Create an instance of the implementation of the CustomerQuery interface. Because a connection object is passed to the constructor, you can use the methods in the implementation for running SQL against a database.
  3. Create an instance of the implementation of the RegionReport interface. Because no connection object is passed to the constructor, you can use the methods in this implementation only for queries over collections.
  4. Set the value for the San Francisco Bay Area sales region.
  5. Populate the List customers with Customer objects that represent the customers who are in the target sales region.
  6. Close and release all resources that are used by cQuery.
  7. Get a count of the different cities that the customers are located in.
  8. Print the first part of the report.
  9. Get an array of CitySikze objects, one object for each city in which customers are located.
  10. Create an array of type CitySize to hold the CitySize objects for the five largest cities.
  11. Print the name and size of the five largest cities.
  12. Get a list of the customers who are in the five largest cities.
  13. Close and release all resources that are used by inMem.

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.


Feedback