Use an implementation of the com.ibm.pdq.runtime.handlers.RowHandler<ROW> interface
to customize how pureQuery returns each row of a query result from
an SQL query.
The RowHandler
<ROW> interface declares
only one method:
ROW handle (ResultSet resultSet, ROW object) throws SQLException;
For
each row of a query result, pureQuery calls the handle() method to
create the object that represents that row. pureQuery passes to the
method the query results in an instance of java.sql.ResultSet, with
the ResultSet's cursor positioned on the current row. The parameter
object is null. If you are creating an implementation of RowHandler<ROW>,
implement this method to create and return an object of type <ROW>
that represents the row indicated by the cursor of the ResultSet instance.
If
the ResultSet object is empty, or the last row of the ResultSet object
has been read, the handle() method is not called.
Attention: Before pureQuery calls the handle() method, pureQuery
positions the cursor of the ResultSet on the current row by calling
the ResultSet's next() method. Therefore, your handle method must
not call the ResultSet's next() method.
In this example,
the returned String object for each row in a query result contains
a delimited series of string values of each column. In this very general,
simple handler, no detailed knowledge is needed regarding the input
ResultSet object.
package customHandlers;
import com.ibm.pdq.runtime.handlers.RowHandler;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class SimpleStringRowHandler implements RowHandler<String>
{
public SimpleStringRowHandler ()
{
delimiter = ", ";
}
public SimpleStringRowHandler (String delimiter)
{
this.delimiter = delimiter;
}
private int columnCount = -1;
private String delimiter;
String handle (ResultSet resultSet, java.lang.String object) throws SQLException
{
if (columnCount < 0)
columnCount = resultSet.getMetaData().getColumnCount();
StringBuffer myBuff = new StringBuffer();
if (columnCount > 0) {
myBuff.append(resultSet.getString(1));
for (int ii=2; ii<=columnCount; ii++) {
myBuff.append(delimiter);
myBuff.append(resultSet.getString(ii)));
}
}
return myBuff.toString();
} // handle
}
Using RowHandler objects with annotated methods
There
are three ways in which you can specify RowHandler objects for annotated
methods.
- Use the @Handler annotation
- Provide the @Handler annotation on the declaration of a method,
in addition to providing the @Select or @Update annotation. In the
@Handler annotation, specify the class of your RowHandler implementation.
The handler that you specify must have a public, no-argument constructor.
pureQuery uses the no-argument constructor to create a single instance
of the handler, and that instance is used by all invocations of the
annotated method.
- For example, you could declare an annotated method that uses SimpleStringRowHandler.
pureQuery instantiates the handler with the no-argument constructor,
which means the column delimiter is ", ".
@Select(sql = "select * from employee where workdept = ?1.departmentNumber")
@Handler(rowHandler = customHandlers.SimpleStringRowHandler.class)
public Iterator<String> selectEmployeesInDepartment(DepartmentBean department);
- Use the @Handler annotation with RowHandlerWithParameters element
- The RowHandlerWithParameters element on the
@Handlers annotation provides access to the SQL statement input parameters
that were passed on method. When you specify the element RowHandlerWithParameters on
an @Handler annotation, the generated code invokes a handle( ) method
with an additional parameter, the object array representing the parameter
values that were passed on the method. The object is basically the
same as the object array that would be passed to a parameterHandler,
if it had been specified.
A RowHandlerWithParameters implementation
class must provide two handle() methods: one method with two arguments,
and one method with three arguments. The two-argument signature can
be an empty method.
For the following example, the application
contains a query where multiple input parameters become search criteria
for the query. The SELECT list does not return these values because
they would be the same as the input values. Yet these values need
to be present in the created output bean. Using the style of RowHandler,
the application can access the values in the RowHandler:
@Select ("SELECT EVENT_NAME, EVENT_LOCATION FROM EVENTS" +
"WHERE EVENT_CODE = ? AND EVENT_DATE = ? AND EVENT_TIME = ?")
@Handlers (rowHandlerWithParameters = com.bigtickets.eventRowHandlerWithParameters)
List<Event> getEvents (eventCode,eventDate,eventTime);
The
RowHandlerWithParameters class
that you provide includes a handle method that takes three parameters:
Sql String
ResultSet
Object[] (contains the input parameters)
The handle method has the ability to access parameters
that were passed to the SQL statement in the Object array.
- Use method parameters
- Alternatively, you can specify the RowHandler implementation as
an argument in the annotated method definition. When your application
calls the method, it can provide the actual instance for pureQuery
to use.
- Handlers that are specified as parameters are not subject to as
many restrictions as those specified in the @Handler annotation.
For example, handlers specified as parameters do not need to have
a no-argument constructor. However, specifying handlers with the
@Handler annotation causes pureQuery to use only one instance of a
handler for all invocations of the method, and requires pureQuery
to do less work at run time. Therefore, the @Handler annotation gives
a slight performance advantage, so you should specify handlers as
method parameters only when you cannot specify them by using the @Handler
annotation.
When you specify a handler as a parameter, the parameter
must be the last parameter in the method signature. If you specify
a RowHandler together with a ParameterHandler as parameters, the handlers
must be the last two parameters in the method signature.
For
example, in an interface you can define an annotated method that uses
the SimpleStringRowHandler:
@Select(sql = "SELECT * FROM employee where workdept = ?1.departmentNumber")
Iterator<String> selectEmployeesInDepartment(DepartmentBean department, \
customHandlers.SimpleStringRowHandler rowHandler);
You
can invoke the method by specifying an instance of SimpleStringRowHandler
that delimits columns with a tab character:
Iterator<String> employees = face.selectEmployeesInDepartment(theDepartment, new customHandlers.SimpleStringRowHandler("\t"));
If
you want to use a single annotated method declaration with multiple
RowHandler<ROW> implementations, you can define an annotated method
that uses the generic RowHandler<ROW> in the signature:
@Select(sql = "SELECT * FROM employee where workdept = ?1.departmentNumber")
<ROW> Iterator<ROW> selectEmployeesInDepartment(DepartmentBean department, RowHandler<ROW> rh);
You
can then invoke the method by passing a RowHandler object of the required
type:
Iterator<String> employees = face.selectEmployeesInDepartment(theDepartment, new customHandlers.simpleStringRowHandler("\t"));
Using RowHandler objects with inline methods
To
specify a RowHandler for an inline method, use one of the inline methods
that takes a RowHandler as a parameter. For example, if you want the
results of a query to be in a list, with each row created by a custom
RowHandler named customHandlers.SimpleStringRowHandler, you can call
a com.ibm.pdq.runtime.Data.queryList(…) method that takes a RowHandler
as a parameter, as in this example:
List<EmployeeString> emp = db.queryList("select * from employee where workdept = ?1.departmentNumber",\
new customHandlers.SimpleStringRowHandler("\t"), theDepartment);