This section explains more advanced TopLink query API calls and techniques that you are most likely to use later in the development cycle.
This chapter includes the following sections:
For more information about the available query API, see Oracle Fusion Middleware Java API Reference for Oracle TopLink.
A redirect query is a named query that delegates query execution control to your application. redirect queried allow you to define the query implementation in code as a static method.
To perform complex operations, you can combine query redirectors with the TopLink query framework.
This section describes How to Create a Redirect Query.
To perform complex operations, you can combine query redirectors with the TopLink query framework. To create a redirector, implement the oracle.toplink.queryframework.QueryRedirector
interface. The query mechanism executes the Object invokeQuery(DatabaseQuery query, Record arguments, Session session)
method and waits for the results.
TopLink provides one preimplemented redirector, the MethodBasedQueryRedirector
method. To use this redirector, create a static invoke method on a class, and use the setMethodName(String)
call to specify the method to invoke.
ReadObjectQuery query = new ReadObjectQuery(Employee.class); query.setName("findEmployeeByAnEmployee"); query.addArgument("employee"); MethodBaseQueryRedirector redirector = new MethodBaseQueryRedirector(QueryRedirectorTest.class, "findEmployeeByAnEmployee"); query.setRedirector(redirector); Descriptor descriptor = getSession().getDescriptor(query.getReferenceClass()); descriptor.getQueryManager().addQuery(query.getName(), query); Vector arguments = new Vector(); arguments.addElement(employee); objectFromDatabase = getSession().executeQuery("findEmployeeByAnEmployee", Employee.class, arguments); public class QueryRedirectorTest { public static Object findEmployeeByAnEmployee( DatabaseQuery query, oracle.toplink.sessions.Record arguments, oracle.toplink.sessions.Session session) { ((ReadObjectQuery) query).setSelectionObject(arguments.get("employee")); return session.executeQuery(query); } }
To make a query time-aware, you specify an AsOfClause
that TopLink appends to the query. Use the AsOfClause
class if your historical schema is based on time stamps or the AsOfSCNClause
class if your historical schema is based on database system change numbers. You can specify an AsOfClause
at the time you acquire a historical session so that TopLink appends the same clause to all queries, or you can specify an AsOfClause
on a query-by-query basis.
Example 111-2 shows how to create a query that uses a particular AsOfClause
. This query will read all Employee
objects as of the time specified by timestamp
using the appropriate history tables described by the HistoryPolicy
set on the Employee
descriptor.
Example 111-2 Using a Historical Session
ReadAllQuery historicalQuery = new ReadAllQuery(Employee.class); AsOfClause asOfClause = new AsOfClause(timestamp); historicalQuery.setAsOfClause(asOfClause); historicalQuery.dontMaintainCache(); List pastEmployees = (List)historicalSession.executeQuery(historicalQuery);
You can use a fetch group with a ReadObjectQuery
or ReadAllQuery
. When you execute the query, TopLink retrieves only the attributes in the fetch group. TopLink automatically executes a query to fetch all the attributes excluded from this subset when and if you call a getter method on any one of the excluded attributes.
Note:
When you use fetch groups outside of CMP, use weaving (see Section 2.10, "Using Weaving").This section describes the following:
For more information about fetch groups, see Section 108.7.1.6, "Fetch Groups and Object-Level Read Queries".
You can optionally designate at most one fetch group as the default fetch group for a descriptor's reference class.
If you execute a ReadObjectQuery
or ReadAllQuery
without specifying a fetch group, TopLink will use the default fetch group unless you configure the query otherwise, as Example 111-3 shows.
Example 111-3 Configuring Default Fetch Group Behavior
// at the descriptor level FetchGroup group = new FetchGroup("nameOnly"); group.addAttribute("firstName"); group.addAttribute("lastName"); employeeDescriptor.getFetchGroupManager().addFetchGroup(group); // set the default fetch group employeeDescriptor.getFetchGroupManager().setDefaultFetchGroup(group); // when query1 is executed, the default fetch group applies ReadAllQuery query1 = new ReadAllQuery(Employee.class); // when query2 is executed, the default fetch group does not apply ReadAllQuery query2 = new ReadAllQuery(Employee.class); query2.setShouldUsedefaultFetchGroup(false);
Example 111-4 shows how to configure a ReadObjectQuery
for the Employee
class with a FetchGroup
named nameOnly
previously stored in the FetchGroupManager
owned by the Employee
class's descriptor.
Example 111-4 Configuring a Query with a FetchGroup Using the FetchGroupManager
In this example, only the Employee
attributes firstName
and lastName
are fetched. If you call the Employee
method get
for any other attribute, TopLink executes another query to retrieve all unfetched attribute values. Thereafter, calling that get
method will return the value directly from the object.
// create static fetch group at the descriptor level FetchGroup group = new FetchGroup("nameOnly"); group.addAttribute("firstName"); group.addAttribute("lastName"); descriptor.getFetchGroupManager().addFetchGroup(group); // use static fetch group at query level ReadAllQuery query = new ReadAllQuery(Employee.class); query.setFetchGroupName("nameOnly");
Example 111-5 shows how to create a FetchGroup
instance dynamically, at the time you create and execute a query, and configure the query with that FetchGroup
directly.
In this example, only the firstName
, lastName
, and salary
attributes are fetched. If you call the Employee
method get
for any other attribute, TopLink executes another query to retrieve all unfetched attribute values. Thereafter, calling that get
method will return the value directly from the object.
Example 111-5 Configuring a Query with a FetchGroup Dynamically
// dynamic fetch group query
ReadAllQuery query = new ReadAllQuery(Employee.class);
FetchGroup group = new FetchGroup("nameAndSalary");
group.addAttribute("firstName");
group.addAttribute("lastName");
group.addAttribute("salary");
query. setFetchGroup(group);
Example 111-6 shows how to create an object-level read query to return data that you know is read-only. Using such a query for read-only data can improve performance.
Example 111-6 Configuring an ObjectLevelReadQuery as Read-Only
ReadAllQuery query = new ReadAllQuery(Employee.class); query.setIsReadOnly(true);
For more information, see the following:
When you define descriptors for an interface to enable querying, TopLink supports querying on an interface, as follows:
If there is only a single implementor of the interface, the query returns an instance of the concrete class.
If there are multiple implementors of the interfaces, the query returns instances of all implementing classes.
When you query on a class that is part of an inheritance hierarchy, the session checks the descriptor to determine the type of the class, as follows:
If you configure the descriptor to read subclasses (the default configuration), the query returns instances of the class and its subclasses.
If you configure the descriptor not to read subclasses, the query returns only instances of the queried class, but no instances of the subclasses.
If you configure the descriptor to outer-join subclasses, the query returns instances of the class and its subclasses.
If neither of these conditions applies, the class is a leaf class and does not have any subclasses. The query returns instances of the queried class.
You can set the query manager to automatically append an expression to every query it performs on a class. For example, you can add an expression that filters the database for the valid instances of a given class.
Use this to do the following:
Filter logically deleted objects
Enable two independent classes to share a single table without inheritance
Filter historical versions of objects
Using Java, configure a descriptor with additional join expressions by creating an amendment method (see Section 119.35, "Configuring Amendment Methods"), and then using the DescriptorQueryManager
methods setAdditionalJoinExpression
or setMultipleTableJoinExpression
, as Example 111-7 shows.
Example 111-7 Registering a Query That Includes a Join Expression
In Example 111-7, the join
expression filters invalid instances of employee
from the query.
public static void addToDescriptor(Descriptor descriptor) { ExpressionBuilder builder = new ExpressionBuilder(); descriptor.getQueryManager().setAdditionalJoinExpression( (builder.getField("EMP.STATUS").notEqual("DELETED")).and( builder.getField("EMP.STATUS").notEqual("HISTORICAL")) ); }
TopLink does not provide a method to directly query against variable one-to-one mappings. To query against this type of mapping, combine TopLink DirectQueryKeys
and TopLink ReportQueries
to create query selection criteria for classes that implement the interface, as follows:
Create two DirectQueryKeys
to query for the possible implementors of the interface:
The first DirectQueryKey
is for the class indicator field for the variable one-to-one mapping.
The second DirectQueryKey
is for the foreign key to the class or table that implements the interface.
Create a subSelect
statement for each concrete class that implements the interface included in the query selection criteria.
Implement a ReportQuery
.
Example 111-8 Creating DirectQueryKeys
// The DirectQueryKeys as generated in the TopLink project Java
// source code from TopLink Workbench
…
descriptor.addDirectQueryKey("locationTypeCode","DEALLOCATION.DEALLOCATIONOBJECTTYPE");
descriptor.addDirectQueryKey("locationTypeId","DEALLOCATION.DEALLOCATIONOBJECTID");
If you are using Oracle Database, you can take advantage of TopLink support for the following Oracle Database features:
Oracle Hints (see Section 111.9.1, "How to Use Oracle Hints")
Hierarchical Queries (see Section 111.9.2, "How to Use Hierarchical Queries")
Oracle Hints is Oracle Database feature through which you can make decisions usually reserved for the optimizer. You use hints to specify things such as join order for a join statement, or the optimization approach of an SQL call.
The TopLink query framework supports Oracle Hints with the following API:
setHintString("/*[hints or comments]*/");
TopLink adds the hint to the SQL string as a comment immediately following a SELECT
, UPDATE
, INSERT
, or DELETE
statement.
Add hints to a read query as follows:
Create a ReadObjectQuery
or a ReadAllQuery
Set the selection criteria.
Add hints as needed.
For example, the following code uses the FULL
hint (which explicitly chooses a full table scan for the specified table):
// Create the query and set Employee as its reference class ReadObjectQuery query = new ReadObjectQuery(Employee.class); // Retrieve ExpressionBuilder from the query ExpressionBuilder builder = query.getExpressionBuilder(); query.setSelectionCritera(builder.get("id").equal(new Integer(1)); // Add the hint query.setHintString("/*+ FULL */" );
This code generates the following SQL:
SELECT /*+ FULL */ FROM EMPLOYEE WHERE ID=1
To add hints to WRITE
, INSERT
, UPDATE
, and DELETE
, create custom queries for these operations in the TopLink query framework, then specify hints as required. For more information, see the following:
Section 23.4, "Configuring Custom SQL Queries for Basic Persistence Operations"
Section 76.5, "Configuring Custom EIS Interactions for Basic Persistence Operations"
For more information about the available hints, see Oracle Database documentation.
Hierarchical Queries is Oracle Database mechanism that lets you select database rows based on hierarchical order. For example, you can design a query that reads the row of a given employee, followed by the rows of people this employee manages, followed by their managed employees, and so on.
To create a hierarchical query, use the setHierarchicalQueryClause
method. This method takes three parameters, as follows:
setHierarchicalQueryClause(startWith, connectBy, orderSibling)
This expression requires all three parameters, as described in the subsequent text.
The startWith
parameter in the expression specifies the first object in the hierarchy. This parameter mirrors Oracle Database START WITH
clause.
To include a startWith
parameter, build an expression to specify the appropriate object, and pass it as a parameter in the setHierarchicalQueryClause
method. If you do not specify the root object for the hierarchy, set this value to null
.
The connectBy
parameter specifies the relationship that creates the hierarchy. This parameter mirrors Oracle Database CONNECT BY
clause.
Build an expression to specify the connectBy
parameter, and pass it as a parameter in the setHierarchicalQueryClause
method. Because this parameter defines the nature of the hierarchy, it is required for the setHierarchicalQueryClause
implementation.
The orderSibling
parameter in the expression specifies the order in which the query returns sibling objects in the hierarchy. This parameter mirrors Oracle Database ORDER SIBLINGS
clause.
To include an orderSibling
parameter, define a vector, and to include the order criteria, use the addElement
method. Pass the vector as the third parameter in the setHierarchicalQueryClause
method. If you do not specify an order, set this value to null
.
Example 111-9 Hierarchical Query
ReadAllQuery raq = new ReadAllQuery(Employee.class); // Specifies a START WITH expression Expression startExpr = expressionBuilder.get("id").equal(new Integer(1)); // Specifies a CONNECT BY expression Expression connectBy = expressionBuilder.get("managedEmployees"); // Specifies an ORDER SIBLINGS BY vector Vector order = new Vector(); order.addElement(expressionBuilder.get("lastName")); order.addElement(expressionBuilder.get("firstName")); raq.setHierarchicalQueryClause(startExpr, connectBy, order); Vector employees = uow.executeQuery(raq);
This code generates the following SQL:
SELECT * FROM EMPLOYEE START WITH ID=1 CONNECT BY PRIOR ID=MANAGER_ID ORDER SIBLINGS BY LAST_NAME, FIRST_NAME
This section describes how to use EJB finders in TopLink, including the following:
In general, to create a finder for an entity bean that uses the TopLink query framework, you must define, declare, and configure it.
For predefined finders (see Section 108.15.1, "Predefined Finders"), you do not need to explicitly create a finder.
For default finders (see Section 108.15.2, "Default Finders"), you only need to define the finder method.
To create a finder for an entity bean that uses the TopLink query framework, follow these steps:
Define the finder method on the entity bean's remoteHome
or localHome
interface.
For entity beans with container-managed persistence, define the method on the entity bean's Home
interface.
For default finders (see Section 108.15.2, "Default Finders"), you must define the finder as follows:
<
RETURN-TYPE
> findBy<
CMP-FIELD-NAME
>(<
CMP-FIELD-TYPE
>)
the first letter of <
CMP-FIELD-NAME
>
must be capitalized
<
RETURN-TYPE
>
may be a single bean type or Collection
.
For example:
EmployeeBean (Integer id, String name) EmployeeHome ..{ Employee findById(Integer id) throws...; Collection findByName(String name) throws...; }
Note:
If you are using default finders (see Section 108.15.2, "Default Finders"), you are finished. TopLink will implement the finder for you at run time.Declare the finder in the ejb-jar.xml
file (see Section 111.10.1.1, "ejb-jar.xml Finder Options").
Start TopLink Workbench.
Click the project icon in the Navigator and select: Selected > Update Project from ejb-jar.xml to read in the finders.
Go to the Queries > Named Queries tab for the bean (see Section 109.3, "Using Named Queries").
Select and configure the finder.
Notes:
For predefined findersfindOneByQuery
and findManyByQuery
, the client creates a query at run time and passes it as a parameter to the finder. Because of this, do not configure query options on these finders. Instead, configure options on the query passed into the finder. For more information about predefined finders, see Section 108.15.1, "Predefined Finders".If required, create an implementation for the query. Some query options require a query definition in code on a helper class, but most common queries do not.
When you use TopLink CMP, define finder methods on the bean's Home
interface, not in the entity bean itself. TopLink CMP provides this functionality and offers several strategies to create and customize finders. The EJB container and TopLink automatically generate the implementation.
The ejb-jar.xml
file contains a project's EJB entity bean information, including definitions for any finders used for the beans. To create and maintain the ejb-jar.xml
file, use either a text editor or TopLink Workbench.
The entity
tag encapsulates a definition for an EJB entity bean. Each bean has its own entity
tag that contains several other tags that define bean functionality, including bean finders.
Example 111-10 illustrates the structure of a typical finder defined within the ejbEjar.xml
file.
Note:
Use a combination of an escape character and a double-quote ( \" ) when defining your query using EJB QL. For more information on correct query syntax, see a note at the end of Configuring Named Query Selection Criteria.Example 111-10 A Simple Finder Within the ejb-jar.xml File
<entity>... <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>...</entity>
The entity
tag contains zero or more query
elements. Each query
tag corresponds to a finder method defined on the bean's home or local Home
interface.
Note:
You can share a single query between both Home interfaces, as follows:Define the same finder (same name, return type, and parameters) on both Home interfaces.
Include a single query
element in the ejb-jar.xml
file.
The following are the elements defined in the query
section of the ejbEjar.xml
file:
description
(optional): Provides a description of the finder.
query-method
: Specifies the method for a finder or ejbSelect
query.
method-name
: Specifies the name of a finder or select method in the entity bean 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): Specifies how to map an abstract schema type returned by a query for an ejbSelect
method. You can map the type to an EJBLocalObject
or EJBObject
type. Valid values are Local
or Remote
.
ejb-ql
: Used for all EJB QL finders. It contains the EJB QL query string that defines the finder or ejbSelect
query. Leave this element empty for non-EJB QL finders.
TopLink provides a predefined finder that takes a DatabaseQuery
such as a ReadAllQuery
. To use this feature in a bean, add the following finder definition to the Home
interface of your bean:
public Collection findManyByQuery(ReadAllQuery query) throws RemoteException, FinderException; public <EJBLocal/Remote> findOneByQuery(ReadObjectQuery query) throws RemoteException, FinderException;
To execute a ReadAllQuery
finder, create the query on the client, as Example 111-11 shows.
You can implement an EJB finder method (including TopLink predefined finders) as a named query. For more information, see Section 109.3, "Using Named Queries". You execute such a finder as you would any other.
TopLink provides a predefined finder (findByPrimaryKey
) that takes a primary key as an Object
.
EJB QL is the standard query language first defined in the EJB 2.0 specification. TopLink supports EJB QL. EJB QL finders let you specify an EJB QL string as the implementation of the query.
Note:
Use a combination of an escape character and a double-quote ( \" ) when defining your query using EJB QL. For more information on correct query syntax, see a note at the end of Section 119.7.1.3, "Configuring Named Query Selection Criteria".EJB QL offers the following advantages:
It is the EJB 2.0 and 2.1 standard for queries.
You can use it to construct most queries.
You can implement dependent object queries with EJB QL.
The disadvantage of EJB QL is that it is difficult to use when you construct complex queries.
To create an EJB QL finder, use this procedure:
Declare the finder on either the LocalHome
or the RemoteHome
interface.
Start TopLink Workbench.
Reimport the ejb-jar.xml
file to synchronize the project to the file.
TopLink Workbench synchronizes changes between the project and the ejb-jar.xml
file.
The following is an example of a simple EJB QL query that requires one parameter. In this example, the question mark ( ? ) in?1
specifies a parameter:
SELECT OBJECT(employee) FROM Employee employee WHERE (employee.name =?1)
To create an EJB QL finder for an entity bean with container-managed persistence, use this procedure:
Declare the finder in the ejb-jar.xml
file, and enter the EJB QL string in the ejb-ql
tag.
Declare the finder on the Home
interface, the LocalHome
interface, or both, as required.
Start TopLink Workbench.
Specify the ejb-jar.xml
file location and choose File > Updated Project from the ejb-jar.xml
file to read in the finders.
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. Then add any required parameters.
Select and configure the finder.
The following is an example of a simple EJB QL query that requires one parameter. In this example, the question mark ("?") in?1
specifies a parameter.
SELECT OBJECT(employee) FROM Employee employee WHERE (employee.name =?1)
You can use custom SQL code to specify finder logic. SQL lets you implement logic that might not be possible to express with TopLink expressions or EJB QL.
To create a SQL finder, use this procedure:
Declare the finder in the ejb-jar.xml
file, and leave the ejb-ql
tag empty.
Start TopLink Workbench.
Specify the ejb-jar.xml
file location and choose File > Updated Project from the ejb-jar.xml
file to read in the finders.
Go the Queries > Named Queries tab for the bean.
Select the finder, select the SQL radio button, and enter the SQL string.
Configure the finder.
The following is an example of a simple SQL finder that requires one parameter. In this example, the number sign character ( # ) is used to bind the argument projectName
within the SQL string:
SELECT * FROM EJB_PROJECT WHERE (PROJ_NAME = #projectName)
Redirect finders let you specify a finder in which the implementation is defined as a static method on an arbitrary helper class. When you invoke the finder, it redirects the call to the specified static method.
For more information about redirect queries, see Section 108.10, "Redirect Queries".
The finder can have any arbitrary parameters. If the finder includes parameters, TopLink packages them into a Vector
and passes them to the redirect method.
Redirect finders offer several advantages. Because you define the redirect finder implementation independently from the bean that invokes it, you can build the redirect finder to accept any type and number of parameters. This lets you create a generic redirect finder that accepts several different parameters and return types, depending on input parameters.
A common strategy for using redirect finders is to create a generic finder that does the following:
Includes logic to perform several tasks
Reads the first passed parameter to identify the type of finder requested and select the appropriate logic
The redirect method contains the logic required to extract the relevant data from the parameters and uses it to construct a TopLink query.
The main disadvantage of redirect finders is that they are complex and can be difficult to configure. They also require an extra helper method to define the query. However, because they support complex logic, they are often the best choice when you need to implement logic unrelated to the bean on which the redirect method is called.
To create a redirect finder, use the following procedure:
Declare the finder in the ejb-jar.xml
file, and leave the ejb-ql
tag empty.
Declare the finder on the Home
interface, the localHome
interface, or both, as required.
Create an amendment method.
For more information, see Section 119.35, "Configuring Amendment Methods".
Start TopLink Workbench.
Choose Advanced Properties > After Load from the menu for the bean.
Specify the class and name of the static method to enable the amendment method for the descriptor.
The amendment method then adds a query to the descriptor's query manager, as follows:
ReadAllQuery query = new ReadAllQuery(); query.setRedirector(new MethodBaseQueryRedirector (examples.ejb.cmp20.advanced. FinderDefinitionHelper.class,"findAllEmployeesByStreetName")); descriptor.getQueryManager().addQuery ("findAllEmployeesByStreetName", query);
The redirect method must return either a single entity bean (object) or a Vector
. Here are the possible method signatures:
public static Object redirectedQuery(oracle.toplink.sessions.Sessions, Vector args)
and
public static Vector redirectedQuery(oracle.toplink.sessions.Sessions, Vector args)
When you implement the query method, ensure that the method returns the correct type. For methods that return more than one bean, set the return type to java.util.Vector
. TopLink converts this result to java.util.Enumeration
(or Collection) if required.
Note:
The redirect method also interprets a TopLink session as a parameter. For more information about a TopLink session, see Part XXI, "TopLink Sessions".At run time, the client invokes the finder from the entity bean home and packages the arguments into the args
vector in order of appearance from the finder method signature. The client passes the vector to the redirect finder, which uses them to execute a TopLink expression.
Example 111-13 A Simple Redirect Query Implementation
public class RedirectorTest { private Session session; private Project project; public static void main(String args[]) { RedirectorTest test = new RedirectorTest(); test.login(); try { // Create the arguments to be used in the query Vector arguments = new Vector(1); arguments.add("Smith"); // Run the query Object o = test.getSession() .executeQuery(test.redirectorExample(), arguments); o.toString(); } catch (Exception e) { System.out.println("Exception caught -> " + e); e.printStackTrace(); } } public ReadAllQuery redirectorExample() { // Create a redirector MethodBasedQueryRedirector redirector = new MethodBasedQueryRedirector(); // Set the class containing the public static method redirector.setMethodClass(RedirectorTest.class); // Set the name of the method to be run redirector.setMethodName("findEmployeeByLastName"); // Create a query and add the redirector previously created ReadAllQuery readAllQuery = new ReadAllQuery(Employee.class); readAllQuery.setRedirector(redirector); readAllQuery.addArgument("lastName"); return readAllQuery; } // Call the static method public static Object findEmployeeByLastName( oracle.toplink.sessions.Session session, Vector arguments) { // Create a query and set Employee as its ref. class ReadAllQuery raq = new ReadAllQuery(Employee.class); raq.addArgument("lastName"); // Create the selection criteria ExpressionBuilder employee = query.getExpressionBuilder(); Expression whereClause = employee.get("lastName").equal(arguments.firstElement()); // Set the selection criteria raq.setSelectionCriteria(whereClause); return (Vector)session.executeQuery(raq, arguments); } ... }
The ejbSelect
method is a query method intended for internal use within an entity bean instance. Specified on the abstract bean itself, the ejbSelect
method is not directly exposed to the client in the home or component interface. Defined as abstract, each bean can include zero or more such methods.
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 as well.
The result-type-mapping
tag in the ejb-jar.xml
file determines the return type for ejbSelect
methods. Set the flag to Remote
to return EJBObjects
; set it to Local
to return EJBLocalObjects
.
The format for an ejbSelect method definition should be similar to the following:
public abstract type ejbSelect<METHOD>(...);
The ejbSelect
query return type is not restricted to the entity bean type on which the ejbSelect
is invoked. Instead, it can return any type corresponding to a container-managed relationship or container-managed field.
Although the ejbSelect
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. This creates a query that is logically scoped to a particular entity bean instance.
To create an ejbSelect method, use this procedure:
Update the ejb-jar.xml
file as follows:
Declare the ejbSelect
method.
Enter the EJB QL string in the ejb-ql
tag.
Specify the return type in the resultEtype-mapping
tag (if required).
Declare the ejbSelect
on the abstract bean class.
Start TopLink Workbench.
Click the project icon in the Navigator, and select: Selected > Update Project from ejb-jar.xml to read in the finders.
Go the Queries > Named Queries tab for the bean.
Select and configure the ejbSelect
method.
Cursors and streams are related mechanisms that let you work with large result sets efficiently. See Section 108.5.3, "Stream and Cursor Query Results" for more information.
Table 111-1 lists the methods that TopLink provides for all subclasses of DataReadQuery
and ReadAllQuery
that you can use to make your query return its results as a cursor or stream.
Table 111-1 Stream and Cursor Query Result Options
Method | Query Returns | Description |
---|---|---|
|
|
Allows you access a database result set cursor, allowing you to move forward and backward through the result set. |
|
|
Allows you to access results one at a time in sequence, as results become available to the underlying database result set cursor. |
Using a ScrollableCursor
or CursoredStream
combines the features of a TopLink with the ability of the database to cursor data, and breaks up the result set into smaller, more manageable pieces.
The behavior of a query that uses a ScrollableCursor
or CursoredStream
differs from other queries in that the elements requested by the client are sent to the client.
This section describes the following:
The TopLink scrollable cursor lets you scroll through a result set from the database without reading the whole result set in a single database read operation. The ScrollableCursor
class implements the Java ListIterator
interface to allow for direct and relative access within the stream. Scrollable cursors also let you scroll forward and backward through the stream.
The following methods let you navigate data with a scrollable cursor:
relative(int i)
: advances the row number in relation to the current row by one row
absolute(int i)
: places the cursor at an absolute row position, 1 being the first row
Several strategies are available for traversing data with cursors. For example, to start at the end of the data set and work toward the first record, do the following:
Call the afterLast
method to place the cursor after the last row in the result set.
Use the hasPrevious
method to determine whether there is a record above the current record. This method returns false
when you reach the final record in the data set.
If the hasPrevious
method returns true
, call the previous
method to move the cursor to the row prior to the current row and read that object.
These are common methods for data traversal, but they are not the only available methods. For more information about the available methods, see Oracle Fusion Middleware Java API Reference for Oracle TopLink.
To use the ScrollableCursor
object, the JDBC driver must be compatible with the JDBC 2.0 specifications.
Java streams let you retrieve query results as individual records or groups of records, which can result in a performance increase. You can use streams to build efficient TopLink queries, especially when the queries are likely to generate large result sets.
Cursored streams provide the ability to read back a query result set from the database in manageable subsets, and to scroll through the result set stream.
The useCursoredStream
method of the ReadAllQuery
class provides cursored stream support.
Example 111-15 Cursored Streams
CursoredStream stream; ReadAllQuery query = new ReadAllQuery(Employee.class); query.useCursoredStream(); stream = (CursoredStream) session.executeQuery(query);
The query returns an instance of CursoredStream
rather than a List
, which can be a more efficient approach. For example, consider the following two code examples. Example 111-16 returns a List
that contains all employee objects. If ACME has 10,000 employees, the List
contains references to 10,000 Employee
objects.
ReadAllQuery query = new ReadAllQuery(Employee.class); Enumeration employeeEnumeration; List employees = (List) session.executeQuery(query); employeeEnumeration = employee.elements(); while (employeeEnumeration.hasMoreElements()) { Employee employee = (Employee) employeeEnumeration.nextElement(); employee.doSomeWork(); }
The following example returns a CursoredStream
instance rather than a List
. The CursoredStream
collection appears to contain all 10,000 objects, but initially contains a reference to only the first 10 Employee
objects. It retrieves the remaining objects in the collection as they are needed. In many cases, the application never needs to read all the objects:
ReadAllQuery query = new ReadAllQuery(Employee.class); query.useCursoredStream(); CursoredStream stream = (CursoredStream) session.executeQuery(query); while (! stream.atEnd()) { Employee employee = (Employee) stream.read(); employee.doSomeWork(); stream.releasePrevious(); } stream.close();
Note:
ThereleasePrevious
message is optional. This releases any previously read objects and frees system memory. Even though released objects are removed from the cursored stream storage, they may remain in the identity map.To optimize CursoredStream
performance, provide a threshold and page size to the useCursoredStream(Threshold, PageSize)
method, as follows:
The threshold specifies the number of objects to read into the stream initially. The default threshold is 10.
The page size specifies the number of objects to read into the stream after the initial group of objects. This occurs after the threshold number of objects is read. Although larger page sizes result in faster overall performance, they introduce delays into the application when TopLink loads each page. The default page size is 5.
When you execute a batch-type operation, use the dontMaintainCache
method with a cursored stream. A batch operation performs simple operations on large numbers of objects and then discards the objects. Cursored streams create the required objects only as needed, and the dontMaintainCache
ensures that these transient objects are not cached.
n
CMP FindersLarge result sets can be resource-intensive to collect and process. To give the client more control over the returned results, configure TopLink finders to use cursors. This combines TopLink's CursoredStream
with the ability of the database to cursor data, and breaks up the result set into smaller, more manageable pieces.
Note:
If you use the transactional attributeREQUIRED
for an entity bean, wrap all read operations in UserTransaction
methods begin
and commit
to ensure that read operations beyond the first page of the cursor have a transaction in which to work.You can configure any finder that returns a java.util.Collection
to use a cursor. When you create the query for the finder, add the useCursoredStream
option to enable cursoring.
Example 111-17 Cursored Stream in a Finder
ReadAllQuery raq = new ReadAllQuery(ProjectBean.class); ExpressionBuilder bldr = raq.getExpressionBuilder(); raq.useCursoredStream(); raq.addArgument("projectName"); raq.setSelectionCriteria(bldr.get("name"). like(bldr.getParameter("projectName"))); descriptor.getQueryManager().addQuery ("findByNameCursored", query);
TopLink offers the following additional elements for traversing finder results:
isEmpty
method: As with java.util.Collection
, isEmpty
method returns a boolean
value indicating whether or not the Collection
is empty.
size
method: As with java.util.Collection
, size
method returns an integer indicating the number of elements in the Collection
.
iterator
method: As with java.util.Collection
, iterator
method returns a java.util.Iterator
for enumerating the elements in the Collection
.
TopLink also offers an extended protocol for oracle.toplink.ejb.cmp.wls.CursoredIterator
(based on java.util.Iterator
):
close
method: Closes the cursor on the server. The client must call this method to close the database connection.
hasNext
method: Returns a boolean
value indicating whether or not any more elements are in the result set.
next
method: Returns the next available element.
next(int count)
method: Retrieves a Vector
of at most count elements from the available results, depending on how many elements remain in the result set.
Example 111-18 illustrates client code executing a cursored finder.
Example 111-18 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();
You can configure a query to retrieve a result set in pages, that is, a partial result as a List
of pageSize
(or less) results. Example 111-19 demonstrates paging through the result set of a query using ReadQuery
methods setMaxRows
and setFirstResult
.
For more information, see the following:
Section 12.12.8, "How to Use Result Set Pagination for Optimization"
Section 12.12.6, "How to Use JDBC Fetch Size for Optimization"
Example 111-19 Using setMaxRows and setFirstResult to Page Through a Result Set
...
int pageSize = 100;
int firstResult = 0;
int maxRows = pageSize;
boolean hasNext = true;
List page = null;
while (hasNext) {
query.setFirstResult(firstResult);
query.setMaxRows(maxRows);
page = (List)sesssion.executeQuery(query);
// process this page of results
if (page.size() == 0) {
hasNext = false;
} else {
firstResult = firstResult + pageSize;
maxRows = maxRows + pageSize;
}
}
...
This section describes how to use caching options in TopLink queries, including the following:
By default, each time you execute a ReadQuery
, TopLink applies the current query configuration to the read operation. In doing so, TopLink will access the session cache, the data source, or both.
Some queries are known to return the same result set (for example, the number of units sold last year by the current sales person). After the first query execution, there is no need to actually execute the query if it is invoked again.
For these types of queries, you can use any TopLink ReadQuery
and configure it to store its query results in an internal query cache.
After its first execution for a set of query parameters, the query will return its cached result set each time it is invoked with the same query parameters. This improves query performance for frequently executed queries. By default a query will cache the results sets for the last 100 queries of specific parameters. You can configure this query cache as part of the QueryResultsCachePolicy
.
Enable this feature using ReadQuery
method cacheQueryResults
or by calling the ReadQuery
method setQueryResultsCachePolicy
with an instance of QueryResultsCachePolicy
, and disable it using ReadQuery
method doNotCacheQueryResults
.
Before using this feature, consider the restrictions in Section 108.16.7.1, "Internal Query Cache Restrictions". For more information, see Section 108.16.7, "How to Cache Query Results in the Query Cache".
You can apply a cache invalidation policy to the query's internal cache (see Section 111.13.2, "How to Configure Cache Expiration at the Query Level"). For more information, see Section 102.2.5, "Cache Invalidation".
Example 111-20 shows how to configure a ReadQuery
to cache its results.
Example 111-20 Configuring a ReadQuery to Cache Its Query Results
ReadObjectQuery query = new ReadObjectQuery(Employee.class); // Instruct the ReadQuery to cache its query results query.cacheQueryResults(); // The first time you invoke it, the ReadQuery reads from the database, session // cache, or both and stores the result set in its internal query cache Employee employeeFirst = (Employee) session.executeQuery(query);
Example 111-21 shows how to configure the ReadQuery
to stop caching its results. The next time the query is executed, TopLink does not use the query cache. Instead, the query accesses the data source.
Example 111-21 Configuring a ReadQuery to Stop Caching Its Query Results
// Disable query caching query.doNotCacheQueryResults(); // The ReadQuery does not use the query cahce and instead accesses the database Employee employee = (Employee) session.executeQuery(query);
Alternatively, you can clear the query's internal cache using ReadQuery
method clearQueryResults
passing in your session. This clears the currently cached results and ensures that the next query execution reads from the database.
You can configure a ReadQuery
with a CacheInvalidationPolicy
.
If you configure a query to cache results in its own internal cache (see Section 111.13.1, "How to Cache Results in a ReadQuery"), the cache invalidation policy allows the cached query result set to expire, based on a time-to-live or daily-expiry. This invalidation time is calculated from the time of the query execution that cached the query result set for the specific set of query parameters.
Example 111-22 shows how to configure a ReadQuery
so that a TimeToLiveCacheInvalidationPolicy
is applied to all the objects returned by the query and cached in the query's internal cache.
Example 111-22 Configuring a CacheInvalidationPolicy on a ReadQuery for the Query's Internal Cache
// The TimeToLiveCacheInvalidationPolicy applies to all objects returned by the query and // cached in the query's internal cache readQuery.setQueryResultsCachePolicy( new QueryResultsCachePolicy(new TimeToLiveCacheInvalidationPolicy(1000)) );
For more information, see Section 102.2.5, "Cache Invalidation".