Oracle® Application Server TopLink Application Developer's Guide
10g Release 2 (10.1.2)
Part No. B15901-01
Queries are the cornerstone of OracleAS TopLink applications. Queries enable you to retrieve information or objects from the database, modify or delete those objects, and create new objects on the database.
The following concepts are key to understanding OracleAS TopLink queries:
The type of query you build determines the type of result set the query returns. You can build:
Object queries that return an object or objects
Summary queries that return partial information about an object or objects
Data queries that return raw data
Object write queries that modify the objects in the database
Object queries, the most common query type in an OracleAS TopLink application, enable you to search a database for persistent objects. OracleAS TopLink offers two object query mechanisms: a
readObject query that searches the database for a single object that matches the search criteria, and a
readAll query that searches for all matching objects.
Object queries search for objects rather than data. For example, a query to find all employees over the age of 40 searches for objects—the employees.
Summary queries enable you to search for partial information about objects that match your search criteria. There are two types of summary queries:
Report queries return data from the database tables that represent a portion of the available information. To build a report query, specify the search criteria and the information you require about the objects in the result set.
Report queries search for information about objects rather than the objects themselves. For example, you can create a report query to discover 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).
For more information, see "ReportQuery".
Partial object queries retrieve partially populated objects from the database rather than complete objects. You do not cache partial objects, nor can you modify them.
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 that the user can select the required object or objects from the list.
For more information, see "Partial Object Reading".
Data queries enable you to query data fields rather than objects directly from the database tables. Data queries represent a common approach to working with unmapped data, such as foreign keys and object version fields.
Object write queries enable you to modify data and objects directly on the database. You can use write queries to insert and update objects on the database. Write queries are useful when you manage simple, nonbusiness object data that have no relationships, such as user preferences.
For more information about write queries, see "Query Objects and Write Operations".
To avoid concurrency issues when you write more complex data to the database, use the Unit of Work.
For more information, see "Unit of Work Basics".
Query components are the mechanisms with which you build your query. These components include:
Advanced query mechanisms, such as query by example, OracleAS TopLink expressions, and database stored procedures
Query languages and syntaxes, such as SQL and EJB QL
The OracleAS TopLink expression framework is a querying syntax. Expressions enable you to specify search criteria in a query, based on the object model. They provide support for standard boolean operators, such as AND, OR, and NOT and support many database functions and operators.
You can create expressions in OracleAS TopLink Mapping Workbench or in the OracleAS TopLink API.
For more information, see "Expressions".
Limited in complexity, query by example is an intuitive way to express a query. To specify a query by example, provide sample instances of the persistent objects to query, and specify the fields and values that define the query. You can use any valid constructor to create an example object.
For more information, see "Query by Example".
A stored procedure is a function, such as Procedural Language/Structured Query Language (PLSQL) statement or Java code, written on the database. Stored procedures enable you to execute logic and access data on the database server.
For more information, see "Stored Procedure Calls".
EJB QL presents queries from an object model perspective, enabling users to declare queries using the attributes of each abstract entity bean in the object model. EJB QL includes path expressions that enable navigation over relationships defined for entity beans and dependent objects.
OracleAS TopLink enables you to use EJB QL to define both queries that return Java objects and finders that return EJBs.
For more information, see "EJB QL".
SQL is a standard query language that enables you to request information from a database. The use of a native query language such as SQL is complex, but it offers advantages that are unavailable with other querying options.
For more information, see "Custom SQL".
OracleAS TopLink queries offer several configuration options to customize query execution, cache usage, and performance.
The following query execution options enable you to optimize the way in which you collect and present query results.
You can specify an order for the results of a query.
For more information, see "Ordering for Read All Queries".
By default, a query that returns a collection of objects presents the objects in a vector. You can specify that the collection be returned in any collection class that implements the
Map interface (for example,
For more information, see "Collection Classes".
You can set a maximum row size on any read query to limit the size of the result set. Use this option to manage queries that can return an excessive number of objects.
For more information, see "Maximum Rows Returned" .
You can set the maximum amount of time that OracleAS TopLink waits for results from a query. This option forces a hung or lengthy query to abort after the specified time has elapsed.
For more information, see "Query Timeout".
When you execute a query, OracleAS TopLink retrieves the information from either the database or the OracleAS TopLink session cache. You can configure the way in which queries use the OracleAS TopLink cache to optimize performance.
Refresh the cache to update all objects in the cache with information from the database. This ensures that all objects in the cache are current.
For more information, see "Refresh".
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 usually see performance gains from in-memory querying; queries based on nonprimary 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 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.
For more information, see "In-Memory Query Cache Usage".
By default, OracleAS TopLink stores query results in the session cache, enabling OracleAS TopLink to execute the query repeatedly without accessing the database. This is useful when you execute queries that run against static data.
Because it does not know how many objects it is looking for, by default a read all query always goes to the database. However, if the object already exists in the cache, time is saved by not having to build a new object from the row.
For more information, see "Caching Query Results".
You can configure a query to maintain an internal cache of the objects returned by the query. This internal cache is disabled by default.
For more information, see "Cache Results In Query Objects".
OracleAS TopLink offers several query options to improve performance, including the following:
Binding and Parameterized SQL: Enables you to create and store queries that are complete except for one or more search parameters. To enhance query performance, invoke the query, and bind parameters to the query. This can improve query performance.
For more information about binding and parameterized SQL, see "Binding and Parameterized SQL".
Batch and Join Reading: To optimize database reads, OracleAS TopLink supports both batch and join reading. When you use these techniques, you dramatically decrease the number of times you access the database during a read operation, especially when your result set contains a large number of objects.
For more information about batch and join reading, see "Query Object Performance Options".
Partial Object Reading: Partial object queries enable you to retrieve partially populated objects rather than complete objects from the database.
For more information about partial object reading, see "Partial Object Reading".
Java Streams: Enable you to retrieve data from the database in cursored Java streams. A cursored stream allows you to view a collection in manageable increments rather than as a complete collection. This is useful when you have a large result set.
For more information about Java streams, see "Java Streams".
Scrollable Cursors: Retrieves the result set from a query on a row-by-row basis. This is useful when you want to operate on the rows individually.
For more information about scrollable cursors, see "Cursors and Streams".
Queries that write to the database are often executed within a Unit of Work. You can also execute read queries within a Unit of Work, although reading the database this way is not common. Two key configuration options are available when you query within the Unit of Work:
Registering Results: When you execute a read query within a Unit of Work, the Unit of Work registers the objects in the result set and returns clones to the Unit of Work cache. If you do not need to modify any of the returned objects, consider executing your query through a regular session.
For more information about read queries within the Unit of Work, see "Reading and Querying Objects with the Unit of Work".
Conform Results to Unit of Work: The OracleAS TopLink conforming feature enables you to query against your relative logical or transaction view of the database. By default, queries are executed on the database. Uncommitted changes can pose a problem in a Unit of Work, because uncommitted changes not yet written to the database cannot influence which result set gets returned.
For more information, see "Conforming Results (UnitOfWork)".
There are two ways to build OracleAS TopLink queries: You can use OracleAS TopLink Mapping Workbench, or you can build them in code using the OracleAS TopLink API.
OracleAS TopLink Mapping Workbench Query tab supports OracleAS TopLink expressions, EJB QL queries and finders, and custom SQL queries and finders.
For more information, see "Specifying Named Queries and Finders" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
As with OracleAS TopLink Mapping Workbench, the OracleAS TopLink query API supports OracleAS TopLink expressions, EJB QL queries and finders, and custom SQL queries and finders. However, if you require more options than are offered by these selection criteria types, you can create queries using the OracleAS TopLink query API to leverage OracleAS TopLink support for query by example and stored procedures.
For more information about the OracleAS TopLink query API, see the Oracle Application Server TopLink API Reference.
An effective way to implement queries is to build predefined queries that you store as part of the project descriptor file. OracleAS TopLink loads the queries into the application at runtime.
Named Queries are defined in the session and called by name from the session. You can create named queries with OracleAS TopLink Mapping Workbench or in Java code.
Redirect Queries allow you to define the query implementation in code as a static method. When you invoke the query, the call redirects to the specified static method. The query can include any arbitrary parameters (or none at all), packaged into a vector and passed to the redirect method.
For more information, see "Predefined Queries".
Named queries are complete, self-contained queries stored in the project descriptor file. Using named queries improves your application performance because it reduces the resources required to run a query.
You can create queries in OracleAS TopLink Mapping Workbench using OracleAS TopLink Mapping Workbench Query tab. The queries you build in the Query tab become part of the OracleAS TopLink project: OracleAS TopLink exports them automatically when you create deployment files from the project.
For more information, see "Specifying Named Queries and Finders" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
The OracleAS TopLink query API enables you to build queries outside of OracleAS TopLink Mapping Workbench. However, unlike queries built in OracleAS TopLink Mapping Workbench, OracleAS TopLink does not include these queries automatically in your OracleAS TopLink application. Instead, add them to the application manually, using after load methods to amend the project descriptor.
For more information about after load methods, see "Customizing OracleAS TopLink Descriptors with Amendment Methods".
Although most OracleAS TopLink queries search for objects directly, a redirect query generally invokes a method that exists on another class and waits for the results of the remote query. Redirect queries enable you to build and use complex operations, including operations that may not otherwise be possible within the query framework.
For more information, see "Redirect Queries".
An EJB finder is a query as defined by the EJB specification. It returns EJBs, collections, and enumerations. The difference between a finder and a query is that queries return Java objects, and finders return EJBs. The OracleAS TopLink query framework enables you to 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.
For more information, see "EJB Finders".
A query key is an alias that OracleAS TopLink expressions use to relate to the descriptors and mappings for a given class. The query key is generally the name of an attribute of the class.
For example, consider a database table that includes a column called
F_NAME that represents the attribute
firstName in the class. Both represent the concept of the first name of an object. OracleAS TopLink expressions use a query key to relate the two when you query on the database using the
firstName as a selection criteria.
By default, OracleAS TopLink builds a query key in a descriptor for each attribute you map and automatically creates query keys for all mapped attributes of a class. The default name of the query key is the same as the name of the mapping.You can add additional query keys for nonmapped or duplicate purpose fields, either in Java code or using OracleAS TopLink Mapping Workbench.
For more information, see "Working with Query Keys" in the Oracle Application Server TopLink Mapping Workbench User's Guide.