108 Introduction to TopLink Queries

TopLink enables you to create, read, update, and delete persistent objects or data using queries in both Java EE and non-Java EE applications for both relational and nonrelational data sources.

This chapter includes the following sections:

• Query Types

• Query Concepts

• Building Queries

• Executing Queries

• Handling Query Results

• Session Queries

• Database Queries

• Named Queries

• Call Queries

• Redirect Queries

• Historical Queries

• Interface and Inheritance Queries

• Descriptor Query Manager Queries

• Oracle Extensions

• EJB 2.n CMP Finders

• Queries and the Cache

• Query API

108.1 Query Types

Table 108-1 lists the query types that you can build in TopLink.

Table 108-1 TopLink Query Types

Query Type	Description	Oracle JDeveloper	TopLink Workbench	Java
Session Queries	A query implicitly constructed and executed by a Session based on input parameters used to perform the most common data source actions on objects.
Database Queries	A query also known as a query object query. An instance of DatabaseQuery that you create and then execute to perform any data source action on either objects or data. You can further refine a DatabaseQuery by also creating and configuring its Call (see Section 108.9, "Call Queries").
Named Queries	An instance of DatabaseQuery stored by name in a Session or a descriptor's DescriptorQueryManager where it is constructed and prepared once. Such a query can then be repeatedly executed by name.
Call Queries	An instance of Call that you create and then either execute directly, using a special Session API to perform limited data source actions on data only, or execute indirectly in the context of a DatabaseQuery. TopLink supports Call instances for custom SQL, stored procedures, and EIS interactions.
Redirect Queries	An instance of MethodBasedQueryRedirector (taking the name of a static method and the Class in which it is defined as parameters) set on a named query. When the query is executed, the static method is invoked.
Historical Queries	Any query executed in the context of a historical session using the time-aware features of the TopLink Expression framework.
Interface and Inheritance Queries	Any query that references an interface type or super and subclasses of an inheritance hierarchy.
Descriptor Query Manager Queries	The DescriptorQueryManager defines a default DatabaseQuery for each basic data source operation (create, read, update, and delete), and provides an API with which you can customize either the DatabaseQuery or its Call.
EJB 2.n CMP Finders	A query defined on the home interface of an enterprise bean that returns enterprise beans. You can implement finders using any TopLink query type, including JPAQLCall and EJBQLCall, a Call that takes JPA/EJB QL.

For more information, see the following:

• Chapter 109, "Using Basic Query API"

• Chapter 111, "Using Advanced Query API"

• Section 108.2, "Query Concepts"

108.2 Query Concepts

In general, querying a data source means performing an action on or interacting with the contents of the data source. To do this, you must be able to perform the following:

• Define an action in a syntax native to the data source being queried.

• Apply the action in a controlled fashion.

• Manage the results returned by the action (if any).

Specific to TopLink, you must also consider how the query affects the TopLink cache. For more information, see Section 108.16, "Queries and the Cache".

This section introduces query concepts unique to TopLink, including the following:

• Call

• DatabaseQuery

• Data-Level and Object-Level Queries

• Summary Queries

• Descriptor Query Manager

• TopLink Expressions

• Query Keys

• Query Languages

108.2.1 Call

In TopLink, the Call object encapsulates an operation or action on a data source. TopLink provides a variety of Call types such as structured query language (SQL), Enterprise Java Beans Query Language (EJB QL), Java Persistence Query Language (JP QL), Extensible Markup Language (XML), and enterprise information system (EIS).

You can execute a Call directly or in the context of a DatabaseQuery.

108.2.2 DatabaseQuery

A DatabaseQuery object is an abstraction that associates additional customization and optimization options with the action encapsulated by a Call. By separating these options from the Call, TopLink can provide sophisticated query capabilities across all Call types.

For more information, see Section 108.7, "Database Queries".

108.2.3 Data-Level and Object-Level Queries

In TopLink, queries can be defined for objects or data, as follows:

• Object-level queries (see Section 108.7.1, "Object-Level Read Query" and Section 108.7.3, "Object-Level Modify Query") are object-specific and return data as objects in your domain model. They are the preferred type of query for mapped data. By far, object-level DatabaseQuery queries are the most common query used in TopLink.

• Data-level queries (see Section 108.7.2, "Data-Level Read Query" and Section 108.7.4, "Data-Level Modify Query") are used to query database tables directly, and are an appropriate way to work with unmapped data.

108.2.4 Summary Queries

While data-level queries return raw data and object-level queries return objects in your domain model, summary queries return data about objects. TopLink provides partial object queries (see Section 108.7.1.3, "Partial Object Queries") to return a set of objects with only specific attributes populated, and report queries (see Section 108.7.5, "Report Query") to return summarized (or rolled-up) data for specific attributes of a set of objects.

108.2.5 Descriptor Query Manager

In addition to storing named queries applicable to a particular class (see Section 108.8, "Named Queries"), you can also use the DescriptorQueryManager to override the default action that TopLink defines for common data source operations. For more information, see Section 108.13, "Descriptor Query Manager Queries".

108.2.6 TopLink Expressions

TopLink expressions let you specify query search criteria based on your domain object model. When you execute the query, TopLink translates these search criteria into the appropriate query language for your platform.

TopLink provides the following two public classes to support expressions:

• The Expression class represents an expression that can be anything from a simple constant to a complex clause with boolean logic. You can manipulate, group, and integrate expressions.

• The ExpressionBuilder class is the factory for constructing new expressions.

You can specify a selection criterion as an Expression with DatabaseQuery method setSelectionCriteria (see Section 108.7, "Database Queries"), and in a finder that takes an Expression (see Section 108.15.7, "Expression Finders").

For more information about using TopLink expressions, see Chapter 110, "Introduction to TopLink Expressions".

108.2.7 Query Keys

A query key is a schema-independent alias for a database field name. Using a query key, you can refer to a field using a schema-independent alias. In relational projects only, TopLink automatically creates query keys for all mapped attributes. The name of the query key is the name of the class attribute specified in your object model.

You can configure query keys in a class descriptor (see Section 119.10, "Configuring Query Keys") or interface descriptor (see Section 119.11, "Configuring Interface Query Keys").

You can use query keys in expressions (see Section 110.4, "Query Keys and Expressions") and to query variable one-to-one mappings (see Section 111.8, "Using Queries on Variable One-to-One Mappings").

108.2.8 Query Languages

Using TopLink, you can express a query using any of the following query languages:

• SQL Queries

• EJB QL Queries

• JP QL Queries

• XML Queries

• EIS Interactions

• Query-by-Example

• TopLink Expressions (see Chapter 110, "Introduction to TopLink Expressions")

In most cases, you can compose a query directly in a given query language or, preferably, you can construct a DatabaseQuery with an appropriate Call and specify selection criteria using a TopLink Expression. Although composing a query directly in SQL appears to be the simplest approach (and for simple operations or operations on unmapped data, it is), using the DatabaseQuery approach offers the compelling advantage of confining your query to your domain object model and avoiding dependence on data source schema implementation details.

Oracle recommends that you compose your queries using JP QL or Expression.

108.2.8.1 SQL Queries

SQL is the most common query language for applications that use a relational database data source.

You can execute custom SQL directly using Session methods executeSelectingCall and executeNonSelectingCall, or you can construct a DatabaseQuery with an appropriate Call.

TopLink provides a variety of SQL Call objects for use with stored procedures and, with Oracle Database, stored functions. For more information, see Section 108.9.1, "SQL Calls".

TopLink also supports PL/SQL call for Oracle stored procedures with PL/SQL data types. For more information, see Section 109.5, "Using a StoredProcedureCall".

108.2.8.2 EJB QL Queries

Like SQL, EJB QL is a query language; but unlike SQL, it presents queries from an object model perspective, allowing you to declare queries using the attributes of each abstract entity bean in your object model. It also includes path expressions that enable navigation over the relationships defined between entity beans and dependent objects.

Using EJB QL offers the following advantages:

• You do not need to know the database structure (such as tables and fields).

• You can construct queries using the attributes of the entity beans instead of using database tables and fields.

• You can use relationships in a query to provide navigation from attribute to attribute.

• EJB QL queries are portable because they are database-independent.

• You can specify the reference class in the SELECT clause.

The disadvantage of EJB QL queries is that it is difficult to use when you construct complex queries.

TopLink provides the full support for the EJB QL specification.

Note:

TopLink supports the LOCATE string function and will generate the correct SQL with it. However, not all data sources support LOCATE. Before using the LOCATE string function, consult your data source documentation.

EJB QL is the standard query language first defined in the EJB 2.0 specification. Consequently, TopLink lets you specify selection criteria using EJB QL in an EJB finder (see Section 108.15.8, "EJB QL Finders").

Although EJB QL is usually associated with EJB, TopLink also lets you specify selection criteria using EJB QL in queries for regular Java objects as well. TopLink provides an EJB QL Call that you can execute directly or in the context of a DatabaseQuery. For more information, see Section 108.9.2, "EJB QL Calls" and Section 108.2.2, "DatabaseQuery".

108.2.8.3 JP QL Queries

See "What You May Need to Know About Querying with Java Persistence Query Language" section of EclipseLink Developer's Guide at http://wiki.eclipse.org/Developing_Applications_Using_EclipseLink_JPA_%28ELUG%29#What_You_May_Need_to_Know_About_Querying_with_Java_Persistence_Query_Language for more information.

108.2.8.4 XML Queries

You can use TopLink XML to query XML data stored in Oracle Database XMLType field. For more information, see Section 27.4, "Direct-to-XMLType Mapping" and Section 110.2.4, "XMLType Functions".

108.2.8.5 EIS Interactions

When you execute a TopLink query using an EIS Call (see Section 108.9.3, "Enterprise Information System (EIS) Interactions"), TopLink converts your selection criteria into an XML format appropriate for your JCA adapter.

If supported by your JCA adapter, you can use the XQuery language by executing an XQuery interaction (see Section 108.9.3.4, "XQueryInteraction") either directly or in the context of a DatabaseQuery.

108.2.8.6 Query-by-Example

Query-by-example is a simple and intuitive way to express a query. To specify a query-by-example, provide a sample instance of the persistent object to query, and set appropriate values on only the data members on which you wish to query.

Query-by-example lets you query for an object based on any attribute that uses a direct mapping or a one-to-one relationship (including those with nesting).

Note:

Query-by-example does not support any other relationship mapping types, nor does it support EJB 2.n CMP beans.

Set only the attributes on which you base the query; set all other attributes to null. By default, TopLink ignores attributes in the sample instance that contain null, zero (0), empty strings, and FALSE. You can modify this list of values (and define other query by example options) by specifying a QueryByExamplePolicy (see Defining a QueryByExamplePolicy).

Query-by-example uses the AND operator to tie the attribute comparisons together. By default, attribute values in the sample instance are compared against corresponding values of candidate objects using EQUALS operator. You can modify this behaviour using the QueryByExamplePolicy.

Both ReadAllQuery and ReadObjectQuery provide a setExampleObject method and setQueryByExamplePolicy method that you can use to specify selection criteria based on an example object instance.

For more information and examples, see Section 109.2.1.4, "Reading Objects Using Query-By-Example".

108.3 Building Queries

You can build queries using Oracle JDeveloper, TopLink Workbench, or Java using the TopLink API.

Some queries are implicitly constructed for you based on passed in arguments and executed in one step (for example, session queries, as described in Section 108.6, "Session Queries") and others you explicitly create, configure, and then execute (for example, Section 108.7, "Database Queries").

For more information, see the following:

• Chapter 109, "Using Basic Query API"

• Chapter 111, "Using Advanced Query API"

108.4 Executing Queries

In TopLink, you execute most queries using the Session API summarized in Table 108-2.

Table 108-2 Session Methods for Executing a Query

Query Type	Session Method	Advantages and Disadvantages
Session Queries	readObject readAllObjects writeObject writeAllObjects deleteObject deleteAllObjects insertObject updateObject	Advantages: the most convenient way to perform common data source operations on objects. Disadvantages: less control over query execution and results; less efficient for frequently executed queries.
Database Queries Named Queries Redirect Queries	executeQuery	Advantages: greatest configuration and execution flexibility; can take advantage of named queries for efficiency. Disadvantages: you must explicitly create and configure DatabaseQuery and possibly Call objects.
Call Queries	executeSelectingCall executeNonSelectingCall	Advantages: convenient way to directly apply an action to unmapped data. Disadvantages: least control over query execution and results; your application must do more work to handle raw data results.

Note:

Oracle recommends that you perform all data source operations using a unit of work: doing so is the most efficient way to manage transactions, concurrency, and referential constraints. For more information, see Chapter 113, "Introduction to TopLink Transactions".

Alternatively, you can execute queries outside of a unit of work using a session API directly, but doing so places greater responsibility on your application to manage transactions, concurrency, and referential constraints.

TopLink executes DescriptorQueryManager queries when you execute a session query. For more information, see Section 108.13, "Descriptor Query Manager Queries".

You execute EJB 2.n CMP finders when you call the appropriate finder method on an EJB 2.n CMP bean. For more information, see Section 108.15, "EJB 2.n CMP Finders".

WARNING:

Allowing an unverified SQL string to be passed into methods (for example: setSQLString(String sql), readAllObjects(Class class, String sql) methods) makes your application vulnerable to SQL injection attacks.

For more information, see the following:

• Chapter 109, "Using Basic Query API"

• Chapter 111, "Using Advanced Query API"

108.5 Handling Query Results

TopLink queries generally return Java objects as their result set. TopLink queries can return any of the following:

• Entire objects, with all attributes populated and the object reflected in the cache.

• Collections of objects (see Section 108.5.1, "Collection Query Results").

• Collections of records.

• Report summaries.

• Partial objects, with only the attributes you specify populated and without the object reflected in the cache (see Section 108.5.2, "Report Query Results").

• Streams of objects (see Section 108.5.3, "Stream and Cursor Query Results").

• EJB (see Section 108.15, "EJB 2.n CMP Finders").

108.5.1 Collection Query Results

A collection is a group of Java objects contained by an instance of Collection or Map

By default, queries that return more than one object return their results in a Vector.

You can configure TopLink to return query results in any concrete instance of Collection or Map.

Collection results are supported by all TopLink query types.

For information and examples on how to configure and handle collection query results, see Section 109.10, "Handling Collection Query Results".

108.5.2 Report Query Results

A ReportQuery (a type of partial object query) returns summary data for selected objects using the database reporting functions and features supported by your platform. Although the report query returns data (not objects), it does enable you to query the returned data and specify it at the object level.

By default, a ReportQuery returns a collection (see Section 108.5.1, "Collection Query Results") of ReportQueryResult objects, one collection per database row returned. You can use the ReportQuery API to configure how a ReportQuery returns its results. For more information see Section 109.11, "Handling Report Query Results".

For more information, see the following:

• Section 108.7.5, "Report Query"

• Section 109.11, "Handling Report Query Results"

• Section 108.7.1.3, "Partial Object Queries".

108.5.3 Stream and Cursor Query Results

A stream is a view of a collection, which can be a file, a device, or a Vector. A stream provides access to the collection, one element at a time in sequence. This makes it possible to implement stream classes in which the stream does not contain all the objects of a collection at the same time.

Large result sets can be resource-intensive to collect and process. To improve performance and give the client more control over the returned results, configure TopLink queries to use a cursor or stream.

Cursors & streams are supported by all subclasses of DataReadQuery and ReadAllQuery.

For more information, see Section 111.11, "Handling Cursor and Stream Query Results".

108.6 Session Queries

Sessions provide query methods that lets you perform the object operations listed in Table 108-3.

Table 108-3 Session Object Query Summary

Session Type	Create	Read	Update	Delete
UnitOfWork	registerObject	readObject readAllObjects	NA	deleteObject deleteAllObjects
Server	NA	NA	NA	NA
ClientSession	NA	readObject readAllObjects	NA	NA
DatabaseSession	insertObject	readObject readAllObjects	updateObject writeObject writeAllObjects	deleteObject deleteAllObjects

Note:

Oracle recommends that you perform all data source operations using a unit of work: doing so is the most efficient way to manage transactions, concurrency, and referential constraints. For more information, see Chapter 113, "Introduction to TopLink Transactions".

These methods implicitly construct and execute a DatabaseQuery based on any of the following input parameters and return Object or Object collection:

• Reference Class (the Class of objects that the query accesses)

• Reference Class and Call

• Reference Class and Expression

• Example object with primary key set

These methods are a convenient way to perform the most common data source operations on objects.

WARNING:

Allowing an unverified SQL string to be passed into these methods makes your application vulnerable to SQL injection attacks.

To access all configurable options to further refine and optimize a query, consider using a corresponding DatabaseQuery directly. For more information, see Section 108.7, "Database Queries".

For more information, see Section 109.1, "Using Session Queries".

108.6.1 Read-Object Session Queries

Read-object queries return the first instance of an Object that matches the specified selection criteria, and read-all object queries return all such instances.

You can also pass in a domain Object with its primary key set and TopLink will construct and execute a read-object query to select that object. This is one form of query by example. For more information on query by example, see Section 108.2.8.6, "Query-by-Example".

For more information, see Section 109.1.1, "How to Read Objects with a Session Query".

108.6.2 Create, Update, and Delete Object Session Queries

Oracle recommends that you create and update objects using a unit of work: doing so is the most efficient way to manage transactions, concurrency, and referential constraints. For more information, see Chapter 113, "Introduction to TopLink Transactions".

However, you can also create and update objects using a session query. These session queries are a convenient way to modify objects directly on the database when you manage simple, nonbusiness object data that has no relationships (for example, user preferences).

If you know an object is new, you can use an insertObject method to avoid having TopLink perform an existence check. If you do not know if an object is new, use the updateObject, writeObject, or writeAllObject methods: TopLink performs an existence check if necessary.

When you execute a write session query, it writes both the object and its privately owned parts to the database. To manage this behavior, use a corresponding DatabaseQuery (see Section 108.7.3.7, "Object-Level Modify Queries and Privately Owned Parts").

Using the Session method deleteObject, you can delete a specific object. Using the Session method deleteAllObjects, you can delete a collection of objects. Each specified object and all its privately owned parts are deleted. In the case of deleteAllObjects, all deletions are performed within a single transaction.

For more information, see Section 109.1.2, "How to Create, Update, and Delete Objects with a Session Query".

108.7 Database Queries

All session types provide an executeQuery method that takes any of the following types of DatabaseQuery:

• Object-Level Read Query

• Data-Level Read Query

• Object-Level Modify Query

• Data-Level Modify Query

• Report Query

Using DatabaseQuery method setCall, you can define your own Call to accommodate a variety of data source options such as SQL (including stored procedures and stored functions), EJB QL queries, and EIS interactions. For more information, see Section 108.9, "Call Queries".

Using DatabaseQuery method setSelectionCriteria, you can specify your selection criteria using a TopLink Expression. For more information, see Section 108.2.6, "TopLink Expressions".

For more information, see Section 109.2, "Using DatabaseQuery Queries".

108.7.1 Object-Level Read Query

Using an ObjectLevelReadQuery, you can query your data source and return Object instances that match the specified selection criteria. This section describes the following:

• ReadObjectQuery

• ReadAllQuery

• Partial Object Queries

• Read-Only Query

• Join Reading and Object-Level Read Queries

• Fetch Groups and Object-Level Read Queries

For more information, see Section 109.2.1, "How to Read Objects Using a DatabaseQuery".

108.7.1.1 ReadObjectQuery

Using a ReadObjectQuery, you can query your data source and return the first object that matches the specified selection criteria.

108.7.1.2 ReadAllQuery

Using a ReadAllQuery, you can query your data source and return a Collection of all the objects that match the specified selection criteria.

108.7.1.3 Partial Object Queries

By default, an ObjectLevelReadQuery returns all attributes of the objects read.

If you require only certain attributes from selected objects, you can create a partial object query by using ObjectLevelReadQuery method addPartialAttributes. Using this method, you can improve query performance by making TopLink return objects with only specified attributes populated.

Applications frequently use partial object queries to compile a list for further selection. For example, a query to find the names and addresses of all employees over the age of 40 returns a list of data (the names and addresses) that partially represents objects (the employees). A common next step is to present this list so the user can select the required object or objects from the list. Later retrieval of a complete object is simplified because TopLink always includes the primary key attribute (even if you do not add it as a partial attribute.

Consider the following when you use partial object queries:

• You cannot edit or cache partial objects.

• Unspecified attributes will be left null.

• You cannot have two partial attributes of the same type.

• You cannot add a partial attribute which is of the same type as the class being queried.

If you require only summary information for certain attributes from selected objects, it is more efficient to use a ReportQuery (see Section 108.7.5, "Report Query").

For more information, see Section 109.2.1.2, "Reading Objects Using Partial Object Queries".

108.7.1.4 Read-Only Query

In cases where you know that data is read-only, you can improve performance by specifying a query as read-only: this tells TopLink that any object returned by the query is immutable.

For more information, see the following:

• Section 119.3, "Configuring Read-Only Descriptors"

• Section 111.4, "Using Read-Only Queries"

• Section 12.12.5, "How to Use Read-Only Queries for Optimization"

108.7.1.5 Join Reading and Object-Level Read Queries

Join reading is a query optimization feature that allows a single query for a class to return the data to build the instances of that class and its related objects. Use this feature to improve query performance by reducing database access. By default, relationships are not join-read: each relationship is fetched separately when accessed if you are using indirection (lazy loading) or as a separate database query if you are not using indirection. For more information, see Section 17.2.4, "Indirection (Lazy Loading)".

You can use join reading with ReadObjectQuery and ReadAllQuery to join the mapped relationships that Table 108-4 lists. Join reading is not currently supported for any other relationship mappings.

Table 108-4 Join Reading by Mapping Type

Query	Mapping Type
ReadObjectQuery	One-to-one (see Section 27.5, "One-to-One Mapping") One-to-many (see Section 27.7, "One-to-Many Mapping")
ReadAllQuery	Many-to-many (see Section 27.8, "Many-to-Many Mapping") Direct collection (see Section 27.10, "Direct Collection Mapping") Direct map (see Section 27.11, "Direct Map Mapping") Aggregate collection (see Section 27.9, "Aggregate Collection Mapping")

Join reading can specify multiple and nested relationships to be joined. Nested joins are expressed through using expressions (see Section 110.2.7, "Expressions for Joining and Complex Relationships").

Outer joins can also be used with join reading through using the expression outer join API. If an outer join is not used, objects with missing one-to-one relationships or empty one-to-many relationships will be filtered from the result set. You can also configure an object-level read query to allow inherited subclasses to be outer-joined to avoid the cost of a single query per class. You can also specify inner or outer joins using the useInnerJoinFetch or useOuterJoinFetch method of any of the mappings listed in Table 108-4.

You can use join reading with custom SQL or stored procedures, but the query must ensure that all of the required data to build all of the join-read objects is returned. If the result set includes the same tables or fields, they must be returned in the same table order as TopLink would have generated.

For more information, see the following:

• Section 109.2.1.10, "Using Join Reading with ObjectLevelReadQuery"

108.7.1.5.1 Avoiding Join-Reading Duplicate Data

Join reading can result in returning duplicate data if a one-to-many or a shared one-to-one relationship is joined. Although TopLink correctly filters the duplicate results from the object result, the duplicate data still must be fetched from the database and can degrade performance, especially if multiple one-to-many relationships are joined. In general, batch reading can be used as a better alternative to join reading, as it does not require fetching duplicate data.

Oracle recommends that you use one-to-many joining with caution, because it does not scale well in many situations.

Because the main cost of a ReadObjectQuery is SQL execution, the performance of a one-to-many join in this case is usually better than a query without joining.

However, because the main cost of a ReadAllObjectQuery is row-fetching, which the duplicate data of a join increases, the performance of a one-to-many join in this case is less efficient than batch reading in many scenarios (even though one-to-many joining is more efficient than reading the objects one-by-one).

This is mainly due to the fact that a one-to-many join reads in duplicate data: the data for each source object will be duplicated for each target object. Depending on the size of the one-to-many relationship and the size of the source object's row, this can become very inefficient, especially if the source object has a Large Object (LOB).

If you use multiple or nested one-to-many joins in the same query, the problem is compounded: the source object's row is duplicated n*m times, and each target object n and m times respectively. This can become a major performance issue.

To handle empty collections, you must use outer joins, so the queries can easily become very database intensive. Batch reading has the advantage of only returning the required data, and does not require outer joins.

Oracle recommends that you use batch reading to optimize querying relationships in read-all applications.

For more information, see the following:

• Section 12.12.3, "How to Use Batch and Join Reading for Optimization"

• Section 12.12.9.2, "Reading Case 2: Batch Reading Objects"

108.7.1.6 Fetch Groups and Object-Level Read Queries

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.

For more information, see the following:

• Section 16.2.4, "Fetch Groups"

• Section 111.3, "Using Queries with Fetch Groups"

108.7.2 Data-Level Read Query

Using a DataLevelReadQuery, you can query your data source and return Object instances that match the specified selection criteria. This section describes the following:

• DataReadQuery

• DirectReadQuery

• ValueReadQuery

For more information, see Section 109.2.4, "How to Read Data with a DatabaseQuery".

WARNING:

Allowing an unverified SQL string to be passed into constructors of such objects as DataReadQuery, DirectReadQuery and ValueReadQuery makes your application vulnerable to SQL injection attacks.

108.7.2.1 DataReadQuery

Use a DataReadQuery to execute a selecting SQL string that returns a Collection of the Record objects representing the result set.

108.7.2.2 DirectReadQuery

Use a DirectReadQuery to read a single column of data (that is, one field) that returns a Collection of values representing the result set.

108.7.2.3 ValueReadQuery

Use a ValueReadQuery to read a single data value (that is, one field). A single data value is returned, or null if no rows are returned.

108.7.3 Object-Level Modify Query

Using an ObjectLevelModifyQuery, you can query your data source to create, update, and delete objects. This section describes the following:

• WriteObjectQuery

• UpdateObjectQuery

• InsertObjectQuery

• DeleteObjectQuery

• UpdateAllQuery

• DeleteAllQuery

• Object-Level Modify Queries and Privately Owned Parts

For more information, see Section 109.2.2, "How to Create, Update, and Delete Objects with a DatabaseQuery".

Note:

Oracle recommends that you create and update objects using a TopLink UnitOfWork: doing so is the most efficient way to manage transactions, concurrency, and referential constraints. For more information, see Chapter 113, "Introduction to TopLink Transactions".

108.7.3.1 WriteObjectQuery

If you do not know whether or not an object is new, use a WriteObjectQuery: TopLink performs an existence check if necessary to determine whether to perform an insert or an update.

If you do know whether or not an object exists, you can avoid the existence check by using an UpdateObjectQuery (see Section 108.7.3.2, "UpdateObjectQuery") or InsertObjectQuery (see Section 108.7.3.3, "InsertObjectQuery").

108.7.3.2 UpdateObjectQuery

If you know that the object you want to modify exists, use an UpdateObjectQuery to avoid having TopLink perform an existence check.

108.7.3.3 InsertObjectQuery

If you know an object is new, you can use an InsertObjectQuery to avoid having TopLink perform an existence check.

108.7.3.4 DeleteObjectQuery

To delete a specific object, construct a DeleteObjectQuery with a single specific object as an argument.

108.7.3.5 UpdateAllQuery

The UpdateAllQuery allows you to take an expression and update a set of objects (at the object level) without loading the objects into memory. You can updated to either a specific or relative value. For example, you can set the value to 5 or to increase by 5 percent.

For more information, see Section 109.2.3.1, "Using UpdateAll Queries".

108.7.3.6 DeleteAllQuery

To delete multiple objects, construct a DeleteAllQuery and use its setObjects method to configure the collection of specific objects to delete. Use DeleteAllQuery method setReferenceClass to configure the reference class of the objects to delete. Each specified object is deleted, but its privately owned parts are not.

In the case of a DeleteAllQuery, all deletions are performed within a single transaction.

For more information, see Section 109.2.3.2, "Using DeleteAll Queries".

108.7.3.7 Object-Level Modify Queries and Privately Owned Parts

When you execute a create or update object DatabaseQuery, it writes both the object and its privately owned parts to the database by default. To create a query that does not update privately owned parts, use the DatabaseQuery method dontCascadeParts. Use this method to do the following:

• Increase performance when you know that only the object's direct attributes have changed.

• Manually resolve referential integrity dependencies when you write large groups of new, independent objects.

  Note:

  Because the unit of work resolves referential integrity internally, this method is not required if you use the unit of work to write to the data source. For more information, see Chapter 113, "Introduction to TopLink Transactions".

108.7.4 Data-Level Modify Query

Using a DataModifyQuery, you can query your data source to execute a nonselecting SQL statement. It is equivalent to Session method executeNonSelectingCall.

For more information, see Section 109.2.5, "How to Update Data with a DatabaseQuery".

108.7.5 Report Query

If you want to summarize (or roll up) certain attributes of a set of objects, you can use a ReportQuery.

A ReportQuery returns summary data from a set of objects and their related objects. That is, it returns data about objects. It can also return multiple objects. A ReportQuery lets you query and specify the data at the object level. To build a report query, you specify the search criteria, the data you require about the objects, and how that data should be summarized.

For example, you can create a report query to compute the average age of all employees in your company. The report query is not interested in the specific objects (the employees), but rather, summary information about them (their average age).

A ReportQuery lets you do the following:

• Specify a subset of the object's attributes and its related object's attributes, which allows you to query for lightweight information.

• Build complex object-level expressions for the selection criteria and ordering criteria.

• Use data source aggregation functions (supported by your platform), such as SUM, MIN, MAX, AVG, and COUNT.

• Use expressions to group data.

• Request primary key attributes with each ReportQueryResult. This makes it easy to request the real object from a lightweight result.

A ReportQuery is the most efficient form of partial object query (see Section 108.7.1.3, "Partial Object Queries") because it takes advantage of the reporting capabilities of your data source (if available). Oracle recommends that you use ReportQuery to do partial object queries.

The ReportQuery API returns a collection of ReportQueryResult objects, similar in structure and behavior to a Record or a Map. For more information, see Section 108.5.2, "Report Query Results".

For more information, see the following:

• Section 12.12.9.1, "Reading Case 1: Displaying Names in a List"

• Section 109.2.1.3, "Reading Objects Using Report Queries"

• Section 119.7, "Configuring Named Queries at the Descriptor Level"

108.8 Named Queries

When you use a session query method like readAllObjects (see Section 108.6, "Session Queries"), TopLink creates a corresponding ReadAllQuery, which builds other objects it needs to perform its task. When TopLink finishes execution of the readAllObjects method, these objects are discarded. Each time you call this session method, TopLink creates these related objects again, uses them once, and then discards them.

Alternatively, you can create a DatabaseQuery (see Section 108.7, "Database Queries") and store it by name at the descriptor-level (see Section 119.7, "Configuring Named Queries at the Descriptor Level") or session-level (see Section 89.13, "Configuring Named Queries at the Session Level").

TopLink prepares a named query once, and it (and all its associated supporting objects) can be efficiently reused thereafter making a named query well suited for frequently executed operations.

Using the Session API (see Section 109.3, "Using Named Queries"), you can execute these queries by name, passing in any required arguments.

When to Use Named Queries

For a reasonably complex query that you execute frequently, you should consider making the query a named query.

If a query is global to a project, configure the named query at the session level (Section 89.13, "Configuring Named Queries at the Session Level").

If a query is global to a Class or you want to configure CMP finders, configure the named query at the descriptor level (see Section 119.7, "Configuring Named Queries at the Descriptor Level"). For more information about descriptor level query configuration, see Section 108.13, "Descriptor Query Manager Queries".

For a very complex query, you can delegate query execution to your own static method using a special form of a named query called a redirect query. For more information about redirect queries, see Section 108.10, "Redirect Queries".

When Not to Use Named Queries

Rarely used queries may be more efficient when built on an as-needed basis. If you seldom use a given query, it may not be worthwhile to build and store that query when you invoke a session.

108.9 Call Queries

All session types provide executeSelectingCall and executeNonSelectingCall methods that take any of the following Call types:

• SQL Calls

• EJB QL Calls

• Enterprise Information System (EIS) Interactions

You can also execute a Call in the context of a DatabaseQuery. For more information on DatabaseQuery, see Section 108.7, "Database Queries".

WARNING:

Allowing an unverified SQL string to be passed into methods (for example: executeSelectingCall(String sql) method) makes your application vulnerable to SQL injection attacks.

108.9.1 SQL Calls

SQL calls access fields in a relational database. TopLink supports the following SQL calls:

• SQLCall

• StoredProcedureCall, including PLSQLStoreProcedureCall

• StoredFunctionCall

Using the Call API (or SQL string conventions), you can specify input, output, and input-output parameters and assign values for input and input/output parameters.

Using a descriptor ReturningPolicy, you can control whether or not TopLink writes a parameter out, retrieves a value generated by the database, or both. For more information, see Section 119.27, "Configuring Returning Policy".

108.9.1.1 SQLCall

Using a SQLCall, you can specify any arbitrary SQL statement and execute it on a data source.

WARNING:

Allowing an unverified SQL string to be passed into methods makes your application vulnerable to SQL injection attacks.

For more information, see Section 109.4, "Using a SQLCall".

108.9.1.2 StoredProcedureCall

A stored procedure is composed of one or more procedural language statements, such as Procedural Language/Structured Query Language (PL/SQL), stored by name in the database. Most relational databases support stored procedures.

You invoke a stored procedure to execute logic and access data from the data source.

Using a StoredProcedureCall, you can detect execution errors, specify input parameters, output parameters, and input/output parameters. However, stored procedures do not provide a return value.

For more information, see Section 109.5, "Using a StoredProcedureCall".

108.9.1.3 StoredFunctionCall

A stored function is Oracle Database feature that provides all the functionality of a stored procedure as well as the ability to return a value.

Using a StoredFunctionCall, you can specify all the features of a StoredProcedureCall as well as the field name of the return value.

For more information, see Section 109.6, "Using a StoredFunctionCall".

108.9.2 EJB QL Calls

In TopLink, EJB QL calls represent EJB QL strings. An EJBQLCall object is an abstraction of a database invocation. You can execute an EJB QL call directly from a session or in the context of a DatabaseQuery.

For more information, see the following:

• Section 109.7, "Using Java Persistence Query Language (JPQL) Calls"

• Section 109.2.8, "How to Specify a Custom EJB QL String in a DatabaseQuery"

• Section 108.2.8.2, "EJB QL Queries"

108.9.3 Enterprise Information System (EIS) Interactions

To invoke a query through a Java EE Connector Architecture (JCA) adapter to a remote EIS, you use an EISInteraction, an instance of Call. TopLink supports the following EISInteraction types:

• IndexedInteraction

• MappedInteraction

• XMLInteraction

• XQueryInteraction

• QueryStringInteraction

In each of these interactions, you specify a functional interface (similar to a stored procedure) that identifies the function to invoke on the EIS. This functional interface contains the following:

• the function name;

• the record name (if different than the function name);

• a list of input arguments;

• a list of output arguments.

For more information, see the following:

• Chapter 71, "Introduction to EIS Projects"

• Section 109.8, "Using EIS Interactions"

108.9.3.1 IndexedInteraction

In an IndexedInteraction, you exchange data with the EIS using indexed records. The order of the specification of the arguments must match the order of the values defined in the indexed record.

108.9.3.2 MappedInteraction

In a MappedInteraction, you exchange data with the EIS using mapped records. The arguments you specify map by name to fields in the mapped record.

108.9.3.3 XMLInteraction

An XMLInteraction is a MappedInteraction that maps data to an XML record. For an XMLInteraction, you may also provide an optional root element name.

108.9.3.4 XQueryInteraction

If your JCA adapter supports the XQuery dynamic query language, you can use an XQueryInteraction, which is an XMLInteraction that lets you specify your XQuery string.

108.9.3.5 QueryStringInteraction

If your JCA adapter supports a query string based dynamic query language, you can use a QueryStringInteraction, which is a MappedInteraction that lets you specify the dynamic query string.

108.10 Redirect Queries

To accommodate complex query logic, you can implement a redirect query: a named query that delegates query execution control to your application. For more information, see Section 108.8, "Named Queries".

Redirect queries lets you define the query implementation in code as a static method. When you invoke the query, the call redirects to the specified static method. Redirect queries accept any arbitrary parameters passed into them packaged in a Vector.

Although most TopLink queries search for objects directly, a redirect query generally invokes a method that exists on another class and waits for the results. Redirect queries let you build and use complex operations, including operations that might not otherwise be possible within the query framework.

By delegating query invocation to a method you provide, redirect queries let you dynamically make decisions about how a query should be executed based on argument values.

Using a redirect query, you can do the following:

• Dynamically configure the query options based on the arguments (for example, ordering and query optimization).

• Dynamically define the selection criteria based on the arguments.

• Pass query-by-example objects or expressions as the arguments.

• Post-process the query results.

• Perform multiple queries or special operations.

If you execute the query on a UnitOfWork, the results register with that instance of UnitOfWork, so any objects you attempt to retrieve with the invoke method must come from the Session cache.

To create a redirect query, you implement the QueryRedirector interface and set your implementation on a named query.

Oracle recommends that you take advantage of the MethodBasedQueryRedirector, an instance of QueryRedirector that TopLink provides. It takes the name of a static method and the Class in which it is defined as parameters. When you set a MethodBasedQueryRedirector on a named query, whenever invokeQuery method is called on this instance, TopLink uses reflection to invoke your static method instead.

The advantages of using a MethodBasedQueryRedirector are as follows:

• You can specify the static method and its Class dynamically.

• The class that provides the static method does not need to implement QueryRedirector.

• Your static method can have any name.

• You can restrict the parameters to your static method to only a Session and a Vector of arguments.

For more information, see Section 111.1, "Using Redirect Queries".

108.11 Historical Queries

By default, a session represents a view of the most current version of objects and when you execute a query in that session, it returns the most current version of selected objects.

If your data source maintains past or historical versions of objects, you can configure TopLink to access this historical data (see Section 87.6, "Historical Sessions").

Once you configure TopLink to take advantage of this historical data, you can access historical versions using the historical queries that Table 108-5 summarizes.

Note:

Flashback queries do not support view selects. This means you cannot use a flashback query on objects with an inheritance policy for read-all-subclasses views. For more information, see Section 16.3, "Descriptors and Inheritance".

Table 108-5 Historical Queries

Historical Query Type	Session	Cache	Must set maintainCache to false?	Query both current and historical versions?
Using an ObjectLevelReadQuery with an AsOfClause	RegularFoot 1	Global Read-only Contains current versions	Yes	No
Using an ObjectLevelReadQuery with Expression Operator asOf	RegularFootref 1	Global Read and write Contains current versions	No	Yes
Using an ObjectLevelReadQuery in a Historical Session	HistoricalFoot 2	Isolated Read-only Contains static snapshot as of specified time	No	No

^Footnote 1 A server or database session based on an OraclePlatform for an Oracle9i Database (or later), or based on TopLink HistoryPolicy.

^Footnote 2 A session returned by a server or database session based on an OraclePlatform or TopLink HistoryPolicy using the acquireHistoricalSession method passing in an AsOfClause.

108.11.1 Using an ObjectLevelReadQuery with an AsOfClause

You can query historical versions of objects using an ObjectLevelReadQuery configured with an AsOfClause (set by ObjectLevelReadQuery method setAsOfClause) that specifies a point in time that applies to every Expression used in the query.

This type of historical query lets you query a static snapshot of object versions as of the specified time.

Note:

To prevent corrupting the global shared cache with old versions of objects, you must set ObjectLevelReadQuery method maintainCache to false in this historical query. If you do not, TopLink will throw an exception when you execute the query.

For more information and examples of using an ObjectLevelReadQuery with an AsOfClause, see Section 111.2, "Using Historical Queries".

108.11.2 Using an ObjectLevelReadQuery with Expression Operator asOf

You can query historical versions of objects using an ObjectLevelReadQuery (such as ReadObjectQuery or ReadAllQuery) containing one or more expressions that use Expression operator asOf to specify a point in time on an Expression-by-Expression basis.

This type of historical query lets you combine both current and historical versions of objects in the same query.

If you configure the ObjectLevelReadQuery with an AsOfClause, that point in time overrides the point in time specified in any Expression in the query (see Section 108.11.1, "Using an ObjectLevelReadQuery with an AsOfClause").

For more information and examples of using an ObjectLevelReadQuery with Expression operator asOf, see Section 111.2, "Using Historical Queries".

108.11.3 Using an ObjectLevelReadQuery in a Historical Session

Given a session that maintains historical versions of objects (based on an appropriate OraclePlatform or TopLink HistoryPolicy), you can use Session method acquireHistoricalSession passing in an AsOfClause that specifies a point in time that applies to all queries and expressions.

This method returns a lightweight, read-only snapshot of object versions as of the specified time. The cache used in this type of session is isolated from the global shared cache. You do not need to set ObjectLevelReadQuery method maintainCache to false in this case.

For more information and examples of using an ObjectLevelReadQuery with a historical session, see Section 111.2, "Using Historical Queries".

108.12 Interface and Inheritance Queries

When you define an interface descriptor (see Section 22.2.1.3, "Creating Relational Interface Descriptors"), you can perform queries on interfaces and inheritance hierarchies.

For more information, see the following:

• Section 111.5, "Querying on Interfaces"

• Section 111.6, "Querying on an Inheritance Hierarchy"

108.13 Descriptor Query Manager Queries

Each Descriptor owns an instance of DescriptorQueryManager that you can use for the following:

• Configuring named queries (see Section 108.13.1, "How to Configure Named Queries")

• Configuring default query implementation (see Section 108.13.2, "How to Configure Default Query Implementations")

• Configuring additional join expressions (see Section 108.13.3, "How to Configure Additional Join Expressions")

108.13.1 How to Configure Named Queries

The DescriptorQueryManager provides API for storing and retrieving frequently used queries by name.

For more information, see Section 108.8, "Named Queries".

108.13.2 How to Configure Default Query Implementations

The DescriptorQueryManager of each Descriptor lets you customize the query implementation that TopLink uses for the following data source operations:

• insert object

• update object

• read object

• read all objects

• delete object

For example, if you need to insert an object using a stored procedure, you can override the default SQLCall used by the DescriptorQueryManager insert object query.

Whenever you execute a query on a given Class, TopLink consults the DescriptorQueryManager to determine how to perform the given data source operation.

You can use this capability for a variety of purposes such as to extend TopLink behavior, access nonrelational data, or use stored procedures or customized SQL calls.

WARNING:

Allowing an unverified SQL string to be passed into methods makes your application vulnerable to SQL injection attacks.

For information and examples on customizing these default query implementations, 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"

108.13.3 How to Configure Additional Join Expressions

You can configure the DescriptorQueryManager to automatically append an expression to every query it performs on a class. For example, you can add an expression that filters the data source for the valid instances of a given class.

For more information, see Section 111.7, "Appending Additional Join Expressions".

108.14 Oracle Extensions

When you use TopLink with Oracle Database, you can make use of the following Oracle specific query features from within your TopLink applications:

• Hints

• Hierarchical Queries

• Flashback Queries

• Stored Functions

108.14.1 Hints

Oracle lets you specify SQL query additions called hints that can influence how the database server SQL optimizer works. This lets you influence decisions usually reserved for the optimizer. You use hints to specify things such as join order for a join statement, or the optimization approach for a SQL call.

You specify hints using the DatabaseQuery method setHintString.

For more information, see the following:

• Section 108.7, "Database Queries"

• Section 111.9.1, "How to Use Oracle Hints"

• Your database Performance Tuning Guide and Reference.

108.14.2 Hierarchical Queries

Oracle Database Hierarchical Queries mechanism 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 the employee manages, followed by their managed employees, and so on.

You specify a hierarchical query clause using DatabaseQuery subclass ReadAllQuery method setHierarchicalQueryClause. For more information on DatabaseQuery queries, see Section 108.7, "Database Queries".

For more information on configuring a ReadAllQuery with an Oracle hierarchical query clause, see Section 111.9.2, "How to Use Hierarchical Queries".

108.14.3 Flashback Queries

When using TopLink with Oracle9i Database (or later), you can acquire a special historical session where all objects are read as of a past time, and then you can express read queries depending on how your objects are changing over time.

For more information, see Section 108.11, "Historical Queries".

108.14.4 Stored Functions

A stored function is Oracle Database mechanism that provides all the capabilities of a stored procedure in addition to returning a value.

For more information, see Section 108.9.1.3, "StoredFunctionCall".

108.15 EJB 2.n CMP Finders

An EJB finder is a query as defined by the EJB specification. It returns EJB, collections, and enumerations. The difference between a finder and a query is that queries return Java objects, but finders return EJB. The TopLink query framework lets you create and execute complex finders that retrieve entity beans.

Finders contain finder methods that define search criteria. The work involved in creating these methods depends on whether you are building container-managed persistence (CMP) bean finders or bean-managed persistence (BMP) bean finders:

• CMP finders require you to define the finder API method signature on the bean Home interface. The CMP provider generates the actual code mechanisms for the finder from the API definition.

• BMP finders require you to provide the code required to execute the finder methods.

In either case, you define finders in the Home interface of the bean.

You can implement finders using any TopLink query feature and you can take advantage of predefined finder implementations that TopLink provides for both CMP and BMP entity beans.

This section describes the following:

• Predefined Finders

• Default Finders

• Call Finders

• DatabaseQuery Finders

• Named Query Finders

• Primary Key Finders

• Expression Finders

• EJB QL Finders

• SQL Finders

• Redirect Finders

• The ejbSelect Method

For more information, see Section 111.10, "Using EJB 2.n CMP Finders".

108.15.1 Predefined Finders

TopLink provides predefined finder implementations that provide a rich API that lets you dynamically specify query properties at run time and take full advantage of TopLink query features.

TopLink provides the following predefined finders:

• Predefined CMP Finders

• Predefined BMP Finders

For more information, see the following:

• Section 2.14, "Considering EJB Entity Beans with CMP Architecture"

• Section 2.15, "Considering EJB Entity Beans with BMP Architecture"

108.15.1.1 Predefined CMP Finders

Table 108-6 lists the predefined finders you can use with TopLink CMP (using OC4J).

The TopLink runtime reserves the method names listed in Table 108-6.

Table 108-6 Predefined CMP Finders

Method	Arguments	Return
findAll	()	Collection
findManyByEJBQL	(String ejbql) (String ejbql, Vector arguments)	Collection
findManyByQuery	(DatabaseQuery query)(DatabaseQuery query, Vector arguments)	Collection
findManyBySQL	(String sql)(String sql, Vector arguments)	Collection
findByPrimaryKey	(Object primaryKeyObject)	EJBObject
findOneByEJBQL	(String ejbql) (String ejbql, Vector arguments)	EJBObject
findOneByQuery	(DatabaseQuery query)(DatabaseQuery query, Vector arguments)	EJBObject
findOneBySQL	(String sql)(String sql, Vector arguments)	EJBObject

Note:

If the finder is located on a local home, replace EJBObject with EJBLocalObject in finders that contain findOneBy.

You can also use each of these finders without a vector of arguments. For example, EJBObject findOneByEJBQL(String ejbql) is a valid dynamic finder, but you must replace the return type of EJBObject with your bean's component interface.

For more information, see Section 111.10, "Using EJB 2.n CMP Finders".

108.15.1.2 Predefined BMP Finders

Table 108-7 lists the predefined finders you can use if you extend your BMP EJB from oracle.toplink.ejb.bmp.BMPEntityBase (see Section 2.15, "Considering EJB Entity Beans with BMP Architecture").

The TopLink runtime reserves the method names listed in Table 108-7.

Table 108-7 Predefined BMP Finders

Method	Arguments	Return
findAll	()(Call)(Expression)(ReadAllQuery)	Enumeration
findAllByNamedQuery	(String queryName, Vector arguments)	Enumeration
findByPrimaryKey	(Object primaryKeyObject)	Object
findOne	(Call) (Expression) (ReadObjectQuery)	Object
findOneByNamedQuery	(String queryName, Vector arguments)	Object

For more information about using EJB finders, see Section 111.10, "Using EJB 2.n CMP Finders".

108.15.2 Default Finders

For each finder method defined on the home interface of an entity bean, whose name matches findBy<CMP-FIELD-NAME> where <CMP-FIELD-NAME> is the name of a persistent field on the bean, TopLink generates a finder implementation including a TopLink query that uses the TopLink expressions framework. If the return type is a single bean type, TopLink creates a ReadObjectQuery; if the return type is a Collection, TopLink creates a ReadAllQuery.

Although you must still define the finder in the entity home, you do not need to declare the finder in the ejb-jar.xml file.

For more information, see Section 111.10.1, "How to Create a Finder".

108.15.3 Call Finders

Finders that use a Call let you create dynamic queries that you generate at run time rather than at deployment time.

For more information, see the following:

• Section 108.9, "Call Queries".

• Section 108.15.1, "Predefined Finders"

108.15.4 DatabaseQuery Finders

Finders that use a DatabaseQuery lets you create dynamic queries that you generate at run time rather than at deployment time.

In addition to finders that take a DatabaseQuery, TopLink also provides a default findAll finder that returns all the EJB of a given type. As with other dynamic finders, the TopLink runtime reserves the name findAll.

For more information, see Section 108.7, "Database Queries".

For more information on TopLink predefined finders that take a DatabaseQuery, see Section 108.15.1, "Predefined Finders".

108.15.5 Named Query Finders

Finders that use a named DatabaseQuery stored in a DescriptorQueryManager or Session let you efficiently reuse frequently executed queries.

For more information, see the following:

• Section 108.8, "Named Queries"

• Section 108.15.1, "Predefined Finders"

108.15.6 Primary Key Finders

TopLink provides predefined finder implementations that take a primary key class as a Java Object.

Because the EJB 2.0 and 2.1 specifications requires the container to implement the findByPrimaryKey call on each bean Home interface, do not delete this finder from a bean.

For more information, see Section 108.15.1, "Predefined Finders".

108.15.7 Expression Finders

Using a finder based on a TopLink Expression offers the following advantages:

• Version-controlled standardized queries in Java code.

• Ability to simplify most complex operations.

• A more complete set of querying features than is available through EJB QL.

Because expressions lets you specify finder search criteria based on the object model, they are frequently the best choice for constructing your finders.

For more information, see Section 108.2.6, "TopLink Expressions".

For more information on TopLink predefined finders that take an Expression, see Section 108.15.1, "Predefined Finders".

You can also use an Expression in a finder that takes a DatabaseQuery by using DatabaseQuery method setSelectionCriteria. For more information on TopLink predefined finders that take a DatabaseQuery, see Section 108.15.4, "DatabaseQuery Finders".

108.15.8 EJB QL Finders

TopLink supports EJB QL. EJB QL finders let you specify an EJB QL string as the implementation of the query.

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.

For more information about EJB QL support in TopLink, see Section 108.2.8, "Query Languages".

For more information on TopLink predefined finders that take EJB QL, see Section 108.15.1, "Predefined Finders".

108.15.9 SQL Finders

Using SQL to define a finder offers the following advantages:

• You can implement logic that cannot be expressed when you use EJB QL or a TopLink Expression.

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

• There may be cases in which custom SQL will improve performance.

SQL finders also have the following disadvantages:

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

• Hard-coded SQL limits portability to other databases.

• No validation is performed on the SQL string. Errors in SQL statements will not be detected until run time.

• The use of SQL for a function other than SELECT may result in unpredictable errors.

For more information on TopLink predefined finders that take SQL, see Section 108.15.1, "Predefined Finders".

108.15.10 Redirect Finders

Redirect finders enable you to implement a finder that is defined on an arbitrary helper class as a static method. When you invoke the finder, TopLink redirects the call to the specified static method.

Redirect queries are complex and 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.

For more information, see the following:

• Section 108.10, "Redirect Queries"

• Section 111.10, "Using EJB 2.n CMP Finders"

108.15.11 The ejbSelect Method

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.

ejbSelect 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 looks as follows:

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, with the following exception: In the case that the ejbSelect method return type is a java.util.Collection, the result must be the entity type on which the selector was defined.

Although 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. This creates a query that is logically scoped to a particular entity bean instance.

For more information and examples on using TopLink queries in the ejbSelect method, see Section 111.10, "Using EJB 2.n CMP Finders".

108.16 Queries and the Cache

When you execute a query, TopLink retrieves the information from either the database or the TopLink session cache. You can configure the way queries use the TopLink cache to optimize performance.

TopLink maintains a client-side cache to reduce the number of read operations required from the database. TopLink caches objects written to and read from the database to maintain object identity. The sequence in which a query checks the cache and database affects query performance. By default, primary key queries check the cache before accessing the database, and all queries check the cache before rebuilding an object from its row.

Note:

You can override the default behavior in the caching policy configuration information in the TopLink descriptor. For more information, see Section 102.2.4, "Explicit Query Refreshes".

This section illustrates ways to manipulate the relationship between query and cache, and explains the following:

• How to Configure the Cache

• How to Use In-Memory Queries

• Primary Key Queries and the Cache

• How to Disable the Identity Map Cache Update During a Read Query

• How to Refresh the Cache

• How to Cache Query Results in the Session Cache

• How to Cache Query Results in the Query Cache

• How to Use Caching and EJB 2.n CMP Finders

108.16.1 How to Configure the Cache

The cache in a TopLink application holds objects that have already been read from or written to the database. Use of the cache in a TopLink application reduces the number of accesses to the database. Because accessing the database consumes time and resources, an effective caching strategy is important to the efficiency of your application.

For more information about configuring and using the cache, see Chapter 102, "Introduction to Cache".

108.16.2 How to Use In-Memory Queries

An in-memory query is a query that is run against the shared session cache. Careful configuration of in-memory querying improves performance, but not all queries benefit from in-memory querying. For example, queries for individual objects based on primary keys generally see performance gains from in-memory querying; queries not based on primary keys are less likely to benefit.By default, queries that look for a single object based on primary keys attempt to retrieve the required object from the cache first, and then to search the database if the object is not in the cache. All other query types search the database first, by default. You can specify whether a given query runs against the in-memory cache, the database, or both. In-memory querying lets you perform queries on the cache rather than the database.

Note:

You cannot expect an ordered result from an in-memory query as ordering is not supported for these queries.

In-memory querying supports the following relationships:

• One-to-one

• One-to-many

• Many-to-many

• Aggregate collection

• Direct collection

  Note:

  By default, the relationships themselves must be in memory for in-memory traversal to work. Ensure that you trigger all value holders to enable in-memory querying to work across relationships.

This section describes the following:

• Configuring Cache Usage for In-Memory Queries

• Expression Options for In-Memory Queries

• Handling Exceptions Resulting from In-Memory Queries

108.16.2.1 Configuring Cache Usage for In-Memory Queries

You can configure in-memory query cache usage at the query level using ReadObjectQuery and ReadAllQuery methods:

• checkCacheByPrimaryKey: The default setting; if a read-object query contains an expression that compares at least the primary key, you can obtain a cache hit if you process the expression against the objects in memory.

• checkCacheByExactPrimaryKey: If a read-object query contains an expression where the primary key is the only comparison, you can obtain a cache hit if you process the expression against the object in memory.

• checkCacheThenDatabase: You can configure any read-object query to check the cache completely before you resort to accessing the database.

• checkCacheOnly: You can configure any read-all query to check only the parent session cache (not the unit of work cache) and return the result from the parent session cache without accessing the database.

• conformResultsInUnitOfWork: You can configure any read-object or read-all query within the context of a unit of work to conform the results with the changes to the object made within that unit of work. This includes new objects, deleted objects and changed objects. For more information and limitations on conforming, see Section 115.4, "Using Conforming Queries and Descriptors".

Alternatively, you can configure cache usage using the ObjectLevelReadQuery method setCacheUsage, passing in the appropriate ObjectLevelReadQuery field: CheckCacheByPrimaryKey, CheckCacheByExactPrimaryKey, CheckCacheThenDatabase, CheckCacheOnly, ConformResultsInUnitOfWork, or DoNotCheckCache.

108.16.2.2 Expression Options for In-Memory Queries

You can use a subset of Expression (see Table 108-8) and ExpressionMath (see Table 108-9) methods with in-memory queries. For more information about these options, see Chapter 110, "Introduction to TopLink Expressions".

Table 108-8 Expressions Operator Support for In-Memory Queries

Expressions Operator	In-Memory Query Support
addMonths
and
anyofFoot 1
anyofAllowingNoneFootref 1
asciiValue
between
concat
currentDate
dateToString
decode
equal
getFootref 1
getAllowingNullFootref 1
getFunction
greaterThan
greaterThanEqual
hexToRaw
ifNull
in
isNull
lastDay
leftPad
leftTrim
length
lessThan
lessThanEqual
like
monthsBetween
newTime
nextDay
notBetween
notIn
notNull
or
ref
replace
rightPad
rightTrim
subQuery
substring
toCharacter
toDate
toLowerCase
toNumber
toUpperCase
toUpperCasedWords
translate
trim
truncateDate

^Footnote 1 For more information, see Section 108.7.1.5, "Join Reading and Object-Level Read Queries".

Table 108-9 ExpressionMath Operator Support for In-Memory Queries

ExpressionMath Operator	In-Memory Query Support
abs
acos
add
asin
atan
atan2
ceil
chr
cos
cosh
exp
floor
ln
log
max
min
mod
none
power
round
sign
sin
sinh
sqrt
subtract
tan
tanh
trunc

108.16.2.3 Handling Exceptions Resulting from In-Memory Queries

In-memory queries may fail for several reasons, the most common of which are the following:

• The query expression is too complex to execute in memory.

• There are untriggered value holders in which indirection (lazy loading) is used. All object models that use indirection must first trigger value holders before they conform on the relevant objects.

TopLink provides a mechanism to handle indirection exceptions. To specify how the application must handle these exceptions, use the following InMemoryQueryIndirectionPolicy methods:

• throwIndirectionException: The default setting; it is the only setting that throws indirection exceptions.

• triggerIndirection: Triggers all valueholders to eliminate the problem.

• ignoreIndirectionExceptionReturnConformed: Returns conforming if an untriggered value holder is encountered. That is, results from the database are expected to conform, and an untriggered value holder is taken to mean that the underlying attribute has not changed.

• ignoreIndirectionExceptionReturnNotConformed: Returns not conforming if an untriggered value holder is encountered.

  Note:

  When you build new applications, consider throwing all conform exceptions. This provides more detailed feedback for unsuccessful in-memory queries. For more information, see Section 115.16.4.2, "Handling Exceptions During Conforming".

108.16.3 Primary Key Queries and the Cache

When a query searches for a single object by a primary key, TopLink extracts the primary key from the query and attempts to return the object from the cache without accessing the database. If the object is not in the cache, the query executes against the database, builds the resulting object(s), and places it in the identity map.

If the query is based on a nonprimary key selection criteria or is a read-all query, the query executes against the database (unless you are using ReadObjectQuery or ReadAllQuery method checkCacheOnly). The query matches primary keys from the result set to objects in the cache, and returns the cached objects, if any, in the result set.

If an object is not in the cache, TopLink builds the object. If the query is a refreshing query, TopLink updates the contents of any objects with the results from the query. Use "equals" on the object identity to properly configure and use an identity map.

Clients can refresh objects when they want to ensure that they have the latest data at a particular time.

Traversing Relationships with Compound Primary Keys

When getting objects by using compound primary keys to traverse relationships, you must create use query keys (see Section 110.4, "Query Keys and Expressions"). By adding a query key for each mapped attribute in a class with a complex primary key, TopLink can use the primary key on the cache.

Consider the class MyClass with two attributes: A and B. Both A and B are mapped as 1:1 mappings to the database and designated primary keys.

You should create a query key for each attribute (such as MyQueryKeyA and MyQueryKeyB) that will map the attributes of the primary key of MyClass without going through the other classes. You can then use the query key to find the object in the cache and query the object's primary key:

builder.get("MyQueryKeyA").equal(new Long("123456"));

108.16.4 How to Disable the Identity Map Cache Update During a Read Query

To disable the identity map cache update, which is normally performed by a read query, call the dontMaintainCache method. This improves the query performance when you read objects that are not needed later by the application and can avoid exceptions during partial object queries (see Section 109.2.1.2, "Reading Objects Using Partial Object Queries").

Example 108-1 demonstrates how code reads Employee objects from the database and writes the information to a file.

Example 108-1 Disabling the Identity Map Cache Update

// Reads objects from the employee table and writes them to an employee file 
void writeEmployeeTableToFile(String filename, Session session) {
    Vector employeeObjects;
    // Create ReadAllQuery and set Employee as its reference class
    ReadAllQuery query = new ReadAllQuery(Employee.class);
    ExpressionBuilder builder = query.getExpressionBuilder();
    query.setSelectionCriteria(builder.get("id").greaterThan(100)); 
    query.dontMaintainCache();
    Vector employees = (Vector) session.executeQuery(query);
    // Write all the employee data to a file
    Employee.writeToFile(filename, employees);
}

108.16.5 How to Refresh the Cache

You can refresh objects in the cache to ensure that they are current with the database, while preserving object identity. This section describes how to use query API to perform the following:

• Configure query refreshing at the descriptor level (see Section 119.9, "Configuring Cache Refreshing") to apply cache refreshing to all queries of a particular object type. Before configuring cache refresh options, consider their effect on performance (see Section 12.10, "Optimizing Cache").

108.16.5.1 Object Refresh

To refresh objects in the cache with the data in the database, call the Session method refreshObject or the ReadObjectQuery method setShouldRefreshIdentityMapResult(true).

108.16.5.2 Cascading Object Refresh

You can control the depth at which a refreshing updates objects and their related objects. There are the following three options:

1. CascadePrivateParts: Default refresh behavior. Refreshes the local level object and objects that are referenced in privately owned relationships.

2. CascadeNone: Refreshes only the first level of the object, but does not refresh related objects.

3. CascadeAll: Refreshes the entire object tree, stopping when it reaches leaf objects.

4. CascadeMapping: Refreshes each mapping that is configured to cascade refresh.

108.16.5.3 Refreshing the Identity Map Cache During a Read Query

Include the refreshIdentityMapResult method in a query to force refreshing of an identity map with the results of the query, as the following example shows:

Example 108-2 Refreshing the Result of a Query in the Identity Map Cache During a Read Query

// Create ReadObjectQuery and set Employee as its reference class
ReadObjectQuery query = new ReadObjectQuery(Employee.class);
ExpressionBuilder builder = query.getExpressionBuilder();
query.setSelectionCriteria(builder.get("lastName").equal("Smith")); 
query.refreshIdentityMapResult();
Employee employee = (Employee) session.executeQuery(query);

The refreshIdentityMapResult method refreshes the object's attributes, but not the attributes of its privately owned parts. However, under most circumstances, you should refresh an object's privately owned parts and other related objects to ensure consistency with the database.

To refresh privately owned or related parts, use the following methods:

• cascadePrivateParts: Refreshes all privately owned objects

• cascadeAllParts: Refreshes all related objects

Example 108-3 Using the cascadePrivateParts Method

ReadAllQuery query = new ReadAllQuery(Employee.class);
query.refreshIdentityMapResult();
query.cascadePrivateParts();
Vector employees = (Vector) session.executeQuery(query);

Note:

If the object is in the session cache, you can also use the refreshObject method to refresh an object and its privately owned parts.

108.16.6 How to Cache Query Results in the Session Cache

By default, TopLink stores query results in the session cache enabling TopLink to execute the query repeatedly, without accessing the database. This is useful when you execute queries that run against static data.

By default, a read-all query always goes to the database, as it does not know how many objects it is seeking. However if the object already exists in the cache, time can be saved by not having to build a new object from the row.

For more information, see Chapter 102, "Introduction to Cache".

108.16.7 How to Cache Query Results in the Query Cache

In addition to TopLink's object cache, TopLink also supports a query cache. There is the following distinction between the two:

• The object cache indexes objects by their primary key, allowing primary key queries to obtain cache hits. By using the object cache, queries that access the data source can avoid the cost of building the objects and their relationships if the object is already present.

• The query cache is distinct from the object cache. The query cache is indexed by the query and the query parameters–not the object's primary key. This allows for any query executed with the same parameters to obtain a query cache hit and return the same result set.

By default, a ReadQuery does not cache its query result set. You can, however, configure the query to cache its result set. This is useful for frequently executed queries whose result set infrequently changes. The query cache always maintains hard references to the result set; the number of results sets for distinct parameters stored in the query cache is configurable. The query cache maintains its size number of the last executed queries with distinct parameters.

For more information, see Section 111.13.1, "How to Cache Results in a ReadQuery".

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".

108.16.7.1 Internal Query Cache Restrictions

TopLink does not support the use of the query cache with cursors: if you use query caching with cursors, TopLink will throw an exception. For information on cursor query results, see Section 108.5.3, "Stream and Cursor Query Results" and Section 111.11, "Handling Cursor and Stream Query Results".

108.16.8 How to Use Caching and EJB 2.n CMP Finders

TopLink caches enterprise beans that EJB finders retrieve. For your application, you can configure the caching of the EJB finders' results in a variety of ways, force the cache to be refreshed, or disable the caching.

This section describes the following:

• Caching Options

• Disabling Cache for Returned Finder Results

• Refreshing Finder Results

108.16.8.1 Caching Options

You can apply various configurations 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, you can set caching options using Oracle JDeveloper or TopLink Workbench.

You can set the caching options on a per-finder basis. Table 108-10 lists the valid values.

Table 108-10 Finder Caching Options

This Setting . . .	Causes Finders to . . .	When the Search Involves a Finder That . . .
ConformResultsInUnitOfWorkFoot 1	Check the unit of work cache before querying the session cache or the database. The finder's results always conform to uncommitted new, deleted, and changed objects.	Returns either a single bean or a collection.
DoNotCheckCache	Query the database, bypassing the TopLink internal caches.	Returns either a single bean or a collection.
CheckCacheByExactPrimaryKey	Check the session cache for the object.	Contains only a primary key, and returns a single bean.
CheckCacheByPrimaryKey	Check the session cache for the object.	Contains a primary key (and may contain other search parameters), and returns a single bean.
CheckCacheThenDatabase	Search the session cache before accessing the database.	Returns a single bean.
CheckCacheOnly	Search the parent session cache only (not the unit of work cache), but not the database.	Returns either a single bean or a collection.

^Footnote 1 Default.

For more information about the TopLink queries, as well as the TopLink unit of work and how it integrates with JTS, see Chapter 113, "Introduction to TopLink Transactions".

Note:

To apply caching options to finders with manually created queries (findOneByQuery, findManyByQuery), use the TopLink API.

108.16.8.2 Disabling Cache for Returned Finder Results

By default, TopLink adds all returned objects to the session cache. However, if you know the set of returned objects is very large, and you want to avoid the expense of storing these objects, you can disable this behavior. To override the default configuration, implement the dontMaintainCache method on the query, or disable returned object caching for the query in Oracle JDeveloper or TopLink Workbench.

108.16.8.3 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 Oracle JDeveloper and TopLink Workbench) causes the query to refresh the object's nonprimary key attributes with the returned information. This occurs on findByPrimaryKey finders as well as all expression and SQL finders for the bean.

If you build a query in Java code, you can set this option by including the refreshIdentityMapResult method. This method automatically cascades changes to privately owned parts of the beans. If you require different behavior, configure the query using a dynamic finder instead.

Note:

When you invoke this option from within a transaction, the refresh action overwrites object attributes, including any that have not yet been written to the database.

If your application includes an OptimisticLock field, use the refresh cache option in conjunction with the onlyRefreshCacheIfNewerVersion option. This ensures that the application refreshes objects in the cache 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.

108.17 Query API

Table 108-11 summarizes the query support provided by each type of session. For each session type, it shows the type of query operation (create, read, update, delete) that you can perform and whether or not you can execute a DatabaseQuery or Call. For example, using a unit of work, you can use session queries to read and delete; using a server session, you can use session queries to create, read, update, and delete.

Table 108-11 Session Query API Summary

Session	Create	Read	Update	Delete	Execute Database Query	Execute Call
Unit of work
Database
Server
Client

Example 108-4 summarizes the important TopLink packages that provide query and expression support:

Example 108-4 Query and Expression Packages

oracle.toplink.queryframework
oracle.toplink.expressions
oracle.toplink.querykeys
oracle.toplink.descriptors.DescriptorQueryManager