PriDE Design Patterns:
Extended and complex queries

The introduction explained already how to perform a query-by-example just using PriDE base functionality to read records according to an attribute value combination (in the simplest case: according to the primary key). This approach is the most needed one. For the assembly of more complex query expressions, the methods ObjectAdapter.find(String) and ObjectAdapter.query(String) can be used. As a difference (like in all other variants of theses two functions) find() returns only the first matching record while query() returns all matches. Both functions get a where-clause passed the syntactic and semantic correctness of which must be ensured by the application. PriDE does not analyse this expression internally, except a check for null, which will cause no where clause at all to be attached resulting in a query for all records of a table. The following example shows a query for all customers of IDs 1000 up to 2000:

Customer c = new Customer();
ResultIterator iter = c.query("id >= 1000 AND id <= 2000");
Customer[] result = (Customer[])iter.toArray();

Building the expressions completely unassisted is of course an error prone task as this is e.g. well-known from JDO. As an alternative to self-defined conditions, PriDE provides the class SQLExpression. This class eases the dynamic assembly of expressions based on the most important and popular SQL operators (siehe Interface SQLExpression.Operator). As another advantage, there is a common central formatting of SQL expressions. The constructor gets passed an SQLFormatter, which usually is just the current Database object, forwarding the formatting to its current ResourceAccessor.The following example illustrates the usage of SQLExpression, to define the same query as above:

Customer c = new Customer(); SQLExpression exp = new SQLExpression(DatabaseFactory.getDatabase()); exp = exp.and("id", SQLExpression.Operator.GREATEREQUAL, 1000);
exp = exp.and("id", SQLExpression.Operator.LESSEQUAL, 2000);
ResultIterator iter = c.query(exp.toString());
Customer[] result = (Customer[])iter.toArray();

Sometimes a query-by-example is generally desirable but there are some additional constraints to be considered. For these cases, the function getConstraint() allows to use PriDE's dynamic SQL generation as far as possible and to assemble only the additional constraint. The following example demonstrates this for a query which causes the matching records to be blocked for update:

Customer c = new Customer();
c.setName("L%");
String constraint = c.constraint(new String[] { "name" }, true);
ResultIterator iter = c.query(constraint + " FOR UPDATE");
Customer[] result = (Customer[])iter.toArray();

Of course, this approach is limited to results which can be mapped to entities. The more a query varies from this principle, the less support it gains from PriDE. In extreme cases the function Database.sqlQuery(String) must be used to self-define a query completely in all details. In worst case, the function Database.getConnection() can be used to exploit the full flexibility of ordinary JDBC programming. When using this function, it must be kept in mind that the fetched connection must be released by Database.releaseConnection(Connection) after the work is done. The ResourceAccessors ensure that repeated calls of getConnection() and releaseConnection(Connection) are very efficient and perform thread-based connection reuse.

Nevertheless, PriDE's basic query concept allows to run very different queries even if this is not obvious on first sight. The following example demonstrates a query for the highest ID of all Customer records, using a modified RecordDescriptor.

// Commonly usable class for attribute maximum calculation
public class MaxDescriptor extends RecordDescriptor {
    private String maxfield; // The database field to get the maximum of
    private Integer max;     // The maximum found
    public Integer getMax() { return max; }
    public void setMax(Integer max) { this.max = max; }

    // MaxDescriptor defines itself as the sink for query results.
    public MaxDescriptor(RecordDescriptor baseDescriptor, String maxfield)
        throws IllegalDescriptorException {
        super(MaxDescriptor.class, baseDescriptor.getTableName(), null,
              new String[][]{ {null, "getMax", "setMax"} });
        this.maxfield = maxfield;
    }

    // As a difference to an ordinary record descriptor, MaxDescriptor
    // does not ask the database for '*' (i.e. all fields) but for the
    // maximum of a particular field
    protected String getResultFields() { return "max(" + maxfield + ")"; }

    public static int getMax(String field, String where, RecordDescriptor red)
        throws SQLException {
        MaxDescriptor maxdesc = new MaxDescriptor(red, field);
        DatabaseFactory.getDatabase().query(where, maxdesc, maxdesc, false);
        return (maxdesc.getMax() == null) ? -1 : maxdesc.getMax().intValue();
    }
}
class Customer extends MappedObject {
    // Attributes and descriptor as always

    public int getMaxID() throws SQLException {
        return MaxDescriptor.getMax("id", null, getDescriptor());
    }
}

The complete source code of the examples above can be found in examples/query.

Home

Introduction

Javadoc

PriDE Design Patterns: Extended and complex queries

PriDE Design Patterns:
Extended and complex queries