Advanced Querying

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.

// 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);

/* 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. 
...

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.

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();

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

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();
}

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();

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:

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

2. Create a subSelect statement for each concrete class that implements the interface included in the query selection criteria.

3. Implement a ReportQuery.

/*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");