Advanced query patterns

Common Query Patterns presents the simple zero.data API for retrieving database results as Java Beans or Maps. Often, developers want to be able to accomplish other, more advanced querying patterns.

Using JoinResultHandler

JPA, Hibernate, and even iBATIS provide functionality to map relational joins into a hierarchy of beans. JoinResultHandler does just that given a constrained set of assumptions about the SELECT statement and beans. If your use case meets these constraints, this handler can save a lot of coding efforts.

Constrained assumptions

The domain classes, or beans, must meet certain criteria in order to be mapped to a joined ResultSet. These rules are listed below:

1. All beans must meet the common Java Bean requirements. Namely, getters and setters for bean properties using the proper method naming syntax.
2. Beans may subclass.
3. Beans must have one or more properties that map to primary key(s).
4. The primary key properties must be annotated with @com.ibm.pdq.annotation.Id or unexpected results will occur.
5. By default, column names map identical property names. @com.ibm.pdq.annotation.Column may be used to map a column to a property when they have different names.
6. If your domain class has a constructor that takes arguments, you must provide a default, no argument constructor.
7. And, most importantly, the property representing a nested child class must be annotated with @zero.data.handler.Join

One-to-one mapping

Below is an example of a domain class:

import com.ibm.pdq.annotation.Id;

public class Address {
        @Id private int address_id;
        private String name;
        
        public Address() {}
        
        // standard getters/setters removed for brevity
}

import com.ibm.pdq.annotation.Id;
import com.ibm.pdq.annotation.Column;
import zero.data.handler.Join;

public class Person {
        @Id private int person_id;
        @Column(name="firstname")
        private String name;
        @Join
        private Address address;
        
        public Person() {}
        
        // standard getters/setters removed for brevity
}

In this example, Person.person_id maps to the column person_id and is used in the mapping algorithm. Person.name maps to the column firstname because of the @Column annotation. Person.address is a reference to a prepopulated instance of Address. This is an example of a one-to-one mapping.

One-to-many mapping

Taking the above use case between Person and Address and modeling Person to own many Addresses:

Below is an example of a domain class:

import com.ibm.pdq.annotation.Id;

public class Address {
        @Id private int address_id;
        private String name;
        
        public Address() {}
        
        // standard getters/setters removed for brevity
}

import com.ibm.pdq.annotation.Id;
import com.ibm.pdq.annotation.Column;
import zero.data.handler.Join;

public class Person {
        @Id private int person_id;
        @Column(name="firstname")
        private String name;
        @Join
        private List<Address> addresses;
}

Using JSONResultHandler and JSONObjectRowHandler

There exists both zero.data.handler.JSONResultHandler and com.ibm.pdq.runtime.data.handlers.JSONResultHandler. Although the class names are similar and may achieve the same result at first glance, the zero.data.handler.JSONResultHandler is designed specifically for the Zero runtime and makes use of Zero JSON libraries. Therefore, the output is more consistent with what is returned with Zero's JSON serializers, including date rendering.

JSONResultHandler

JSONResultHandler is used with the Manager.query() method to process the entire results of a ResultSet. It returns a JSONArray of JSONObject. Each JSONObject maps to a row in the ResultSet as described below.

def results = data.query('SELECT * FROM table', new JSONResultHandler())
// work with results which is JSONArray<JSONObject>

JSONObjectRowHandler

JSONObjectRowHandler is used with any of the convenience methods that take a RowHandler implementation. It returns a JSONObject for a row of data in the ResultSet.

def row = data.queryFirst('SELECT * FROM table', new JSONObjectRowHandler())
// work with row which is JSONObject

Paging query results

Paging support in zero.data is currently being implemented. Therefore, this documentation is incomplete at present.

Ever searched on the web? I'm sure you have, lots of time. When performing a search, are the thousands of results for a keyword returned all on one page? Never. They use paging to show the result little by little, usually in multiples of 5.

Paging means showing your query result in multiple pages instead of just put them all in one long page. By splitting the query result in multiple pages you can save processing and download time plus you don't have much scrolling to do.

Some database vendors support paging natively in their SQL implementation. Some are simple, others a bit verbose. Check your vendor's documentation for more details. Following are examples from more popular databases.

Take it to the LIMIT

MySQL and PostgreSQL make use of the LIMIT predicate as follows:

SELECT * FROM table LIMIT 0, 20

This tells the database to return the first 20 rows. LIMIT 51, 5 returns rows 51 through 55. This can be parameterized in a zero.data query as follows:

def start = 51
def max = 5
def results = data.queryList("SELECT * FROM table LIMIT $start, $max")

Using IteratorPagingResultHandler

As indicated above, SQL result paging is not consistently implemented for each database. zero.data provides a way to retrieve pages of data with IteratorPagingResultHandler.

data.beginTransaction()
def result = data.query("SELECT * FROM pager", new IteratorPagingResultHandler(new MapRowHandler(), 51, 75))
// work with result which is Iterator<Map<String,Object>> representing rows 51 through 75
data.endTransaction()

Since IteratorPagingResultHandler returns an Iterator, we need to operator and the results of the query before the connection is closed. Thus, we start with calling beginTransaction(), execute the query, work with the result, and then call endTransaction(), which ensures that the connection is closed. This pattern or use of inTransaction is required.

IteratorPagingResultHandler returns an Iterator of whatever type the the passed in RowHandler implementation returns. In the example above, MapRowHandler returns a Map for each row, as its name implies. To return bean, BeanRowHandler could be used.

IteratorPagingResultHandler has two constructor signatures:

IteratorPagingResultHandler(RowHandler handler, int start, int end)
IteratorPagingResultHandler(int pageNumber, int pageSize, RowHandler handler)

The two signatures offer two patterns of dealing with paging. The first of the signatures allows for explicit start and end values. The example above makes use of this. The second signature takes the page number and page size and calculates the actual start and end rows for you. The following example would return the same results, rows 51 to 75, as the example above:

data.beginTransaction()
def result = data.query("SELECT * FROM pager", new IteratorPagingResultHandler(3, 25, new MapRowHandler()))
// work with result which is Iterator<Map<String,Object>> representing rows 51 through 75
data.endTransaction()

CLOB and the ClobbableMapRowHandler

// assumes column 'content' is CLOB
data.beginTransaction()
def result = data.queryFirst("SELECT * FROM mytable")
java.sql.Clob content = result.content
// work with java.sql.Clob objects between transaction because the Connection is still open
String str = content.subString(0, content.length()) 
data.endTransaction()

However, for smaller CLOB objects, it may be tolerable to treat as a String. When this is the case, ClobbableMapRowHandler is your friend. This RowHandler implementation extends the MapRowHandler to process CLOB objects as String. It can be used as follows:

// assumes column 'content' is CLOB
def result = data.queryList("SELECT * FROM comments", new ClobbableMapRowHandler())
String content = result.content