Returning Multiple Rows from a Database Control Method

This topic discusses methods that you can add to a Database control that will return multiple rows in from the database.

To learn about Database controls, see Database Control: Using a Database from Your Web Service.

To learn about creating a Database control, see Creating a New Database Control.

# Returning Multiple Rows from a Database Control Method

Some database operations return one or more columns from multiple rows. An example of such an operation is a SELECT operation that returns one or more columns from multiple rows.

There are several ways to return multiple rows from a database operation. The techniques for returning multiple rows are analogous to those described above for returning a single row. See Returning a Single Row from a Database Control Method.

The first choice is whether to return an array of objects or a java.util.Iterator.

An array of objects may be more convenient for the users of your control since they won't have to understand how to use Iterators. However, when an array is returned only one database operations is performed and the entire result set must be stored in memory. For large result sets, 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 result set.

To learn how to return an array of objects, see Returning an Array of Objects.

While Iterators require more sophistication on the part of users of your control, they are more efficient at handling large result sets. An Iterator is accessed one element (row) at a time (via the Iterator's next() method), and it will transparently make 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.

To learn about returning a java.util.Iterator, see Returning an Iterator.

Finally, you may 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.

To learn about returning a java.sql.ResultSet, see Returning a Result Set.

# Returning an Array of Objects

When you want to return an array of objects, you 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 these techniques are given 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 intcustid;
        public String name;
    }

    /**
     * @sql statement="SELECT custid,name FROM customer WHERE custage<19"
     *      array-max-length=100
     */
    Customer [] findAllMinorCustomers()

This example will return 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 Returning an Object.

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

When an array is returned by a Database control method, the @jws:sql tag may have the array-max-length attribute. This attribute can protect you from very large result sets that may be returned by very general queries. If array-max-length is present, no more than that many rows will be 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 as described below in Returning an Iterator.

## 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 intcustid;
        public String name;
        public Customer() {};
    }

    /**
     * @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. Keys are case-insensitive when accessed via the HashMaps methods (the capitalization of the key names returned by HashMap.keySet() depends on the database driver in use). The value is an object of the 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.

To access the name field of the returned records in the calling web service (JWS file), you could use the following code:

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

    int custID = <some customer ID>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, you declare the method's return type to be java.util.Iterator. You then add the iterator-element-type attribute to the @jws:sql tag 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.

Note: The Iterator that is returned is only guaranteed to be valid for the life of the JWS 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 @jws:sql tag.

    public static class Customer
    {
        public intcustid;
        public String name;
        public Customer() {};
    }

    /**
     * @sql statement="SELECT custid,name FROM customer"
     *      iterator-element-type="Customer"
     */java.util.IteratorgetAllCustomersIterator()

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

To access the returned records in the calling web service (JWS file), you could use the following code:

    CustomerDBControl.Customercust;

    java.util.Iteratoriter = 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 @jws:sql tag.

    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.IteratorgetAllCustomersIterator()

To access the returned records in the calling web service (JWS file), you can use the following code:

    java.util.HashMapcustHash;java.util.Iteratoriter = 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

You may gain complete access to the java.sql.ResultSet returned by a query. This is an advanced use. 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 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.

    /**
     * @jws:sql statement="SELECT * FROM customer"
     */
    public java.sql.ResultSetfindAllCustomersResultSet();

To access the returned ResultSet in the calling web service (JWS file), you could use the following code:

    java.sql.ResultSetresultSet;
    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 named name.

Related Topics

Parameter Substitution in @jws:sql Statements