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.
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 |
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);
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.
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.