RimuDB Logo
Database Access Framework for Java

Generic Queries

Sometimes a database query requires a join or some complex condition that is outside the basic capabilities of the Finder and WhereList classes. Similarly, it is sometimes impractical to have to use the primary key of the table to perform update or delete operations. Often an SQL statement that uses database specific syntax needs to be used for performance reasons. These are all cases when a Generic Query is the best solution.

The framework permits arbitrary SQL statements to be used with the GenericQuery, IterativeQuery and GenericUpdate classes. If a select statement is required, use GenericQuery. If the result set is likely to be very large then use IterativeQuery. If an insert, update or delete is required, use GenericUpdate.

For GenericQuery, the constructor specifies the database and an SQL statement. Internally, the framework uses PreparedStatements so the SQL statement can contain parameter markers for values. The caller executes one of the executeQueryXXX() methods, depending upon the type of result set the caller expects. If the SQL statement has parameter markers, then the parameters can be passed on the executeQueryXXX() method. The framework performs the statement execution, handles the connections and returns a resulting Object. The resulting object depends upon the executeQueryXXX() method used.

Table Names

Table names are stored in the Table Descriptor XML document. When the Finder.selectXXX() and Finder.find() methods are used, the table name is derived from this XML document. While is it possible to hard-code table names in GenericQuery SQL statements, it is preferable to derive the table names for GenericQueries from the Table Descriptor. If the table name ever changes, then only the Table Descriptor needs to change.

This is done using a special syntax in the SQL statement. Use the syntax of {dataobject-name}, or {DO-instance-name} or {fully-qualified-DO-instance-name}.

The following are all valid:


"SELECT VALUE FROM {OrderTransaction}"

"SELECT VALUE FROM {OrderTransactionDO}"

"SELECT VALUE FROM {com.mydomain.db.OrderTransactionDO}"

The name is case-sensitive. Fully qualified DO instance names are permitted in case there are multiple DO instances with the same name in different packages.

GenericQuery.executeQueryToObject()

Use this method if the SQL statement will return a single row containing a single column. The following constructor must be used: GenericQuery(Database db, String sql)


// method signature
Object executeQueryToObject(Object... parameters)

// Return the number of order transactions with a first and last name that match 'john' and 'smith'
Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select count(*) from {OrderTransaction} where first_name = ? and last_name = ?";
GenericQuery query = new GenericQuery(db, sql);
Integer value = (Integer)query.executeQueryToObject("john", "smith");

GenericQuery.executeQueryToDataObject()

Use this method if the SQL statement will return a single row containing a DataObject. The following constructor must be used: GenericQuery(Database db, Class<? extends DataObject> clazz, String sql)


// method signature
Object executeQueryToDataObject(Object... parameters)

// Return the order transaction for order 12345
String sql = "select * from {OrderTransaction} where orderNr = ?";
GenericQuery query = new GenericQuery(db, OrderTransactionDO.class, sql);
OrderTransactionDO orderTransaction = (OrderTransactionDO)query.executeQueryToDataObject(12345);

GenericQuery.executeQueryToObjectList()

Use this method if the SQL statement will return multiple rows each containing a single column. The following constructor must be used: GenericQuery(Database db, String sql)


// method signature
List<Object> executeQueryToObjectList(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select last_name from {OrderTransaction} where first_name = ?";
GenericQuery query = new GenericQuery(db, sql);
List<Object> list = query.executeQueryToObjectList("john");

GenericQuery.executeQueryToDataObjectList()

Use this method if the SQL statement will return multiple rows each containing a DataObject. The following constructor must be used: GenericQuery(Database db, Class<? extends DataObject> clazz, String sql)


// method signature
List<DataObject> executeQueryToDataObjectList(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select * from {OrderTransactionDO} where first_name = ? and last_name = ?";
GenericQuery query = new GenericQuery(db, OrderTransactionDO.class, sql);
List<DataObject> list = query.executeQueryToDataObjectList("john", "smith");
for (DataObject dataObject : list) {
   OrderTransactionDO orderTransaction = (OrderTransactionDO) dataObject;
   System.out.println("orderNr="+orderTransaction.getOrderNr());
}

GenericQuery.executeQueryToDataObjectListArray()

Use this method if the SQL statement will return multiple rows each containing multiple DataObjects. When the SQL statement contains a join of two or more tables then multiple Data Objects can be returned.

In order for this to work, the select statement must return all the columns required by the data objects. The following constructor must be used: GenericQuery(Database db, Class<? extends DataObject>[] clazz, String sql)


// method signature
List<DataObject[]> executeQueryToDataObjectListArray(Object... parameters)

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select * from {OrderHeader} A join {OrderLine} B on A.order_nr = B.order_nr";
Class[] clazzes = new Class[] {OrderHeaderDO.class, OrderLineDO.class};
GenericQuery query = new GenericQuery(db, clazzes, sql);
List<DataObject[]> list = query.executeQueryToDataObjectListArray();
for (DataObject[] dataObject : list) {
   OrderHeaderDO orderHeader = (OrderHeaderDO) dataObject[0];
   OrderLineDO orderLine = (OrderLineDO) dataObject[1];
   System.out.println("orderNr="+orderHeader.getOrderNr());
   System.out.println("  sequence="+orderLine.getSequence()+" item="+orderLine.getItemNumber());
}

GenericQuery.executeQueryWithBinderList()

Use this method if the SQL statement will return multiple rows each containing a selection of the available columns or scalar results. This is the most flexible form of the methods, allowing any values that can be returned by a select statement to be returned. It does however, require that a bean class is created for handling the selection. In most cases the DefaultQueryResultSetBinder can be used to bind the result set with the beans. If the DefaultQueryResultSetBinder is used, then the bean class must implement IResultSetBean. The following constructor must be used: GenericQuery(Database db, IQueryResultSetBinder binder, String sql)


// method signature
List<?> executeQueryWithBinderList(Object... parameters)

// A bean class
public class OrderTransactionSummaryBean implements IResultSetBean {
   private String name = null;
   private int count = 0;

   public void bindResultSet(ResultSet rs) throws RimuDBException {
      try {
         setName(rs.getString(1));
         setCount(rs.getInt(2));
      } catch (SQLException e) {
         throw new RimuDBException(e);
      }
   }

   public int getCount() {
      return count;
   }

   public void setCount(int count) {
      this.count = count;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }
}


// Executing the query

Database db = cdb.getDatabase(OrderTransactionDO.class);
String sql = "select name, count(*) from {OrderTransaction} where name = ?";
DefaultQueryResultSetBinder binder = new DefaultQueryResultSetBinder(OrderTransactionSummaryBean.class);
GenericQuery query = new GenericQuery(db, binder, sql);
List<?> list = query.executeQueryWithBinderList("test3name");
OrderTransactionSummaryBean bean = (OrderTransactionSummaryBean)list.get(0);

Helper Methods

There are some methods provided for Generic Queries that make some steps easier:

GenericQuery.buildParameterString(int i)

When constructing an SQL statement for a GenericQuery, a list of parameter markers sometimes needs to be created. i.e.


StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM MYTABLE WHERE CODE IN ");
sb.append(GenericQuery.buildParameterString(6));

Will generate the following string:


SELECT * FROM MYTABLE WHERE CODE IN (?, ?, ?, ?, ?, ?)

GenericQuery.buildElementString(String elements[])

Instead of creating an SQL statement with parameter markers, it is possible to build a list from an array of Strings. i.e.


String[] elements = new String[]{"ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX"}; 

StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM MYTABLE WHERE CODE IN ");
sb.append(GenericQuery.buildParameterString(elements));

Will generate the following string:


SELECT * FROM MYTABLE WHERE CODE IN ('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX')

Although this method is provided, it isn't as safe as using parameter markers. Constructing an SQL statement from Strings allows for the possibility of SQL injection attacks.