Advanced Querying

OracleAS TopLink offers several advanced mechanisms and techniques that enhance your queries. This section describes the following:

Creating Additional Query Keys
Querying on Interfaces
Querying on an Inheritance Hierarchy
Cursors and Streams
Querying Across Variable One-to-One Mappings

Creating Additional Query Keys

A query key is an alias for a field name. Instead of referring to a field using a DBMS-specific field name, such as F_NAME, query keys allow OracleAS TopLink expressions to refer to the field using Java attribute names, such as firstName.

For more information about Query Keys, see "Query Keys".

You can implement query keys either with OracleAS TopLink Mapping Workbench or in Java.

For more information about implementing query keys with OracleAS TopLink Mapping Workbench, see "Working with Query Keys" in the Oracle Application Server TopLink Mapping Workbench User's Guide.

Implementing Query Keys in Java

To add and register query keys with a descriptor, implement the following methods:

addQueryKey(): Method of the Descriptor class for regular query keys
addDirectQueryKey(): Method for one-to-one query keys that specifies the name of the query key and the name of the table field
addAbstractQueryKey(): Method for abstract query keys

Example 6-69 Implementing a One-to-One Query Key

// Add a query key for the foreign key field using the direct method
descriptor.addDirectQueryKey("managerId", "MANAGER_ID");

// The same query key can also be added through the add method
DirectQueryKey directQueryKey = new DirectQueryKey();
directQueryKey.setName("managerId");
directQueryKey.setFieldName("MANAGER_ID");
descriptor.addQueryKey(directQueryKey);

/* Add a one-to-one query key for the large project that the employee is a leader of (this assumes only one project) */
OneToOneQueryKey projectQueryKey = new OneToOneQueryKey();
projectQueryKey.setName("managedLargeProject");
projectQueryKey.setReferenceClass(LargeProject.class);
ExpressionBuilder builder = new ExpressionBuilder();
projectQueryKey.setJoinCriteria(builder.getField("PROJECT.LEADER_ID").equal(builder.getParameter("EMPLOYEE.EMP_ID")));
descriptor.addQueryKey(projectQueryKey);

Example 6-70 Implementing a One-to-Many Query Key

/* Implements keys for the projects where the employee manages multiple projects */
OneToManyQueryKey projectsQueryKey = new OneToManyQueryKey();
projectsQueryKey.setName("managedProjects");
projectsQueryKey.setReferenceClass(Project.class);
ExpressionBuilder builder = new ExpressionBuilder();
projectsQueryKey.setJoinCriteria(builder.getField("PROJECT.LEADER_ID").equal(builder.getParameter("EMPLOYEE.EMP_ID")));
descriptor.addQueryKey(projectsQueryKey);
// Next define the mappings. 
...

Example 6-71 Implementing a Many-to-Many Query Key

ManyToManyQueryKey key = new ManyToManyQueryKey();
key.setName("myAs");
key.setReferenceClass(A.class);
ExpressionBuilder builder = new ExpressionBuilder();
Expression exp = builder.getField("AB_JOIN.B_ID").equal(builder.getParameter("B.ID" ));
Expression exp1 = builder.getField("AB_JOIN.A_ID").equal(builder.getField("A.ID") );
key.setJoinCriteria(exp.and(exp1));
descriptor.addQueryKey(key);

Querying on Interfaces

When you define descriptors for an interface to enable querying, OracleAS TopLink supports querying on an interface, as follows:

If there is only a single implementor of the interface, then the query returns an instance of the concrete class.
If there are multiple implementors of the interfaces, then the query returns instances of all implementing classes.

Querying on an Inheritance Hierarchy

When you query on a class that is part of an inheritance hierarchy, the session checks the descriptor to determine the type of the class:

If you configure the descriptor to read subclasses (the default configuration), then the query returns instances of the class and its subclasses.
If you configure the descriptor not to read subclasses, then the query returns only instances of the queried class, but no instances of the subclasses.
If neither of these conditions apply, then the class is a leaf class and does not have any subclasses. The query returns instances of the queried class.

Cursors and Streams

Cursors and streams are related mechanisms that enable you to work efficiently with large result sets.

Cursors and Java Iterators

The OracleAS TopLink scrollable cursor enables you to scroll through a result set from the database without reading the whole result set in a single database read. The ScrollableCursor class implements the Java ListIterator interface to allow for direct and relative access within the stream. Scrollable cursors also enable you to scroll forward and backward through the stream.

Traversing Data with Scrollable Cursors

Several methods enable you to navigate data with a scrollable cursor:

relative(int i): Advances the row number in relation to the current row by one row
absolute(int i): Places the cursor at an absolute row position, 1 being the first row

Several strategies are available for traversing data with cursors. For example, to start at the end of the data set and work toward the first record:

Call the afterLast() method to place the cursor after the last row in the result set.
Use the hasPrevious() method to determine whether there is a record above the current record. This method returns FALSE when you reach the final record in the data set.
If the hasPrevious() method returns TRUE, then call the previous() method to move the cursor to the row above the current row and read that object.

These are common methods for data traversal, but they are not the only available methods. For more information about the available methods, see the Oracle Application Server TopLink API Reference.

To use the ScrollableCursor object, the JDBC driver must be compatible with JDBC 2.0 specifications.

Example 6-72 Traversing with a Scrollable Cursor

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.useScrollableCursor();
ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);

while (cursor.hasNext()) {
    System.out.println(cursor.next().toString());
}
cursor.close();

Java Streams

Java streams enable you to retrieve query results as individual records or groups of records, which can result in a performance increase. You can use streams to build efficient OracleAS TopLink queries, especially when the queries are likely to generate large result sets.

Cursored Stream Support

Cursored streams combine the iterative ability of the ScrollableCursor interface with OracleAS TopLink support for streams. The result is the ability to read back a query result set from the database in manageable subsets, and to scroll through the result set stream.

The useCursoredStream() method of the ReadAllQuery class provides cursored stream support.

Example 6-73 Cursored Streams

CursoredStream stream;
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.useCursoredStream();
stream = (CursoredStream) session.executeQuery(query);

The query returns an instance of CursoredStream rather than a Vector, which can be a more efficient approach. For example, note the following two code examples. Example 6-74 returns a Vector that contains all employee objects. If ACME has 10,000 employees, then the Vector contains references to 10,000 Employee objects.

Example 6-74 Using a Vector

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
Enumeration employeeEnumeration;

Vector employees = (Vector) session.executeQuery(query);
employeeEnumeration = employee.elements();

while (employeeEnumeration.hasMoreElements())
{
Employee employee = (Employee) employeeEnumeration.nextElement();
employee.doSomeWork();
}

Example 6-74 returns a CursoredStream instance rather than a Vector. The CursoredStream collection appears to contain all 10,000 objects, but initially contains a reference only to the first 10 Employee objects. It retrieves the remaining objects in the collection as they are needed. In many cases, the application never needs to read all the objects.

The following approach results in a significant performance increase:

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.useCursoredStream();

CursoredStream stream = (CursoredStream) session.executeQuery(query);
while (! stream.atEnd()) 
{
    Employee employee = (Employee) stream.read();
    employee.doSomeWork();
    stream.releasePrevious();
}
stream.close();

Note:

The releasePrevious() message is optional. This method releases any previously read objects and frees system memory. Even though released objects are removed from the cursored stream storage, they remain in the identity map.

Optimizing Streams

To optimize CursoredStream performance, provide a threshold and page size to the useCursoredStream(Threshold, PageSize) method, as follows:

The threshold specifies the number of objects to read into the stream initially. The default threshold is 10.
The page size specifies the number of objects to read into the stream after the initial group of objects. This occurs after the threshold number of objects is read. Although larger page sizes result in faster overall performance, they introduce delays into the application when OracleAS TopLink loads each page. The default page size is 5.

When you execute a batch-type operation, use the dontMaintainCache() option with a cursored stream. A batch operation performs simple operations on large numbers of objects and then discards the objects. Cursored streams create the required objects only as needed, and the dontMaintainCache() option ensures that these transient objects are not cached.

Querying Across Variable One-to-One Mappings

OracleAS TopLink does not provide a method to directly query against variable one-to-one mappings. To query against this type of mapping, combine OracleAS TopLink DirectQueryKeys and OracleAS TopLink ReportQueries to create query selection criteria for classes that implement the interface, as follows:

Create two DirectQueryKeys to query for the possible implementors of the interface.
- The first DirectQueryKey is for the class indicator field for the variable one-to-one mapping.
- The second DirectQueryKey is for the foreign key to the class or table that implements the interface.
Create a subSelect statement for each concrete class that implements the interface included in the query selection criteria.
Implement a ReportQuery.

Example 6-75 Creating DirectQueryKeys

/*The DirectQueryKeys as generated in the OracleAS TopLink project java source code from OracleAS TopLink Mapping Workbench */
…
descriptor.addDirectQueryKey("locationTypeCode","DEALLOCATION.DEALLOCATIONOBJECTTYPE");
descriptor.addDirectQueryKey("locationTypeId","DEALLOCATION.DEALLOCATIONOBJECTID");