Referring to objects in in-memory Java collections by using "this" in SELECT statements

In SELECT statements in queries over in-memory collections, you can use "this" to refer to objects in collections.
Being able to refer to objects in collections with "this" is useful for two reasons:

How "this" works

For each collection, the objects contained by the collection effectively become the contents of a column named ‘this' in an SQL table that represents the content of the collection.

pureQuery examines the class of the collection's contained objects to determine the names of the properties in those objects. Property names become names for columns in an SQL table, and the property values become property values in the corresponding row of the SQL table.

For example, consider the collection List<Customer> named customers and this definition of a Customer bean.

public Customer {
    public Integer custId;
    public String name;
    public String addressl1;
    public String city;
    public Integer storeRegion;
 }

pureQuery might represent the contents of the collection in a virtual SQL table like this.

Table 1. Virtual SQL table for List<Customer> customers
THIS CUSTID NAME ADDRESSL1 CITY REGION
Customer object 0 001 Big Customer 1234 Market Street San Francisco 900
Customer object 1 002 Another Big Customer 5678 California Street San Francisco 900
Customer object 2 003 Still Another Big Customer 90 North First Street San Jose 900

Example

Suppose that you need an annotated method to query a list of Customer objects and return those objects that represent customers who are located in the five largest cities in a sales region.

You could write the method like this:

@Select(sql="SELECT cr.custId, cr.name, cr.addressl1, " + 
         " cr.city, cr.storeRegion " +
         " FROM ?1 AS cr, ?2 AS t5 WHERE cr.city = t5.city ")  
  List<Customer> getMailingList(List<Customer> curRegion,
                       CitySize[] topFive);
The CitySize class might have this definition
public class CitySize {
  public String city;
  public Integer size;
}

As Customer objects are read from the curRegion list, for objects that have a city equal to a city appearing in the CitySize array, the selected column values are used to populate a new instance of a Customer object, and that new object is added to the list that is returned by the getMailingList() method.

You could also select all of the columns in the Customer objects, like this:

@Select(sql="SELECT cr.* " +
         " FROM ?1 AS cr, ?2 AS t5 WHERE cr.city = t5.city ")  
  List<Customer> getMailingList(List<Customer> curRegion,
                       CitySize[] topFive);

As instances of Customer objects are read from the curRegion list, for objects that have a city equal to a city appearing in the CitySize array, all selected column values (including the column "this") are used to populate a new instance of a Customer object, and that new object is added to the List returned by the getMailingList() method.

Notice that both of these methods create new instances of Customer objects that match the query. If you use "this" in the list in the SELECT statement and the objects that match the query are of any of the following types, then your methods create new instances of the objects that match their queries, too.
  • Primitive type or a wrapper of a primitive type
  • byte[]
  • java.sql.Date
  • java.sql.Time
  • java.sql.Timestamp

In this example, as Customer objects are read from the curRegion list, those objects that have a city equal to a city appearing in the CitySize array are added to the list that is returned by the getMailingList() method.

@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 differences between the different versions of the getMailingList() method would be more visible if this method were defined to return a List<Map<String,Object>> object.

The first version would result in each Map object containing String keys with the values custid, name, addressl1, city, and storeregion.

The second version would result in each Map object containing String keys with the values this, custid, name, addressl1, city, and storeregion.

The third version would result in each Map object containing a String key with the value this.


Feedback