5
Defining and Executing Queries

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

Using Finder Libraries

The general steps required to successfully define a finder method for an entity bean using TopLink query framework are as follows:

Define the finder method on the entity bean's home interface (as required by the EJB specification).
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 simple queries.

NAMED finders

A NAMED finder refers to a TopLink query that has been registered with the container under a specific name.

Creating NAMED finders

When using NAMED finders, the "find" method on the home interface must correspond to the name of a TopLink query that has been registered with the container. The query is implemented and then registered with the container within a "TopLink descriptor amendment" method or session amendment class.

Example 5-1 A NAMED finder using a TopLink query named findCustomersInCity

 public Enumeration findCustomersInCity(String aCity)throws FinderException,
RemoteException;

Defining "named" TopLink queries

Before the findCustomersInCity finder shown in this example can work, the corresponding named query findCustomersInCity must be defined and made known to TopLink. This can be accomplished several ways, depending on the mechanism used to define the query.

Under EJB QL or SQL

When using EJB QL or SQL to define a finder, you can either define the query in the Mapping Workbench in the bean descriptor's Queries tab, or add the query to the descriptor in a user defined method.

Under the TopLink expression framework

When using the TopLink expression framework the query must be added using a user defined method.

The user defined method can take one of two forms:

A descriptor amendment method specified on the bean descriptor in the TopLink project in the Mapping Workbench
The preLogin method on a session event listener class. Session event listener classes are specified using the <event-listener-class> element in the toplink-ejb-jar.xml descriptor.

Example 5-2 Create a ReadAllQuery

ExpressionBuilder exp = new ExpressionBuilder();
ReadAllQuery = new ReadAllQuery();
query.setReferenceClass(Customer.class);
query.setSelectionCriteria(exp.get("city").equal(exp.getParameter("city")));
query.addArgument("city");

Example 5-3 Define `findCustomersInCity` query in the preLogin method of a session event listener class and specify the session event listener class in the toplink-ejb-jar.xml deployment descriptor.

public void preLogin(SessionEvent event) {
   // create a query...
   event.getSession().getDescriptor(Customer.class).getQueryManager().addQuery("fin
   dCustomersInCity", query);
}

Example 5-4 Define `findCustomersInCity` query in the amendment method of the descriptor

public static void amendment(Descriptor descriptor) {
   // create a query...
   descriptor.getQueryManager().addQuery("findCustomersInCity", query);
}

For more information on creating a named query, see "Query objects" in Chapter 1 of Oracle9iAS TopLink Foundation Library Guide.

Using the TopLink expression framework

Define the named query in the amendment method, and add the query to the TopLink descriptor's QueryManager.

The named query must be defined based on the following:

If the return type for the finder method on the home interface is java.util.Enumeration or Collection, then the query object defined must be a oracle.toplink.QueryFramework.ReadAllQuery. If the return type is an entity bean's remote 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 names and types of the corresponding finder element defined on the home interface.

The arguments defined in the query are retrieved via the builder.getParameter() call and then are used for comparison purposes in conjunction with various predicates/operators: equal(), like(), anyOf(), and so on. For more information, see TopLink: Using the Foundation Library.

Example 5-5 Using the TopLink expression framework

public static void addCustomerFinders(Descriptor descriptor) {
   /* Enumeration findCustomersInCity(String aCity)
   Since this finder returns an Enumeration, the type of the query is
   ReadAllQuery. The finder is a "NAMED" finder. It's implementation
   is a ReadAllQuery that is registered with the QueryManager. */

   //1 the query is defined
   ReadAllQuery query1 = new ReadAllQuery();
   query1.setName("findCustomersInCity");
   query1.addArgument("aCity");
   query1.setReferenceClass(CustomerBean.class);

   //2 an expression is used
   ExpressionBuilder builder = new ExpressionBuilder();
   query1.setSelectionCriteria
   builder.get("city").like(builder.getParameter("aCity";

   //3 An option at this point would be to set any desired options on the query,
   e.g., queryl.refreshIdentityMapResult();

   //4 Finally, the query is registered with the querymanager.
   descriptor.getQueryManager().addQuery("findCustomersInCity",query1);
}

Using the generic NAMED finder

Alternatively you can use a named query without providing the matching implementation on the home interface if you use the generic NAMED finder provided by TopLink for IBM WebSphere Foundation Library. This finder takes the name of the named query and a vector of arguments as parameters.

Example 5-6 The generic NAMED finder

public Enumeration findAllByNamedQuery(String queryName, Vector arguments)
throws RemoteException, FinderException;

CALL finders

CALL finders allow dynamic creation of queries. These dynamic queries are generated at runtime instead of at deployment time. When using a CALL finder, a TopLink SQLCall or StoredProcedureCall is passed as a parameter to a finder that returns an Enumeration.

Creating CALL finders

The implementation for CALL finders is supplied by TopLink for IBM WebSphere. To make this available to your bean you will have to add the following finder definition to the home interface of your bean.

Example 5-7 A CALL query

public Enumeration findAll(Call call) throws RemoteException,
FinderException;

Executing a CALL finder

When using a CALL finder, the call is created on the client using the TopLink interface oracle.toplink.queryFramework.Call. this call has three implementors: EJBQLCall, SQLCall and StoredProcedureCall. Refer to the TopLink: Using the Foundation Library manual for details on creating instances of these classes.

Example 5-8 Executing an CALL finder (select statement)

try {
   SQLCall call = new SQLCall();
   call.setSQLString("SELECT * FROM EMPLOYEE");
   Enumeration employees = getEmployeeHome().findAll(call);
}

Example 5-9 Executing an CALL finder (stored procedure)

try {
   StoredProcedureCall call = new StoredProcedureCall();
   call.setProcedureName("READ_ALL_EMPLOYEES");
   Enumeration employees = getEmployeeHome().findAll(call);
}

EXPRESSION finders

EXPRESSION finders allow dynamic creation of queries. These dynamic queries are generated at runtime instead of at deployment time. When using an EXPRESSION finder, a TopLink Expression is passed as a parameter to a finder that returns an Enumeration.

Creating EXPRESSION finders

The implementation for EXPRESSION finders is supplied by TopLink for IBM WebSphere Foundation Library. To make this available to your bean, you will have to add the following finder definition to the home interface of your bean.

Example 5-10 An EXPRESSION query

public Enumeration findAll(Expression expression) throws RemoteException,
FinderException;

Executing an EXPRESSION finder

When using an EXPRESSION finder, the query is created on the client.

Example 5-11 Executing an EXPRESSION finder

try {
   Expression expression = new
   ExpressionBuilder().get("firstName").like("J%");
   Enumeration employees =
   getEmployeeHome().findAll(expression);
}

EJBQL finders

EJBQL is the standard query language defined in the EJB 2.0 specification and is available for use in TopLink with EJB 1.1 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

Declare the finder on the Home interface.
Start the Mapping Workbench.
Go to the Queries > Finders > Named Queries tab for the bean.
Add a finder and give it the same name as the finder you declared on your bean's home, and add any required parameters.
Select and configure the finder.

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

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

READALL finders

READALL finders allow dynamic creation of queries. These dynamic queries are generated at runtime instead of at deployment time. Using a READALL finder, a TopLink ReadAllQuery is passed as a parameter to a finder that returns an Enumeration.

Creating READALL finders

The implementation for READALL finders is supplied by TopLink for IBM WebSphere Foundation Library. To make this available to your bean, you will have to add the following finder definition to the home interface of your bean.

Example 5-12 A READALL query

public Enumeration findAll(ReadAllQuery query) throws RemoteException,
FinderException;

Executing a READALL finder

When using a READALL finder, the query is created on the client.

Example 5-13 Executing a READALL finder

try {
   ReadAllQuery query = new ReadAllQuery(Employee.class);
   query.addJoinedAttribute("address");
   Enumeration employees = getEmployeeHome().findAll(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 Chapter 4 of the Mapping Workbench Reference Guide). For finders whose queries are manually created (findOneByQuery, findManyByQuery), caching options must be applied manually using TopLink for Java APIs.

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.

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.

DoNotCheckCache

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

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

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 not configurable through the deployment descriptors, but can be configured for queries using dontMaintainCache() on the TopLink query API:

...
ExpressionBuilder bldr = new ExpressionBuilder();
ReadAllQuery raq = new ReadAllQuery();
raq.setReferenceClass(ProjectBean.class);
raq.dontMaintainCache();
raq.addArgument("projectName");
raq.setSelectionCriteria(bldr.get("name").
like(bldr.getParameter("projectName")));
...

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 refreshIdentityMayResult() 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 to be lost.

In the case where an OptimisticLock field is in use, the refresh cache option can be used in conjunction with a global setting for a bean so that 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. In the amendment method for a bean, the method onlyRefreshCacheIfNewerVersion() is called on the passed in TopLink Descriptor argument.

The following example illustrates how to set onlyRefreshCacheIfNewerVersion() option for a bean:

public static void addOrderFinders(Descriptor descriptor) {
   ...
   descriptor.onlyRefreshCacheIfNewerVersion();
}

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

5 Defining and Executing Queries