Finder and ejbSelect Methods

Finder and ejbSelect methods provide an mechanism for locating entities in a persistent store without having to write code to access it natively. The methods allow the programmer to execute the queries; the definition of the query and how it is mapped to the store is defined in the deployment descriptor.

Finder Methods

Finders are declared on the home or local home interface of an Entity EJB. With a CMP entity, no method is defined in the EJB implementation; instead the implementation of the method is provided by CMP. Finders always return the Entity with which they are associated, either a single instance, a Set of instances, or a Collection of instances.

For a single-valued finder, the method returns a reference to a single entity, or null. If the result of the finder's query is empty, then the finder will throw an ObjectNotFoundException; if the result contains more than one row then a FinderException will be thrown. A null value is returned if the query returned one result and the value of that result is a null value.

A multi-valued finder will always return a Set or Collection of references but this result will be empty if the query result was empty. The result may contain null values if the underlying query returned a null result. A Collection result may also contain duplicate values unless the query explicitly specifies DISTINCT.

A finder method using EJB-QL is declared in ejb-jar.xml using a query element:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<query-method>
<method-name>findByStatus</method-name>
<method-params>
<method-param>int</method-param>
</method-params>
</query-method>
<ejb-ql>
<![CDATA[ SELECT OBJECT(o) FROM Order AS o WHERE o.status = ?1 ]]>
</ejb-ql>
</query>
</entity>

The use of a CDATA section is recommended to avoid the need to quote characters like "<" which are common in queries.

If the query for the finder cannot be specified in EJB-QL, then the ejb-ql element should be left empty and the description element used to provide a description of the desired result. The definition of the query would then be provided in jbosscmp-jdbc.xml as described below.

ejbSelect Methods

ejbSelect methods provide a mechanism for an entity to execute queries itself. ejbSelect methods are not constrained like finders to only returning a reference to the same entity; they can return a reference to any entity, a value from cmp-field, or a reference to another entity obtained from a single-valued cmr-field. They can also return Sets or Collections of any of these.

Because an ejbSelect method is not associated with a home or local home interface, the type of reference returned may need to be specified using a result-type-mapping element. The default is to return a reference to the local component interface.

If the query for a single-valued ejbSelect method returns no results, then an ObjectNotFoundException will be thrown; if it returns more than one result then a FinderException will be thrown. A multi-valued ejbSelect method will always return a Set or Collection but this will be empty if the query result was empty. Null values may be returned if the value in the query result was null.

An ejbSelect method is declared in ejb-jar.xml using a query element just like a finder:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<query-method>
<method-name>ejbSelectLineItems</method-name>
<method-params>
<method-param>int</method-param>
</method-params>
</query-method>
<result-type-mapping>Remote</result-type-mapping>
<ejb-ql>
<![CDATA[ SELECT OBJECT(l) FROM Order AS o, IN(o.lineItems) AS l WHERE o.id = ?1 ]]>
</ejb-ql>
</query>
</entity>

This query returns a list of remote references to the LineItem EJBs for a specific order. This can be useful as it allows the relationship between Order and LineItem to be used without having to instantiate the actual Order EJB. Although ejbSelect method cannot be exposed directly, a common technique is to use an ejbHome method to allow access from clients:

public interface OrderHome extends EJBHome {
/**
* Return the LineItems for an order
*/
public Set getLineItems(int orderId) throws FinderException, RemoteException;
}

public abstract class OrderEJB implements EntityBean {
public Set ejbHomeGetLineItems(int orderId) throws FinderException {
return ejbSelectLineItems(orderId);
}

public abstract Set ejbSelectLineItems(int orderId) throws FinderException;
}

JBoss Extensions to EJB-QL

There are several limitations to EJB-QL, especially compared to native SQL. For example, EJB-QL does not support basic functions like UCASE or LCASE, the use of parameters in many places, or the ability to sort the query result. JBoss has an enhanced compiler that provides access to these, and other, capabilities; for full details on the query language extensions see JBoss-QL.

To activate these extensions, a query definition can be provided in a query element in jbosscmp-jdbc.xml which will override the value specified in the ejb-ql element in ejb-jar.xml; in fact, the specification recommends that in these circumstances the ejb-ql element is left empty and the description tag be used to document the desired result.

For example, a query that returned Orders sorted by ship date could be define in ejb-jar.xml as:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<description>Return all orders sorted by ship date, most recent first</description>
<query-method>
<method-name>findSortedByDate</method-name>
<method-params/>
</query-method>
<ejb-ql/>
</query>
</entity>

and in jbosscmp-jdc.xml as:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<query-method>
<method-name>findSortedByDate</method-name>
<method-params/>
</query-method> <jboss-ql>
<![CDATA[ SELECT OBJECT(o) FROM Order o ORDER BY shipDate DESC ]]>
</jboss-ql>
</query>
</entity>

Using Declared SQL

Although JBoss-QL provides enhancements over EJB-QL, in certain circumstances the query needs to be specified using the native SQL dialect of the underlying database. JBoss needs to be able to modify the SQL statement, for example to add read-ahead fields, so rather than a simple string, the statement is built up from a series of elements:

select Used to specify the SELECT clause.
select/distinct Indicates that a SELECT DISTINCT statement should be used to eliminate duplicate values.
select/ejb-name Only valid for ejbSelect statements, indicates the EJB which should be returned from the query.
select/field-name Only valid for ejbSelect statements with an ejb-name, indicates the cmp-field or cmr-field of that EJB that should be returned.
select/alias The alias to use for the main table. For a finder, this is the table for the EJB that declared the finder; for an ejbSelect it is the table for the EJB declared in the select/ejb-name element.
select/additional-columns Text that is appended to the columns in the SELECT clause. This can be used to ensure specific columns are included in the SELECT; for example, some databases require every column used in an ORDER BY clause to be declared in the select list.
from Used to specify additional tables in the FROM clause; should start with a "," or JOIN operator
where Used to specify the WHERE clause
order Used to specifiy an ORDER BY clause
other Text appended to the end of the query; for example, a LIMIT OFFSET clause in MySQL

Method parameters can be referenced in the SQL text using the syntax "{n}" where n is the index of the argument (starting at zero).

As an example, the following declaration in jbosscmp-jdbc.xml could be used to select orders over a certain total value:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<query-method>
<method-name>ejbSelectWithValueOver</method-name>
<method-params>
<method-param>double</method-param>
</method-params>
</query-method>
<declared-sql>
<select>
<distinct/>
<ejb-name>Order</ejb-name>
<alias>o</alias>
<additional-columns>, SUM(p.PRICE*l.QUANTITY)
</select>
<from> LEFT JOIN LINEITEM l ON (o.ORDER_ID = l.FK_ORDER_ID)
INNER JOIN PRODUCT p ON (l.FK_PRODUCT_ID = p.PRODUCT_ID)</from>
<where> <![CDATA[ o.ORDER_ID = o.ORDER_ID GROUP BY ORDER_ID HAVING SUM(p.PRICE*l.QUANTITY) > {0} ]]>
</where>
<order>SUM(p.PRICE*l.QUANTITY) DESC</order>
</declared-sql>
</query>
</entity>

This would result in the SQL text:

SELECT o.ORDER_ID, SUM(p.PRICE*l.QUANTITY)
FROM ORDER_DATA o
LEFT JOIN LINEITEM l ON (o.ORDER_ID = l.FK_ORDER_ID)
INNER JOIN PRODUCT p ON (l.FK_PRODUCT_ID = p.PRODUCT_ID)
WHERE o.ORDER_ID = o.ORDER_ID
GROUP BY ORDER_ID
HAVING SUM(p.PRICE*l.QUANTITY) > ?
ORDER BY SUM(p.PRICE*l.QUANTITY) DESC

JBoss Dynamic Queries

In some cases, the query to be executed may not be known at deployment time. A frequent example of this is when the query is being used to search for entities based on information entered in a form. The most efficient way to execute this is to build up the query based on the fields entered in the form but EJB-QL does not allow such dynamic behaviour.

JBoss supports this through the use of dynamic ejbSelect methods. Such a method is declared using a dynamic-ql tag in jbosscmp-jdbc.xml; in ejb-jar.xml the ejb-ql element should be left empty. A dynamic method takes two parameters: a String of JBoss-QL that should be executed, and an Object array containing parameters.

In ejb-jar.xml, the method would be declared as:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<description>Dynamic query to support search form</description>
<query-method>
<method-name>ejbSelectSearchForm</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Object[]</method-param>
</method-params>
</query-method>
<ejb-ql/>
</query>
</entity>

In jbosscmp-jdbc.xml, the dynamic-ql tag is used to indicate this is a dynamic query:

    <entity>
<ejb-name>Order</ejb-name>
<query>
<query-method>
<method-name>ejbSelectSearchForm</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Object[]</method-param>
</method-params>
</query-method>
<dynamic-ql/>
</query>
</entity>

An example of code that uses this method could be:

public interface OrderHome extends EJBObject {
public Set search(OrderSearchForm form) throws FinderException, RemoteException;
} public abstract class OrderEJB implements EntityBean {
public Set ejbHomeSearch(OrderSearchForm form) throws FinderException {
StringBuffer where = new StringBuffer();
List args = new ArrayList();
if (form.status != null) {
where.append(" o.status = ?");
args.add(form.status);
}
if (form.shipToState != null) {
where.append(" o.shipAddress.state = ?");
args.add(form.shipToState);
}
// and any other fields ...

StringBuffer query = new StringBuffer(); query.append("SELECT OBJECT(o) FROM Order o ");
if (where.length() > 0) {
query.append("WHERE").append(where);
}

return ejbSelectSearchForm(query.toString(), args.toArray());
}

public abstract Set ejbSelectSearchForm(String query, Object[] params) throws FinderException {
}
}

JBoss BMP Finders

When all else fails, JBoss allows a CMP entity to implement a finder using BMP code. This provides the greatest flexibility but exposes the Bean Provider to the issues of dealing with the store. If the EJB provides an implementation of the ejbFind method for a finder, then JBoss will call that method rather than executing a query itself.

Defining such a method is strictly a violation of the EJB Specification and as such will result in a warning at deployment time; this warning can be ignored.

One simple programming trick uses such a method to convert a list of keys to a list of references without having to contact the database:

public interface OrderHome extends EJBHome {
public Collection findByKeys(Collection keys) throws FinderException, RemoteException;
}
public abstract class OrderEJB implements EntityBean {
public Collection ejbFindByKeys(Collection keys) throws FinderException {
return keys;
}
}