|
Oracle TopLink Developer's Guide
10g Release 3 (10.1.3) B13593-01 |
|
 Previous |
 Next |
|
Oracle TopLink Developer's Guide
10g Release 3 (10.1.3) B13593-01 |
|
|
Previous |
Next |
A simple expression usually consists of three parts:
The attribute, which represents a mapped attribute or query key of the persistent class
The operator, which is an expression method that implements boolean logic, such as GreaterThan, Equal, or Like
The constant or comparison, which refers to the value used to select the object
In the following code fragment:
expressionBuilder.get("lastName").equal("Smith");
The attribute is lastName.
The operator is equal.
The constant is the string "Smith".
The expressionBuilder substitutes for the object or objects to be read from the database. In this example, expressionBuilder represents employees.
You can use the following components when constructing an Expression:
Expressions use standard boolean operators, such as AND, OR, and NOT, and you can combine multiple expressions to form more complex expressions. Example 97-3 illustrates a code fragment that queries for projects managed by a selected person, and that have a budget greater than or equal to 1,000,000.
Example 97-3 Using Boolean Logic in an Expression
ExpressionBuilder project = new ExpressionBuilder();
Expression hasRightLeader, bigBudget, complex;
Employee selectedEmp = someWindow.getSelectedEmployee();
hasRightLeader = project.get("teamLeader").equal(selectedEmp);
bigBudget = project.get("budget").greaterThanEqual(1000000);
complex = hasRightLeader.and(bigBudget);
Vector projects = session.readAllObjects(Project.class, complex);
TopLink expressions support the following database functions and operators:
like
notLike
toUpperCase
toLowerCase
toDate
rightPad
Database functions lets you define more flexible queries. Example 97-4 illustrates a code fragment that matches several last names, including "SMART", "Smith", and "Smothers":
Example 97-4 Using a Database Function Supported by the Expression API
emp.get("lastName").toUpperCase().like("SM%")
You access most functions using Expression methods such as toUpperCase.
Mathematical functions are available through the ExpressionMath class. Mathematical function support in expressions is similar to the support provided by the Java class java.lang.Math. Example 97-5 illustrates using the abs and subtract methods.
You can use the following operators when constructing queries against data mapped to an Oracle Database XMLType column:
extract: Takes an XPath string and returns an XMLType which corresponds to the part of the original document that matches the XPath.
extractValue: Takes an Xpath string and returns either a numerical or string value based on the contents of the node pointed to by the XPath.
existsNode: Takes an Xpath expression and returns the number of nodes that match the Xpath.
getStringVal: Gets the string representation of an XMLType object.
getNumberVal: Gets the numerical representation of an XMLType object.
Example 97-6 illustrates how to use the extract operator in a query:
You can use the Expression method getFunction to access database functions that TopLink does not support directly. Example 97-7 illustrates how to access a database function named VacationCredit from within an expression, even though there is no support for such a function in the Expression API.
Example 97-7 Using a Database Function Not Supported by the Expression API
emp.get("lastName").getFunction("VacationCredit").greaterThan(42)
This expression produces the following SQL:
SELECT . . . WHERE VacationCredit(EMP.LASTNAME) > 42
The Expression API includes additional forms of the getFunction method that allow you to specify arguments. For more information, see Oracle TopLink API Reference.
You can also access a custom function that you create. For more information on creating a custom function in TopLink, see "Creating and Using a User-Defined Function".
Expressions can include an attribute that has a one-to-one relationship with another persistent class. A one-to-one relationship translates naturally into a SQL join that returns a single row.
Example 97-8 illustrates a code fragment that accesses fields from an employee's address.
Example 97-8 Using an Expression with a One-to-One Relationship
emp.get("address").get("country").like("S%")
Example 97-8 corresponds to joining the EMPLOYEE table to the ADDRESS table, based on the address foreign key, and checking for the country name.
You can nest these relationships infinitely, so it is possible to ask for complex information as follows:
project.get("teamLeader").get("manager").get("manager").get("address").get("street")
You can query against complex relationships, such as one-to-many, many-to-many, direct collection, and aggregate collection relationships. Expressions for these types of relationships are more complex to build, because the relationships do not map directly to joins that yield a single row per object.
This section describes the following:
A join is a relational database query that combines rows from two or more tables. Relational databases perform a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables.
An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.
An outer join extends the result of an inner join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. Outer joins can be categorized as left or right:
A query that performs a left outer join of tables A and B returns all rows from A. For all rows in A that have no matching rows in B, the database returns null for any select list expressions containing columns of B.
A query that performs a right outer join of tables A and B returns all rows from B. For all rows in B that have no matching rows in A, the database returns null for any select list expressions containing columns of A.
When you query with a join expression, TopLink can use joins to check values from other objects or other tables that represent parts of the same object. Although this works well under most circumstances, it can cause problems when you query against a one-to-one relationship, in which one side of the relationship is not present.
For example, Employee objects may have an Address object, but if the Address is unknown, it is null at the object level and has a null foreign key at the database level. When you attempt a read that traverses the relationship, missing objects cause the query to return unexpected results. Consider the following expression:
(emp.get("firstName").equal("Steve")).or(emp.get("address"). get("city").equal("Ottawa"))
In this case, employees with no address do not appear in the result set, regardless of their first name. Although not obvious at the object level, this behavior is fundamental to the nature of relational databases.
Outer joins rectify this problem in the databases that support them. In this example, the use of an outer join provides the expected result: all employees named Steve appear in the result set, even if their address is unknown.
To implement an outer join, use Expression method getAllowingNull, rather than get, and Expression method anyOfAllowingNone, rather than anyOf.
For example:
(emp.get("firstName").equal("Steve")).or(emp.getAllowingNull ("address").get("city").equal("Ottawa"))
Support and syntax for outer joins vary widely between databases and database drivers. TopLink supports outer joins for Oracle databases, IBM DB2, SQL Anywhere, Microsoft Access, Microsoft SQL Server, Sybase SQL Server, and the JDBC outer join syntax.
You can use joins anywhere expressions are used, including: selection-criteria, ordering (see "Specifying Read Ordering"), report queries (see "Report Query"), partial objects (see "Partial Object Queries"), one-to-one relational mappings (see "Configuring Joining at the Mapping Level"), and join reading (see "Join Reading and Object-Level Read Queries").
Use the expression API shown in Table 97-1 to configure inner and outer join expressions.
Table 97-1 Expression API for Joins
| Expression API | Type of Join | Type of Mapping |
|---|---|---|
|
|
inner |
one-to-one |
|
|
outer |
one-to-one |
|
|
inner |
|
|
|
outer |
Footnote 1 Use one-to-many joining with caution. In many cases, it is less efficient than batch reading. For more information, see "Avoiding Join-Reading Duplicate Data".
Footnote 2 You cannot use the ObjectLevelReadQuery method addJoinedAttribute with a join expression on a many-to-many mapped attribute (see "Using Join Reading").
To query across a one-to-many or many-to-many relationship, use the anyOf operation. As its name suggests, this operation supports queries that return all items on the "many" side of the relationship that satisfy the query criteria.
Example 97-9 illustrates an expression that returns employees who manage at least one employee (through a one-to-many relationship) with a salary less than $10,000.
Example 97-9 Using an Expression with a One-to-Many Relationship
emp.anyOf("managedEmployees").get("salary").lessThan(10000);
Example 97-10 illustrates how to query across a many-to-many relationship using a similar strategy:
Example 97-10 Using an Expression with a Many-to-Many Relationship
emp.anyOf("projects").equal(someProject)
TopLink translates these queries into SQL that joins the relevant tables using a DISTINCT clause to remove duplicates. TopLink translates Example 97-9 into the following SQL:
SELECT DISTINCT . . . FROM EMP t1, EMP t2 WHERE t2.MANAGER_ID = t1.EMP_ID AND t2.SALARY < 10000
You can use one-to-one and one-to-many join expressions in an ObjectLevelReadyQuery to configure joins on a per-query basis (see "Join Reading and Object-Level Read Queries").
You can also configure joins at the mapping level (see "Configuring Joining at the Mapping Level").
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:
