Syntax of the WHERE clause

What is the complete syntax of the WHERE clause?

The syntax of a WHERE clause is fairly complex.

First, the "where" literal is optional, and will be automatically supplied if the where clause is not the empty string. For example, "a = b" gets converted to "where a = b" but "" is not modified. This makes it easy to specify findAll, using the empty string, meaning "select [values] from [table]".

We do parameter substitution, using the standard SQL substitution prefix colon. These parameters correspond to the names of the parameters in the original finder specification in the XML descriptor. A simple example is:

  <finder>
    <method-signature>findAccountsLargerThan(float balance)</method-signature>
    <where-clause>balance > :balance</where-clause>
  </finder>

Here, we will compose a SQL select statement using the where clause:

        balance > ?

and we will substitute the value of the parameter "balance" for the ? in the WHERE clause.

Compound parameters are also supported, using the standard dot syntax. For example:

  <finder>
     <method-signature>findByCity(Address address)</method-signature>
     <where-clause>city = :address.city AND state = :address.state</where-clause>
   </finder>

In this WHERE clause, we are using the city and state fields of the address compound object to select particular records. The underlying Address object could either have JavaBeans style getter methods corresponding to the attributes city and state or it could alternatively have public fields corresponding to the attributes.

Entity beans can be used as parameters in the finder method. If not used as a compound type (that is, you want to tell the cmp engine to use a field from the passed refrence for the SQL query), their primary key will be substituted in the WHERE clause. For example, if we have a set of OrderItems associated with an Order object, we could have the following finder:

        java.util.Collection OrderItemHome.findByOrder(Order order);

which returns all OrderItems associated with a particular Order. The WHERE clause for this would be:

        <finder>
          <method-signature>findByOrder(Order order)</method-signature>
          <where-clause>order_id = :order[ejb/orders]</where-clause>
        </finder>

In this WHERE clause, the primary key of the Order object is substituted for the ":order[ejb/orders]" string. The string between the brackets must be the ejb-ref corresponding to the home of the parameter type. In this example, the ejb/orders is an ejb-ref (actually it must be an ejb-link) pointing to the OrderHome.

Alternatively, one can use the EJBObject as a compound type, and access its method in the finder, as in:

        order_id = :order.orderId

This will call the getOrderId() method on the order EJBObject and use the result in the selection criterion.