Returning Multiple Rows from a Database Control Method

This topic describes how to write a method on a Database control that returns multiple rows from the database. It describes the ways in which you can perform this operation, including returning an array, returning an Iterator object, and returning a resultset.

Deciding How to Return Multiple Rows

A SELECT query may return one or more fields from multiple rows. A method on a Database control that returns multiple rows should have a return type that can store these values. The Database control method can return an array of objects, an Iterator, or a resultset.

Returning an array of objects is the easiest way to return multiple rows, so it is a good choice if you think your users will prefer simplicity when using your control. However, when an array is returned only one database operation is performed and the entire resultset must be stored in memory. For large resultsets, this is problematic. You can limit the size of the returned array, but then you cannot provide a way for your user to get the remainder of the resultset. To learn how to return an array of objects, see the Returning an Array of Objects section, below.

While Iterators require more sophistication on the part of users of your control, they are more efficient at handling large resultsets. An Iterator is accessed one element (row) at a time via the Iterator's next() method, and it transparently makes repeated requests from the database until all records have been processed. An Iterator does not present the risk of running out of memory that an array presents. However, note that an Iterator returned from a database control cannot be used within a Page Flow file (JPF), because an Iterator wraps a ResultSet object, which is always closed by the time it is passed to the web-tier (where page flow files reside). For this reason, your database control should return an array of objects (see above) when it is called from a Page Flow file. Also, an Iterator cannot be returned to a stateful process, because stateful processes cannot maintain an open database connection (which Iterators require). To learn about returning a java.util.Iterator, see the Returning an Iterator section, below.

Finally, you can choose to return a java.sql.ResultSet from a Database control method. This grants complete access to the results of the database operation to clients of your control, but it requires knowledge of the java.sql package. Also, note that a ResultSet returned from a database control cannot be used within a page flow file (JPF), because a ResultSet object is always closed by the time it is passed to the web-tier (where page flow files reside). For this reason, your database control should provide an array of objects when it is called from a page flow file. To learn about returning a java.sql.ResultSet, see the Returning a Resultset section, below.

Returning an Array of Objects

To return an array of objects, declare the method's return type to be an array of the object you want to return. That type may be either a type you define, or it may be java.util.Hashmap.

Examples of both of these techniques are provided in the following sections.

Returning an Array of User-Defined Objects

The following example demonstrates how to return an array of objects whose type you have declared. In this case, an array of Customer objects is returned:

public static class Customer
{ 
    public int custid;
    public String name;
}
/**
 * @jc:sql statement="SELECT custid,name FROM customer WHERE custage<19"
 *     array-max-length=100
 */
Customer [] findAllMinorCustomers()

This example returns all rows in which the custage field contains a value less than 19.

When returning an array of objects, the class declared as the return type of the method must meet the criteria described in the Returning an Object section of the Returning a Single Row from a Database Control topic. If no rows are returned by the query, the returned value of the Database control method is a zero-length array.

If you are returning an array from Database control method, you can limit the size of the array returned by setting the array-max-length attribute of the @jc:sql annotation. This attribute can protect you from very large resultsets that may be returned by very general queries. If array-max-length is present, no more than that many rows are returned by the method.

The default value of array-max-length is 1024.

The array-max-length attribute may have the special value "all" (in quotes), indicating that all rows satisfying the query should be returned. Note that if the query is too general, this can result in use of all available memory. To avoid excessive memory usage, return an Iterator object as described below in the Returning an Iterator section, below.

Returning an Array of HashMaps

Returning an array of HashMaps is analogous to returning an array of user-defined objects, which is described in the preceding section.

The following example demonstrates returning an array of HashMaps:

public static class Customer
{ 
    public int custid;
    public String name;
    public Customer() {};
}
/**
 * @jc:sql statement="SELECT custid,name FROM customer WHERE custage<19"
 *     array-max-length=100
 */
java.util.HashMap [] findAllMinorCustomersHash()

The array of HashMaps returned contains an element for each row returned, and each element of the array contains an entry for each column in the result. The key for each entry is the corresponding column name. The capitalization of the key names returned by HashMap.keySet() depends on the database driver in use, but keys are case-insensitive when accessed via the HashMap's methods. The value returned is an object of the Java Database Connectivity (JDBC) default type for the database column.

If no rows are returned by the query, the returned value of the Database control method is a zero-length array.

For information on mapping between Java types and JDBC types (database types), see Mapping Database Field Types to Java Types in the Database Control.

The following code shows how to access the name field of the returned records:

/**
 * @common:control
 */
private CustomerDBControl custDB;

java.util.HashMap [] hashArr;
String name;

hashArr = custDB.findAllMinorCustomersHash();
for(i=0; i<hashArr.length; i++)
{
    name = (String)hashArr[i].get("NAME");
    // say hello to the all of the minors

   System.out.println("Hello, " + name + "!"); 
}

Returning an Iterator

When you want to return an Iterator object, you declare the method's return type to be java.util.Iterator. You then add the iterator-element-type attribute to the @jc:sql annotation to indicate the underlying type that the Iterator will contain. The specified type may be either a type you define, or it may be java.util.Hashmap. Examples of these techniques are given in the following sections.

The Iterator that is returned is only guaranteed to be valid for the life of the method call to which it is returned. You should not store an Iterator returned from a Database control method as a static member of your web service's class, nor should you attempt to reuse the Iterator in subsequent method calls if it is persisted by other means.

Returning an Iterator with a User-Defined Object

To return an Iterator that encapsulates a user-defined type, provide the class name as the value of the iterator-element-type attribute of the @jc:sql annotation, as shown here:

public static class Customer
{ 
    public int custid;
    public String name;
    public Customer() {};
}
/**
 * @sql statement="SELECT custid,name FROM customer"
 *     iterator-element-type="Customer"
 */
java.util.Iterator getAllCustomersIterator()

The class specified in the iterator-element-type attribute must meet the criteria described in Returning an Object.

The following example shows how to access the returned records:

CustomerDBControl.Customer cust;
java.util.Iterator iter = null;
iter = custDB.getAllCustomersIterator();
while (iter.hasNext()) 
{
    cust = (CustomerDBControl.Customer)iter.next();
    // say hello to every customer
    System.out.println("hello, " + cust.name + "!");
}

Returning an Iterator with HashMap

To return an Iterator that encapsulates a HashMap, provide java.util.HashMap as the value of the iterator-element-type attribute of the @jc:sql annotation, as shown here:

public static class Customer
{ 
    public int custid;
    public String name;
    public Customer() {};
}
/**
 * @sql statement="SELECT custid,name FROM customer"
 *     iterator-element-type="java.util.HashMap"
 */
java.util.Iterator getAllCustomersIterator()

The following code shows how to access the returned records:

java.util.HashMap custHash;
java.util.Iterator iter = null;
int customerID;
String customerName;
iter = custDB.getAllCustomersIterator();
while (iter.hasNext()) 
{
    custHash = (java.util.HashMap)iter.next();
    customerID = (int)custHash.get("CUSTID");
    customerName = (String)custHash.get("NAME");
}

The HashMap contains an entry for each database column that is returned by the query. The key for each entry is the corresponding column name, in all uppercase. The value is an object of the JDBC default type for the database column.

For information on mapping between Java types and JDBC types (database types), see Mapping Database Field Types to Java Types in the Database Control.

Returning a Resultset

The Database control is designed to allow you to obtain data from a database in a variety of ways without having to understand the classes in the java.sql package. If you and your users do understand these classes, however, you can gain complete access to the java.sql.ResultSet object returned by a query.

If you want to return a resultset, you declare the method's return type to be java.sql.ResultSet. A client of your control then accesses the resultset directly to process the results of the database operation.

The following example demonstrates returning a resultset:

/**
 * @jc:sql statement="SELECT * FROM customer" 
 */
public java.sql.ResultSet findAllCustomersResultSet(); 

The following code shows how to access the returned resultset:

java.sql.ResultSet resultSet;
String thisCustomerName;
resultSet = custDB.findAllCustomersResultSet();
while (resultSet.next())
{
    thisCustomerName = new String(resultSet.getString("name"));
}

This example assumes the rows returned from the database operation include a column called name.

Related Topics

Returning a Single Value from a Database Control Method

Returning a Single Row from a Database Control Method

Parameter Substitution in @jc:sql Statements