Returning a Single Row from a Database Control Method

This topic discusses methods that you can add to a Database control that return a single row 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.

To learn about parameter substitution in @jws:sql statements, see Parameter Substitution in @jws:sql Statements.

# Returning a Single Row from a Database Control Method

If the database operation returns multiple columns, you have a choice of return values for the method. You may return an object that meets specific criteria, or you may return a java.util.HashMap.

# Returning an Object

To return an object of a particular class, you must declare the class with specific characteristics:

- The class must contain members with names that match the names of the columns that will be returned by the query. The matching is case-insensitive, since database column names are case-insensitive. The class may contain other members, but must include at least the members corresponding to the columns that will be returned.

- The members must be of an appropriate type to hold a value from the corresponding column in the database. For information on mapping between database types and Java types, see Mapping Database Field Types to Java Types in the Database Control.

- If the class is an inner class (a class declared inside another class), the class must be declared public static.

Since this class exists in a tight relationship with the Database control, the class is typically declared as an inner class of the main interface in the Database control's CTRL file. However, it may be any Java class that meets the above criteria.

The example below illustrates returning an object from a Database control method:

    public static class Customer
    {
        public int custid;
        public String name;
        public Customer() {};
    }
    /**
     * @sql statement="SELECT custid,name FROM customer WHERE custid={customerID}"
     */
    Customer findCustomer(int customerID)

Note: Class member variables and the class itself must be public in order for the Database control to substitute them.

If the corresponding database field does not contain a value, the class member will be set to null if it is an object and 0 or false if it is a primitive. This may affect your decisions regarding the types you use in your class. If the database field contained no data, an Integer member would receive the value null, but an int member would receive the value 0. Zero may be a valid value, thus use of int instead of Integer makes it impossible for subsequent code to determine whether a value was present in the database.

If there is no column in the database corresponding to a member of the class, that member will also be set to null or 0, depending on whether the member is an primitive or an object.

If the query returns columns that cannot be matched to the members of the class, an exception is thrown. If you don't know the columns that will be returned or they may change, you should consider returning a HashMap instead of a specific class.

If no rows are returned by the query, the returned value of the Database control method is null.

In this example, the method is declared as returning a single object of type Customer. As such, even if the database operation returns multiple rows, only the first row is returned to the method's caller. To learn how to return multiple rows to the caller, see Returning Multiple Rows from a Database Control Method.

# Returning a HashMap

You may choose to return a HashMap if the number of columns or the particular column names returned by the query are unknown or may change.

To return a HashMap, declare the return value of the method as java.util.HashMap.

    /**
     * @jws:sql statement="SELECT * FROM customer WHERE custid={custID}"
     */
    public java.util.HashMap findCustomerHash(int custID);

The HashMap returned will contain 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 HashMap's 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.

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

In this example, the method is declared as returning a single object of type java.util.HashMap. As such, even if the database operation returns multiple rows, only the first row is returned to the method's caller.

To learn how return multiple rows to the caller, see Returning Multiple Rows from a Database Control Method

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

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

    /**
     * @jws:operation
     */
    public String getCustomerName(int custID)
    {
        java.util.HashMap hash;
        String name;
        hash = custDB.findCustomerHash(custID);
        if( hash != null )
        {
            name = (String)hash.get("NAME");
        }
        else
        {
            name = new String("Customer not found");
        }
        return name;
    }

If no rows are returned by the query, the returned value of the Database control method is null.

Related Topics

Parameter Substitution in @jws:sql Statements

Mapping Database Field Types to Java Types in the Database Control