5
Defining and Executing Finders

TopLink provides a feature-rich query framework in which complex database queries can be constructed and executed to retrieve entity beans. TopLink Container-Managed Persistence enables you to define the finder methods on the home interface, but does not require you to implement them in the entity bean. TopLink Container-Managed Persistence provides this required functionality, and offers a number of strategies for creating and customizing finders. The EJB container and TopLink automatically generate the implementation.

Defining finders in TopLink

The general steps required to successfully define a finder method for an entity bean using TopLink Container-Managed Persistence's query framework are as follows:

1. Declare finders in the ejb-jar.xml file.

2. Define the finder method on the entity bean's home and/or local home interface(s) (as required by the EJB specification)

3. Use the Mapping Workbench to change any options on finders.

4. If required, create an implementation for the query. Some query options require that the query be defined in code on a helper class, but this is not required for most queries.

ejb-jar.xml Finder Options

The ejb-jar.xml file specifies all of the EJB 2.0 specification related information for a bean, including the definitions for any finders that are to be used for that bean. The ejb-jar.xml file may be created and edited using a text editor, or it may be created using the Mapping Workbench. All finders are defined within the ejb-jar.xml file with a structure similar to the following example.

Example 5-1 A simple finder within an ejb-jar.xml file

...
<query>
   <query-method>
      <method-name>findLargeAccounts</method-name>
         <method-params>
         <method-param>double</method-param>
         </method-params>
   </query-method>
   <ejb-ql><![CDATA[SELECT OBJECT(account) FROM AccountBean account WHERE account.balance > ?1]]></ejb-ql>
</query>
...

Query Section - XML Elements

The ejb-jar.xml file can contain zero or more <query> elements in the <entity> tag. Each one of these <query> tags corresponds to a finder method that is defined on the bean's home or local home interface. If you define the same finder (same name, return type, and parameters) on both home interfaces, then only a single <query> element is defined in the ejb-jar.xml file and they must share the same TopLink query.

The elements that are defined in the <query> section of the ejb-jar.xml file are:

• description (optional)- Used to provide text describing the finder.

• query-method - Used to specify the method for a finder or ejbSelect query.

• method-name - Specifies the name of a finder or select method in the entity bean's implementation class.

• method-params - Contains a list of the fully-qualified Java type names of the method parameters.

• method-param - Contains the fully-qualified Java type name of a method parameter.

• result-type-mapping (optional)- Used in the query element to specify whether an abstract schema type returned by a query for an ejbSelect method is to be mapped to an EJBLocalObject or EJBObject type. Valid values are Local or Remote

• ejb-ql - Used for all finders that can be expressed using EJB QL. It contains the EJB QL query string that defines a finder or ejbSelect query. This parameter is left empty for non-EJBQL finders.

Choosing the best finder type for your query

TopLink supports five general types of finders:

• EJBQL queries

• Expressions built using the TopLink expression framework

• Dynamic queries

• redirect queries

• SQL queries

Most finders can be defined using the EJBQL mechanism. However, the other mechanisms have their own advantages:

• Expression finders give the user access to TopLink's expression framework.

• If dynamic querying is required (the query logic must be defined at run time) then one of the Dynamic types may be used.

• SQL and redirect queries are provided for those rare cases where no other mechanism is suitable.

For more information about defining finders, see the Oracle9iAS TopLink Mapping Workbench Reference Guide.

Using EJBQL

EJBQL is the standard query language defined in the EJB 2.0 specification and is available for use in TopLink with both 1.1 and 2.0 beans. EJBQL finders enable a specific EJBQL string to be specified as the implementation of the query.

Advantages

EJBQL offers several advantages in that it:

• is the EJB 2.0 standard for queries

• can be used for most queries

• can be used in dependent object queries

Disadvantages

Some complex queries may be difficult to define using EJBQL.

Creating an EJBQL finder

To create an EJBQL finder

1. Declare the finder in the ejb-jar.xml and enter the EJBQL string in the ejb-ql tag.

2. Declare the finder on the Home interface, the LocalHome interface, or both as required.

3. Start the Mapping Workbench.

4. Specify the ejb-jar.xml location and select File > Updated Project from ejb-jar.xml to read in the finders.

5. Go to the Queries > Named Queries tab for the bean.

6. Select and configure the finder.

Following is an example of a simple EJBQL query that takes one parameter. In this example, the question mark ("?") is used to bind the argument name within the EJBQL string.

SELECT OBJECT(employee) FROM Employee employee WHERE (employee.name =?1)

Notes: The argument (bolded in the example) must be a numeric value. Employee (bolded in the example) refers to the <abstract-schema-name> defined for that particular bean.

For more information on EJBQL, see the Oracle9iAS TopLink Foundation Library Guide.

Using the TopLink Expression framework

Finders can take advantage of TopLink's rich expressions framework to define the logic of the query.

Advantages

Using TopLink expressions to access the database has some advantages over using EJBQL:

• Standardizes queries to Java code, which can be version controlled.

• Can simplify complex operations.

• Fuller set of querying features than is available through EJBQL.

Example

A sub-query expression using a comparison and count operation

This code queries all employees that have more than 5 managed employees.

ExpressionBuilder emp = new ExpressionBuilder();
ExpressionBuilder managedEmp = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Employee.class, managedEmp);
subQuery.addCount();
subQuery.setSelectionCriteria (managedEmp.get("manager").equal(emp));
Expression exp = emp.subQuery(subQuery).greaterThan(5);

Note: This type of query is only possible using the TopLink expression framework.

Disadvantages

The disadvantages to using TopLink Expressions in finders are:

• The Mapping Workbench does not support the TopLink Expression framework, so the Expression finders must be created in code using a project class or descriptor amendment.

Creating an Expression Finder

1. Declare the finder in the ejb-jar.xml and leave the ejb-ql tag empty. This step is optional but should be performed in order to maintain compliance with the EJB 2.0 spec.

2. Declare the finder on the Home interface, the LocalHome interface, or both as required.

3. Create an amendment method as described in "Creating amendment methods for Expression finders".

4. Start the Mapping Workbench.

5. Select Advanced Properties > After Load from the menu for the bean.

6. Enable the amendment method for the descriptor by specifying the class and name of the static method.

Building an expression

An example of an expression is as follows:

builder.get("address").get("city").equal(theCity);

This represents the query logic, or the "selection criteria" for the query. The logical translation for this query is:

Find all Employees whose address attribute's city attribute is equal to the value passed in as an argument when the finder method is invoked.

To introduce the basics of constructing a query expression, examine each element of this expression:

• builder: Represents an instance of TopLink's ExpressionBuilder (oracle.toplink.expressions.ExpressionBuilder), and represents the entry point for defining an expression.

• get: The Get predicate allows comparison of attributes within the expression (in this case, the city attribute of the entity bean's address attribute). If the attribute is
  • a target of a 1:1 relationship, the relationship is traversed and subsequent predicates can access attributes of the related object. For example:

  • get("address").get("city")

  • a target of a one-to-many (1:M) or many-to-many (M:M) relationship, the predicate anyof can be used to apply selection criteria to any of the related objects, resulting in multiple objects returned equal(theCity), where the equal operator describes the operation to be applied on the attribute.

  In this case, city is compared based on equality to the parameter theCity, which represents the first argument passed into the finder method.

• equal(theCity): The equal qualifier represents the string or value against which records are being compared. The equal operator describes the operation to be applied on the attribute. In this case, city is compared based on equality to the parameter theCity, which represents the first argument passed into the finder method.

• ("address") and ("city"): These components represent nested attributes within the data. Multiple get predicates indicate a logical comparison that mirrors the structure of the data. Consider the two gets in the example:

  builder.get("address").get("city")

The first get in this statement specifies the address attribute, while the second get examines city, which is an attribute of the attribute, address.

Creating amendment methods for Expression finders

A TopLink Expression query must first be implemented and then registered with the runtime within a "TopLink descriptor amendment" method. Define the named query in the static amendment method, and add the query to the TopLink descriptor's QueryManager. The named query must be defined based on the following:

• The name of the query must match the name of the finder on the home interface.

• If the return type for the finder method on the home interface is java.util.Collection, then the query object defined must be a oracle.toplink.queryframework.ReadAllQuery.

• If the return type is an entity bean's remote or local interface (that is, only a single entity bean is returned), then the query must be of type oracle.toplink.queryframework
  .ReadObjectQuery.

• The reference class must be the bean class against which the finder is querying.

• The arguments defined in the query must exactly match the parameter name and type of the corresponding finder declared on your home interface. This includes the argument names, the number of arguments, as well as the order in which they occur.

For more information on configuring an amendment method, see the Oracle9iAS TopLink Mapping Workbench Reference Guide.

Using Dynamic finders

The EJB 2.0 specification allows for finders defined in the ejb-jar.xml file as queries, with their search criteria specified as EJBQL query strings. TopLink expands on the specification, enabling you to create queries using other query formats such as SQL, expressions, dynamic query objects, and Redirects (see "Choosing the best finder type for your query").

In addition to this support, TopLink provides a number of predefined finders that can be used for executing dynamic queries (queries for which the logic is determined by the user at run-time). The names for these finders are reserved by the TopLink runtime and cannot be reused for other finders.

The predefined finders are:

• EJBObject findOneByEJBQL(String ejbql, Vector args)

• Collection findManyByEJBQL(String ejbql, Vector args)

• EJBObject findOneBySQL(String sql, Vector args)

• Collection findManyBySQL(String sql, Vector args)

• EJBObject findOneByQuery(DatabaseQuery query, Vector args)

• Collection findManyByQuery(DatabaseQuery query, Vector args)

Each of these finders can also be used without the Vector of arguments. For example, EJBObject findOneByEJBQL(String ejbql) is a valid dynamic finder. The return type of "EJBObject" is replaced by the component interface of your bean.

Creating a Dynamic finder

To create a Dynamic finder

1. Declare the finder in the ejb-jar.xml file and leave the ejb-ql tag empty.

2. Declare the finder on the Home interface, the LocalHome interface, or both as required.

3. Start the Mapping Workbench.

4. Specify the ejb-jar.xml location and select File > Updated Project from ejb-jar.xml to read in the finders.

5. Go to the Queries > Named Queries tab for the bean.

6. Select and configure the finder.

   Notes: If the advanced query options described in "Advanced finder options" are not required, only steps 1 and 2 needs to be completed. The findOneByQuery and findManyByQuery dynamic finders should not have any query options configured for them. The reason is the query is created at runtime by the client and passed as a parameter to the finder. Any query options that you wish to set should be done on that query.

Using findAll

Like the dynamic finders, the name findAll is reserved by the TopLink runtime and cannot be reused for other finders. For more information on defining and configuring the finder, see "Creating a Dynamic finder".

Using findByPrimaryKey

The findByPrimaryKey finder is always created in the Mapping Workbench on the initial loading of a bean class. Like other finders, the findByPrimaryKey finder can be configured with the various query options that TopLink provides (see "Advanced finder options") but can also be deleted from the Mapping Workbench project. In this case, however, a warning is issued informing the user that the default container findByPrimaryKey options will be active. The EJB 2.0 specification requires that the findByPrimaryKey call is present on the home interface, but should not have a query entry in the ejb-jar.xml file.

Using redirect finders

Redirect finders enable you to specify a finder for which the implementation is defined in code as a static method on an arbitrary helper class. When the finder is invoked, the call is re-directed to the specified static method.

The finder can have any arbitrary parameters or none at all. If the finder includes parameters, they are packaged into a vector and passed to the redirect method.

Advantages

Redirect finders provide client parameter-passing flexibility. Compared to other finder types in which the parameters are relatively simple objects used to match against an entity bean's attributes, redirect finders may include arguments that are not linked to these values, because the finder implementation is completely defined by the bean developer. The redirect method typically contains the logic required to extract the relevant data from the parameters and use it to construct a TopLink query.

Disadvantages

Redirect queries are complex and often more difficult to configure. they also require an extra helper method to define the query.

To create a redirect finder

1. Declare the finder in the ejb-jar.xml leaving the ejb-ql tag empty.

2. Declare the finder on the Home interface, the localHome interface, or both as required.

3. Create an amendment method (see "Creating amendment methods for Expression finders").

4. Start the Mapping Workbench.

5. Select Advanced Properties > After Load from the menu for the bean.

6. Enable the amendment method for the descriptor by specifying the class and name of the static method.

The amendment method should then add a query to the descriptor's QueryManager as follows:

ReadAllQuery query = new ReadAllQuery();query.setRedirector(new
MethodBaseQueryRedirector (examples.ejb.cmp20.advanced.
FinderDefinitionHelper.class, "findAllEmployeesByStreetName"));
descriptor.getQueryManager().addQuery ("findAllEmployeesByStreetName", query);

examples.ejb.cmp20.advanced.FinderDefinitionHelper includes a static method findAllEmployeesByStreetName(Session session, Vector args) which executes the query. It is up to the implementor of the query method to ensure that the proper types are returned. For methods returning more than one bean, the return type must be java.util.Vector. TopLink converts this result to java.util.Enumeration (or Collection) if required.

Note: The redirect method also takes a TopLink Session as a parameter. For more information on TopLink Session, see "Database Sessions" in the Oracle9iAS TopLink Foundation Library Guide.

The redirect method must return either a single entity bean (Object) or a Vector. The possible method signatures are:

• public static Object redirectedQuery2(oracle.toplink.sessions.Session s, Vector args)

• public static Vector redirectedQuery4(oracle.toplink.sessions.Session s, Vector args)

Example 5-2 A simple Redirect query implementation:

public static Vector findAllEmployeesByStreetName(Session s, Vector args) {
   ReadAllQuery raq = new ReadAllQuery();
   raq = raq.setReferenceClass(EmployeeBean.class);
   raq.addArgument("streetName");
   ExpressionBuilder builder = newExpressionBuilder();
   .raq.setSelectionCriteria(builder.get("address")
   .get("street").equal(args.elmentAt(0)));
   return (Vector)s.executeQuery(raq);
}

At run time when the client invokes the finder from the entity bean's home, the arguments are automatically packaged into the args Vector (in order of appearance from the finder's method signature) for use within the static method. The code implementing the Redirect finder can then use any necessary APIs to extract information out of the arguments (once retrieved from the args Vector) for use within a TopLink expression.

Using SQL

SQL type finders allow a specific SQL string to be specified as the implementation of the query.

Advantages

The advantages of using SQL include:

• It can be used if the query logic cannot be expressed using EJBQL or the TopLink expression framework.

• It allows for the use of a stored procedure instead of TopLink generated SQL

• There may be cases where custom SQL will improve performance.

Disadvantages

This approach is generally not recommended if the query can be created using any of the other options, because

• Writing complex custom SQL statements requires a significant maintenance effort if the database tables change.

• The hard coded SQL limits portability to other databases.

• No validation is done on the SQL String, so if the SQL has errors, these will not be detected until run time.

• If the SQL does something other than SELECT, unpredictable errors may result.

Creating an SQL finder

To create an SQL finder

1. Declare the finder in the ejb-jar.xml and leave the ejb-ql tag empty.

2. Start the Mapping Workbench.

3. Specify the ejb-jar.xml location and select File > Updated Project from ejb-jar.xml to read in the finders.

4. Go the Queries > Named Queries tab for the bean.

5. Select the finder, check the SQL radio button and enter the SQL string.

6. Configure the finder.

Following is an example of a simple SQL finder that takes one parameter. In this example, the hash-character '#' is used to bind the argument projectName within the SQL string.

SELECT * FROM EJB_PROJECT WHERE (PROJ_NAME = #projectName)

Using ejbSelect

ejbSelects are similar to finders in function, but they can only be invoked from within a bean. Like finders, ejbSelects are defined in the ejbjar.xml using a <query> entry and can have various options configured using the Mapping Workbench. Also like finders, ejbSelects require a SELECT clause in addition to FROM and WHERE clauses.

However, ejbSelects differ from regular finders in the following ways:

• ejbSelects require an additional <result-type-mapping> tag, which specifies whether the return type is intended to be Local or Remote (the default is Local).

• ejbSelects are not defined on the bean home, but rather on the bean itself. These are not exposed to the client, but are used internally by bean business methods and by home methods.

• ejbSelect queries can return other return types other than the entity bean type on which they are invoked, and in fact may return any type corresponding to a container-managed relationship or container-managed field.

Understanding select methods

Select methods are query methods intended for internal use within an entity bean instance. Unlike finder methods, select methods are not specified in the entity bean's home interface but on the abstract bean itself.

The format for an ejbSelect method definition looks like this:

public abstract type ejbSelect<METHOD>(...);

The select method represents a query method that is not directly exposed to the client in the home or component interface. It is defined as being abstract, and each bean can include zero or more such methods.

Even though the select method is not based on the identity of the entity bean instance on which it is invoked, it can use the primary key of an entity bean as an argument to an ejbSelect<METHOD> to define a query that is logically scoped to a particular entity bean instance.

Select methods have the following characteristics:

• The method name must have ejbSelect as its prefix.

• It must be declared as public.

• It must be declared as abstract.

• The throws clause must specify the javax.ejb.FinderException, although it may also specify application-specific exceptions.

The return type for ejbSelects that return entities is determined by the <result-type-mapping> tag in the ejb-jar.xml. If the flag is set to Remote, then EJBObjects are returned; if set to Local, then EJBLocalObjects are returned.

Creating an ejbSelect

1. Declare the ejbSelect in the ejb-jar.xml, enter the EJBQL string in the <ejb-ql> tag, and specify the return type in the <result-type-mapping> tag (if required).

2. Declare the ejbSelect on the abstract bean class.

3. Start the Mapping Workbench.

4. Specify the ejb-jar.xml location and select File > Updated Project from ejb-jar.xml to read in the finders.

5. Go the Queries > Named Queries tab for the bean.

6. Select and configure the ejbSelect query.

Advanced finder options

There are a number of options that can be used by the experienced TopLink developer. These options should only be used when the developer has a complete understanding of the consequences of making changes to them.

Caching options

Various configurations can be applied to the underlying query to achieve the correct caching behavior for the application. There are several ways to control the caching options for queries.

For most queries, caching options can be set in the Mapping Workbench (see "Caching objects" in the Mapping Workbench Reference Guide).

The caching options can be set on a per-finder basis. The valid values are:

• ConformResultsInUnitOfWork (default): For finders returning a single result and finders returning a collection, the 'UnitOfWork' cache for the current JTS UserTransaction is queried. The finder's results will conform to uncommitted new objects, deleted objects and changed objects.

• DoNotCheckCache: For finders returning a single object and finders returning a collection, the cache is not checked.

• CheckCacheByExactPrimaryKey: If a finder returning a single object involves an expression that contains the primary key and only the primary key, the cache is checked.

• CheckCacheByPrimaryKey: If a finder returning a single object involves an expression that contains the primary key, a cache hit can still be obtained through processing the expression against the object in the cache.

• CheckCacheThenDatabase: A finder returning a single object queries the cache completely before resorting to accessing the database.

• CheckCacheOnly: For finders returning a single object and finders returning a collection, only the cache is checked; the database is not accessed.

For more information about TopLink queries as well as the TopLink UnitOfWork and how it integrates with JTS, see "Database Sessions" in the Oracle9iAS TopLink Foundation Library Guide.

Note: For finders whose queries are manually created (findOneByQuery, findManyByQuery), caching options must be applied manually using TopLink for Java APIs.

Disabling caching of returned finder results

By default, TopLink adds to the cache all returned objects whose primary keys are not currently in the cache. This can be disabled if the client knows that the set of returned objects is very large and wants to avoid the expense of storing these objects. This option is configurable through the Mapping Workbench or on the TopLink query API for queries using dontMaintainCache().

Caching of returned finder results can also be disabled in the Mapping Workbench. For more information on disabling caching for returned finder results, see the Oracle9iAS TopLink Mapping Workbench Reference Guide.

Refreshing finder results

A finder may return information from the database for an object whose primary key is already in the cache. When set to true, the refresh cache option in the Mapping Workbench indicates that the object's non-primary key attributes are refreshed with the returned information. This occurs on findByPrimaryKey finders as well as all EXPRESSION and SQL finders for that bean when set at the bean attributes level.

When refreshing is enabled, the refreshIdentityMapResult() method is invoked on the query. This is configured to automatically cascade private parts. If behavior other than private object cascading is desired, use a dynamic finder.

Caution: When issuing refreshing finders while in user transactions, refreshing the object may cause changes already made to that object during that transaction to be lost.

In the case where an OptimisticLock field is in use, the refresh cache option can be used in conjunction with the onlyRefreshCacheIfNewerVersion() option. In that case, the non-primary key attributes are refreshed only if the version of the object in the database is newer than the version in the cache.

For finders that have no refresh cache setting, the onlyRefreshCacheIfNewerVersion() method has no effect.

Managing large result sets

Finders can return large result sets which can be resource intensive to collect and process. To give the client more control over the returned results, TopLink finders can be configured to use cursors. This leverages TopLink's CursoredStream and a database's cursoring ability to break up the result set into smaller, more manageable pieces.

Building the query

Any finder that returns a java.util.Enumeration under EJB 1.1 or a java.util.Collection under EJB 2.0 can be configured to use a cursor. When the query is created for the finder, useCursoredStream() enables cursoring.

Example

A query that uses a CursoredStream

ReadAllQuery raq = new ReadAllQuery();
ExpressionBuilder bldr = new ExpressionBuilder();
raq.setReferenceClass(ProjectBean.class);
raq.useCursoredStream();
raq.addArgument("projectName");
raq.setSelectionCriteria(bldr.get("name").
like(bldr.getParameter("projectName")));
descriptor.getQueryManager().addQuery ("findByNameCursored");

Executing the finder from the client in EJB 1.1

An extended protocol is available on the client in oracle.toplink.ejb.cmp.wls11.CursoredEnumerator (based on java.util.Enumeration):

hasMoreElements()

As with java.util.Enumeration, this method returns a boolean indicating if any elements remain.

nextElement()

As with java.util.Enumeration, this method returns the next available element.

nextElements(int count)

Retrieve a Vector of at most count elements from the available results, depending on how many elements are left to read.

close()

close the cursor on the server. It is mandatory that the client send this message when it is done with the results.

The behavior differs from a normal finder as follows:

• Only the elements requested by the client are sent to the client.

• Nothing is cached on the client in the CursoredEnumerator.

• If you are using the transactional attribute REQUIRED for your entity bean, all reads must be wrapped in a UserTransaction begin() and commit(). If not, reads beyond the first page of the cursor will have no transaction in which to work.

The following example illustrates client-code executing a cursored finder:

import oracle.toplink.ejb.cmpwaswls11. CursoredEnumerator;
//... other imports as necessary
getTransaction().begin();
CursoredEnumerator cursoredEnumerator = (CursoredEnumerator)getProjectHome()
 .findByNameCursored("proj%");

Vector projects = new Vector();
for (int index = 0; index < 50; i++) {
   Project project = (Project)cursoredEnumerator.nextElement();
   projects.addElement(project);
}

// Rest all at once ...
Vector projects2 = cursoredEnumerator.nextElements(50);
cursoredEnumerator.close();
getTransaction().commit();

Executing the finder from the client in EJB 2.0

An extended protocol is available for the client in oracle.toplink.ejb.cmp.wls.CursoredCollection (based on java.util.Collection):

isEmpty()

As with java.util.Collection, isEmpty() returns a boolean indicating if the Collection is empty or not.

size()

As with java.util.Collection, size() returns an integer which is the number of elements in the Collection.

iterator()

As with java.util.Collection, iterator() returns a java.util.Iterator for enumerating the elements in the Collection.

An extended protocol is also available for oracle.toplink.ejb.cmp.wls.CursoredIterator (based on java.util.Iterator):

close()

closes the cursor on the server. It is mandatory that the client send this message when it is done with the results.

hasNext()

returns a boolean indicating if there is a next element.

next()

returns the next available element.

next(int count)

retrieves a Vector of at most count elements from the available results, depending on how many elements are left to read.

This behavior differs from a normal finder as follows:

• Only the elements requested by the client are sent to the client.

• Nothing is cached on the client.

• If you are using the transactional attribute REQUIRED for your entity bean, all reads must be wrapped in a UserTransaction begin() and commit(). If not, reads beyond the first page of the cursor will have no transaction in which to work.

The following example illustrates client-code executing a cursored finder

//import both CursoredCollection and CursoredIterator
import oracle.toplink.ejb.cmp.wls.*;
//... other imports as necessary
getTransaction().begin();
CursoredIterator cursoredIterator = (CursoredIterator)getProjectHome().findByNameCursored("proj%")
 .iterator();
Vector projects = new Vector();
for (int index = 0; index < 50; i++) {
   Project project = (Project)cursoredIterator.next();
   projects.addElement(project); !
}
// Rest all at once ...
Vector projects2 = cursoredIterator.next(50);
cursoredIterator.close();
getTransaction().commit();