This topic describes how to write methods on a Database control that return a single row from the database. When you return a single row with multiple fields, your method must have a return type that can contain multiple values--either an object that is an instance of a class that you have built for that purpose, or a java.util.HashMap object.
If you know the names of the fields returned by the query, you will probably want to return a custom object. If the number of columns or the particular field names returned by the query are unknown or may change, you may choose to return a HashMap.
You can specify that the return type of a Database control method is a custom object, an instance of a class whose members correspond to fields in the database table. In most cases, a class whose members hold corresponding database field values is declared as an inner class (a class declared inside another class) in the Database control's JCX file. However, it may be any Java class that meets the following criteria:
The following example declares a Customer class with members corresponding to fields in the Customers table. The findCustomer method returns an object of type Customer:
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: The Customer class above is simplified for the sake of clarity. For data modelling classes, it is generally good design practice to have private fields, with public setter and getter methods.
public static class Customer { private int custid; private String name; public Customer() {}; public int getCustid() { return this.custid; } public void setCustid(int custid) { this.custid = custid; } public String getName() { return this.name; } public void setName(String name) { this.name = name; } }
If a database field being queried contains no value for a given row, the class member is set to null if it is an object and to 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, so using 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 is also 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. For more information, see the Returning a HashMap section, below.
If no rows are returned by the query, the returned value of the Database control method is null.
In the example given above, the method is declared as returning a single object of type Customer. So 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.
If the number of columns or the particular column names returned by the query are unknown or may change, you may choose to return a HashMap. To return a HashMap, declare the return value of the method as java.util.HashMap, as shown here:
/** * @jc:sql statement="SELECT * FROM customer WHERE custid={custID}" */ public java.util.HashMap findCustomerHash(int custID);
The HashMap returned 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 all keys are case-insensitive when accessed via the HashMap's methods. The value is an object of the Java Database Connectivity (JDBC) default type for the database column. To learn more about mapping between database types, Java types and JDBC types, see Mapping Database Field Types to Java Types in the Database Control.
In the example above, the method is declared as returning a single object of type java.util.HashMap. So 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.
The following code allows you to access the name field of the returned record:
/** * @common:control */ private CustomerDBControl custDB; /** * @common: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 the query returns no rows, the returned value of the Database control method is null.
Parameter Substitution in @jc:sql Statements
Mapping Database Field Types to Java Types in the Database Control