Executing a Dynamic Query: openByQuery

The Database interface provides the openByQuery method for executing a dynamic query. The Java client passes a string containing SQL along with an array of input parameters to openByQuery. The method returns a java.util.Iterator object containing objects representing the results of the query. This allows you to process the query results using standard objects rather than result sets. There are a few rules governing the SQL string passed to the method:

• The first returned column contains the fully qualified name of the %ID column for a table — JavaTutorial.PhoneNumber.%ID, for example.

• ORDER BY clauses must use column names rather than numbers to specify the ordering.

The following Java client method uses openByQuery to execute a query retrieving all of the PhoneNumber objects belonging to a particular Contact and with the value type for the PhoneNumberType. The method iterates through the set of returned PhoneNumber objects displaying the values of PhoneNumberType and Number for each.

public class BindingExamples {
   public static void displayPhoneNumbersByTypeOBQ(Database db, String id, 
   String type) throws CacheException{
      Object[] args = {id, type};
      String sql = "SELECT JavaTutorial.PhoneNumber.%ID FROM " +
      "JavaTutorial.PhoneNumber WHERE Contact = ? AND PhoneNumberType = ?";
      Iterator iter = db.openByQuery(sql, args);
      while (iter.hasNext()){
         PhoneNumber pn = (PhoneNumber)iter.next();
         System.out.println("Type: " + pn.getPhoneNumberType() +
         " Number: " + pn.getNumber());
      }
   }
}