Oracle Extension Support

OracleAS TopLink supports the following Oracle enterprise enhancements for Oracle databases:

Oracle Hints and the OracleAS TopLink Query Framework
Hierarchical Queries

Oracle Hints and the OracleAS TopLink Query Framework

Oracle Hints is an Oracle database feature through which a developer makes decisions usually reserved for the optimizer. You use hints to specify things such as join order for a join statement, or the optimization approach of a SQL call.

The OracleAS TopLink query framework supports Oracle Hints with the following API:

setHintString("/*[hints or comments]*/");

OracleAS TopLink adds the hint to the SQL string as a comment immediately following a SELECT, UPDATE, INSERT, or DELETE statement.

To add hints to a read query:

Create a ReadObjectQuery or a ReadAllQuery
Set the selection criteria.
Add hints as needed.

For example, the following code uses the FULL hint (which explicitly chooses a full table scan for the specified table):

// This line sets up the query
ReadObjectQuery query = new ReadObjectQuery(Employee.class);
query.setSelectionCritera(new ExpressionBuilder().get("id").equal(new Integer(1));
// This line adds the hint
query.addHintString("/*+ FULL */" );

This code generates the following SQL:

SELECT /*+ FULL */ FROM EMPLOYEE WHERE ID=1

To add hints to WRITE, INSERT, UPDATE, and DELETE, create custom queries for these operations in the OracleAS TopLink query framework, then specify hints as required.

For more information about the available hints, see the Oracle database documentation.

Hierarchical Queries

Hierarchical queries is an Oracle database mechanism that enables you to 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.

To create a hierarchical query, use the setHierarchicalQueryClause() method. This method takes three parameters, as follows:

setHierarchicalQueryClause(StartWith, ConnectBy, OrderSibling)

This expression requires all three parameters, as follows:

StartWith Parameter

The StartWith parameter in the expression specifies the first object in the hierarchy. This parameter mirrors the Oracle database START WITH clause.

To include a StartWith parameter, build an expression to specify the appropriate object, and pass it as a parameter in the setHierarchicalQueryClause() method. If you do not specify the root object for the hierarchy, then set this value to NULL.

ConnectBy Parameter

The ConnectBy parameter specifies the relationship that creates the hierarchy. This parameter mirrors the Oracle database CONNECT BY clause.

Build an expression to specify the ConnectBy parameter, and pass it as a parameter in the setHierarchicalQueryClause() method. Because this parameter defines the nature of the hierarchy, it is required for the setHierarchicalQueryClause() implementation.

OrderSibling Parameter

The OrderSibling parameter in the expression specifies the order in which the query returns sibling objects in the hierarchy. This parameter mirrors the Oracle database ORDER SIBLINGS clause.

To include an OrderSibling parameter, define a vector, and to include the order criteria, use the addElement() call. Pass the vector as the third parameter in the setHierarchicalQueryClause() method. If you do not specify an order, then set this value to NULL.

Example 6-68 Hierarchical Query

ReadAllQuery raq = new ReadAllQuery(Employee.class);
// Specify a START WITH expression
Expression startExpr = expressionBuilder.get("id").equal(new Integer(1));
// Specifies a CONNECT BY expression
Expression connectBy = expressionBuilder.get("managedEmployees");
//Specifies an ORDER SIBLINGS BY vector
Vector order = new Vector();
order.addElement(expressionBuilder.get("lastName"));
order.addElement(expressionBuilder.get("firstName"));
raq.setHierarchicalQueryClause(startExpr, connectBy, order);
Vector employees = uow.executeQuery(raq);

The preceding code generates the following SQL:
SELECT * FROM EMPLOYEE START WITH ID=1 CONNECT BY PRIOR ID=MANAGER_ID ORDER SIBLINGS BY LAST_NAME, FIRST_NAME