10g (9.0.4)
Part Number B10313-01
 Home |
 Solution Area |
 Contents |
 Index |
| Oracle Application Server TopLink Application Developer's Guide 10g (9.0.4) Part Number B10313-01 |
|
Queries are a key element to any Oracle Application Server TopLink application, because they enable OracleAS TopLink to manage persistent data on the database. The query framework that OracleAS TopLink provides gives you the flexibility you need to manage the complex persistence requirements of enterprise applications.
The OracleAS TopLink query framework offers the following key features:
To define OracleAS TopLink queries, use the OracleAS TopLink Mapping Workbench, the OracleAS TopLink API, or in the case of entity beans, EJB Finders.
This chapter introduces OracleAS TopLink queries, and includes discussions on:
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, 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 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".
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 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 directly from the database tables rather than objects. 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:
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 the 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 enables you to request information from a database. The use of a native query language such as SQL is complex, but it offers advantages 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 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 Collection or Map interface (for example: HashMap).
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 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 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 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 generally see performance gains from in-memory querying; queries based on non-primary 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.
In that it does not know how many objects it is looking. 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:
For more information about binding and parameterized SQL, see "Binding and Parameterized SQL".
For more information about batch and join reading, see "Query Object Performance Options".
For more information about partial object reading, see "Partial Object Reading".
For more information about Java streams, see "Java Streams".
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. There are two key configuration options available when you query within the Unit of Work:
For more information about read queries within the Unit of Work, see "Reading and Querying Objects with the Unit of Work".
For more information, see "Conforming Results (UnitOfWork)".
There are two ways to build OracleAS TopLink queries: you can use the OracleAS TopLink Mapping Workbench, or you can build them in code using the OracleAS TopLink API.
The 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 the 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.
OracleAS TopLink supports the following predefined queries:
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 the OracleAS TopLink Mapping Workbench using the 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 the OracleAS TopLink Mapping Workbench. However, unlike queries built in the 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 might 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, but 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:
Home interface. The CMP provider generates the actual code mechanisms for the finder from the API definition.
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 an object's first name. 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 the OracleAS TopLink Mapping Workbench.
For more information, see "Working with Query Keys" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
OracleAS TopLink supports several options for creating queries, including:
OracleAS TopLink expressions enable you to specify query search criteria based on the object model. OracleAS TopLink translates the resulting query into SQL and converts the results of the query into objects. OracleAS TopLink provides two public classes to support expression:
The OracleAS TopLink expression framework provides methods through the following classes:
Expression class provides most general functions, such as toUpperCase.
ExpressionMath class supplies mathematical methods.
The following code examples illustrate the two classes. Example 6-1 uses the Expression class, while Example 6-2 uses the ExpressionMath class.
expressionBuilder.get("lastName").equal("Smith");
ExpressionMath.abs(ExpressionMath.subtract(emp.get("salary"), emp.get("spouse").get("salary")).greaterThan(10000)
This division of functionality enables OracleAS TopLink expressions to provide similar mathematical functionality to the Java class, java.lang.Math, but keeps both the Expression and ExpressionMath classes from becoming unnecessarily complex.
A simple expression normally consists of three parts:
GreaterThan, Equal, or Like
In the following code fragment:
expressionBuilder.get("lastName").equal("Smith");
The expressionBuilder substitutes for the object or objects to be read from the database. In this example, expressionBuilder represents employees.
Expressions offer the following advantages over SQL when you access a database:
Query interface so that it looks similar to traditional Java calling conventions. For example, the Java code required to get the street name from the Address object of the Employee class looks like this:
emp.getAddress().getStreet().equals("Meadowlands");
The expression to get the same information is similar:
emp.get("address").get("street").equal("Meadowlands");
ExpressionBuilder emp = new ExpressionBuilder(); Expression exp = emp.get("address").get("street").equal("Meadowlands"); Vector employees = session.readAllObjects(Employee.class, exp.and(emp.get("salary").greaterThan(10000)));
OracleAS TopLink automatically generates the appropriate SQL from that code:
SELECT t0.VERSION, t0.ADDR_ID, t0.F_NAME, t0.EMP_ID, t0.L_NAME, t0.MANAGER_ ID, t0.END_DATE, t0.START_DATE, t0.GENDER, t0.START_TIME, t0.END_ TIME,t0.SALARY FROM EMPLOYEE t0, ADDRESS t1 WHERE (((t1.STREET = 'Meadowlands')AND (t0.SALARY > 10000)) AND (t1.ADDRESS_ID = t0.ADDR_ID))
Expressions use standard boolean operators, such as AND, OR, and NOT and you can combine multiple expressions to form more complex expressions. For example, the following code fragment queries for projects managed by a selected person, with a budget greater than or equal to 1,000,000.
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);
OracleAS TopLink supports the following database functions and operators:
Database functions allow you to define more flexible queries. For example, the following code fragment matches several last names, including "SMART", "Smith", and "Smothers":
emp.get("lastName").toUpperCase().like("SM%")
You access most functions through methods such as toUpperCase on the Expression class.
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.
For example:
ExpressionMath.abs(ExpressionMath.subtract(emp.get("salary"),emp.get("spouse") .get("salary")).greaterThan(10000)
You can use expressions to implement database functions that OracleAS TopLink does not support directly. For simple functions, use the getFunction() operation, which the argument is the name of a function. For example, consider the following expression, which calls a function called VacationCredit on the database:
emp.get("lastName").getFunction("VacationCredit").greaterThan(42)
This expression produces the following SQL:
SELECT . . . WHERE VacationCredit(EMP.LASTNAME) > 42
You can also create more complex functions and add them to OracleAS TopLink. See "Platform and User-Defined Functions".
Expressions can include an attribute that has a one-to-one relationship with another persistent class. A one-to-one relation translates naturally into a SQL join that returns a single row.
For example, the following code fragment accesses fields from an employee's address:
emp.get("address").get("country").like("S%")
This example 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.
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. For example, consider the following code fragment:
emp.anyOf("managedEmployees").get("salary").lessThan(10000);
This code returns employees who manage at least one employee (through a one-to-many relationship) with a salary below $10,000. You can query across a many-to-many relationship using a similar strategy:
emp.anyOf("projects").equal(someProject)
OracleAS TopLink translates these queries to SQL, and SQL joins the relevant tables using a DISTINCT clause to remove duplicates.
For example:
SELECT DISTINCT . . . FROM EMP t1, EMP t2 WHERE t2.MANAGER_ID = t1.EMP_ID AND t2.SALARY < 10000
To create Expression objects, use the get() method or its related methods on an Expression or ExpressionBuilder. The ExpressionBuilder acts as a stand-in for the objects you query. To construct a query, send messages to the ExpressionBuilder that correspond to the attributes of the objects. We recommend that you name ExpressionBuilder objects according to the type of objects against which you do a query.
This example uses the query key lastName to reference the field name L_NAME.
Expression expression = new ExpressionBuilder().get("lastName").equal("Young");
ExpressionBuilder emp = new ExpressionBuilder(); Expression exp1, exp2; exp1 = emp.get("firstName").equal("Ken"); exp2 = emp.get("lastName").equal("Young"); return exp1.and(exp2);
Expression expression = new ExpressionBuilder().get("lastName").notLike("%ung");
Expressions support subqueries (SQL subselects) and parallel selects. To create a subquery, use a single expression builder. With parallel selects, use multiple expression builders when you define a single query. This enables you to specify joins for unrelated objects at the object level.
Some queries compare the results of other, contained queries (or subqueries). SQL supports this comparison through subselects. OracleAS TopLink expressions provide subqueries to support subselects.
Subqueries enable you to define sophisticated expressions that query on aggregated values (counts, min, max) and unrelated objects (exists, in, comparisons). To obtain a subquery, pass an instance of a report query to any expression comparison operation, or use the subQuery operation on expression builder. The subquery is not required to have the same reference class as the parent query, and it must use its own expression builder.
You can nest subqueries, or use them in parallel. Subqueries can also make use of custom SQL.
For expression comparison operations that accept a single value (equal, greaterThan, lessThan), the subquery result must return a single value. For expression comparison operations that accept a set of values (in, exists), the subquery result must return a set of values.
This example searches for all employees with more than 5 managed employees.
ExpressionBuilder emp = new ExpressionBuilder(); ExpressionBuilder managedEmp = new ExpressionBuilder(); ReportQuery subQuery =new ReportQuery(Employee.class, managedEmp); subQuery.addCount(); subQuery.setSelectionCriteria(managedEmp.get("manager") .equal(emp)); Expression exp = emp.subQuery(subQuery).greaterThan(5);
This example searches for the employee with the highest salary in the city of Ottawa.
ExpressionBuilder emp = new ExpressionBuilder(); ExpressionBuilder ottawaEmp = new ExpressionBuilder(); ReportQuery subQuery = new ReportQuery(Employee.class, ottawaEmp); subQuery.addMax("salary"); subQuery.setSelectionCriteria(ottawaEmp.get("address").get("city").equal("Ottawa")); Expression exp = emp.get("salary").equal(subQuery).and(emp.get("address").get("city").equal("Ottawa"));
This example searches for all employees that have no projects.
ExpressionBuilder emp = new ExpressionBuilder(); ExpressionBuilder proj = new ExpressionBuilder(); ReportQuery subQuery = new ReportQuery(Project.class, proj); subQuery.addAttribute("id"); subQuery.setSelectionCriteria(proj.equal(emp.anyOf("projects")); Expression exp = emp.notExists(subQuery);
Parallel expressions enable you to compare unrelated objects. Parallel expressions require multiple expression builders, but do not require the use of report queries. Each expression must have its own expression builder, and you must use the constructor for expression builder that takes a class as an argument. The class does not have to be the same for the parallel expressions, and you can create multiple parallel expressions in a single query.
Only one of the expression builders is considered the primary expression builder for the query. This primary builder makes use of the zero argument expression constructor, and OracleAS TopLink obtains its class from the query.
This example queries all employees with the same last name as another employee of different gender, and accounts for the possibility that returned results can be a spouse.
ExpressionBuilder emp = new ExpressionBuilder(); ExpressionBuilder spouse = new ExpressionBuilder(Employee.class); Expression exp = emp.get("lastName").equal(spouse.get("lastName")) .and(emp.get("gender").notEqual(spouse.get("gender"));
A relationship mapping differs from a regular query because it retrieves data for many different objects. To enable you to specify these queries, supply arguments when you execute the query. Use the getParameter() and getField() methods to acquire values for the arguments.
A parameterized expression executes searches and comparisons based on variables instead of constants. This approach enables you to build expressions that retrieve context-sensitive information. This technique is useful when you:
Parameterized expressions require that the relationship mapping know how to retrieve an object or collection of objects based on its current context. For example, a one-to-one mapping from Employee to Address must query the database for an address based on foreign key information from the Employee table. Each mapping contains a query that OracleAS TopLink constructs automatically based on the information provided in the mapping. To specify expressions yourself, use the mapping customization mechanisms. For more information about the mapping customization mechanisms, see the Oracle Application Server TopLink Mapping Workbench User's Guide.
The getParameter() method returns an expression that becomes a parameter in the query. This method enables you to create a query that employs user input as the search criteria. The parameter must be either the fully qualified name of the field from a descriptor's row, or a generic name for the argument.
Parameters you construct this way are global to the current query, so you can send this message to any expression object.
ExpressionBuilder address = new ExpressionBuilder(); Expression exp = address.getField ("ADDRESS.EMP_ID").equal(address.getParameter("EMPLOYEE.EMP_ID")); exp = exp.and(address.getField("ADDRESS.TYPE").equal(null));
The getField() method returns an expression that represents a database field with the given name. Use the Expression getField() method to construct the selection criteria for a mapping. The argument is the fully qualified name of the required field. Because fields are not global to the current query, you must send this method to an expression that represents the table from which this field is derived. See also "Data Queries" .
This example obtains a simple one-to-many mapping from class PolicyHolder to Policy using a nondefault selection criteria. The SSN field of the POLICY table is a foreign key to the SSN field of the HOLDER table.
OneToManyMapping mapping = new OneToManyMapping();
mapping.setAttributeName("policies");
mapping.setGetMethodName("getPolicies");
mapping.setSetMethodName("setPolicies");
mapping.setReferenceClass(Policy.class);
// Build a custom expression here rather than using the defaults
ExpressionBuilder policy = new ExpressionBuilder();
mapping.setSelectionCriteria(policy.getField("POLICY.SSN")).equal(policy.
getParameter("HOLDER.SSN")));
This example uses an employee's first name to demonstrate how to use a custom query to find the employee.
ExpressionBuilder emp = new ExpressionBuilder(); Expression firstNameExpression; firstNameExpression = emp.get("firstName").equal(emp.getParameter("firstName")); ReadObjectQuery query = new ReadObjectQuery(); query.setReferenceClass(Employee.class); query.setSelectionCriteria(firstNameExpression); query.addArgument("firstName"); Vector v = new Vector(); v.addElement("Sarah"); Employee e = (Employee) session.executeQuery(query, v);
This example demonstrates how to use a custom query to find all employees that live in the same city as a given employee.
ExpressionBuilder emp = new ExpressionBuilder(); Expression addressExpression; addressExpression = emp.get("address").get("city").equal(emp.getParameter("employee").get("address") .get("city")); ReadObjectQuery query = new ReadObjectQuery(Employee.class); query.setName("findByCity"); query.setReferenceClass(Employee.class); query.setSelectionCriteria(addressExpression); query.addArgument("employee"); Vector v = new Vector(); v.addElement(employee); Employee e = (Employee) session.executeQuery(query, v);
Different databases sometimes implement the same functions in different ways. For example, an argument that specifies that data returns in ascending order might be ASC or ASCENDING. To manage differences, OracleAS TopLink recognizes functions and other operators that vary according to the relational database.
Although most platform-specific operators exist in OracleAS TopLink, use the ExpressionOperator class to add your own.
An ExpressionOperator has a selector and a vector of strings:
You can also specify whether the operator is prefix or postfix. In a prefix operator, the first constant string prints before the first argument; in a postfix, it prints afterwards.
ExpressionOperator toUpper = new ExpressionOperator(); toUpper.setSelector(); Vector v = new Vector(); v.addElement("UPPER("); v.addElement(")"); toUpper.printAs(v); toUpper.bePrefix(); toUpper.setNodeClass(FunctionExpression.class);// To add this operator for all databaseExpressionOperator.addOperator(toUpper);// To add to a specific platformDatabasePlatform platform = session.getLogin().getPlatform(); platform.addOperator(toUpper);
This example illustrates the getFunction() method, called with a vector of arguments.
ReadObjectQuery query = new ReadObjectQuery(Employee.class); expression functionExpression = new ExpressionBuilder().get("firstName").getFunction(ExpressionOperator.toUpper). equal("BOB"); query.setSelectionCriteria(functionExpression); session.executeQuery(query);
You can use expressions to retrieve data rather than objects. This is a common approach when you work with unmapped information in the database, such as foreign keys and version fields.
Expressions that query for objects generally refer to object attributes, which may in turn refer to other objects. Data expressions refer to tables and their fields. You can combine data expressions and object expressions within a single query. OracleAS TopLink provides two main operators for expressions that query for data: getField(), and getTable().
The getField() operator enables you to retrieve data from either an unmapped table or an unmapped field from an object. In either case, the field must be part of a table represented by that object's class; otherwise, OracleAS TopLink raises an exception when you execute the query.
You can also use the getField() operator to retrieve the foreign key information for an object.
builder.getField("[FIELD_NAME]").greaterThan("[ARGUMENT]");
The getTable() operator returns an expression that represents an unmapped table in the database. This expression provides a context from which to retrieve an unmapped field when you use the getField() operator.
builder.getTable("[TABLE_NAME]").getField("[FIELD_NAME]").equal("[ARGUMENT]");
A common use for the getTable() and getField() operators is to retrieve information from a link table (or reference table) that supports a many-to-many relationship. Example 6-18 reads a many-to-many relationship that uses a link table and also checks an additional field in the link table. This code combines an object query with a data query, using the employee's manager as the basis for the data query. It also features parameterization for the project ID.
ExpressionBuilder emp = new ExpressionBuilder(); Expression manager = emp.get("manager"); Expression linkTable = manager.getTable("PROJ_EMP"); Expression empToLink = emp.getField("EMPLOYEE
.EMP_ID").equal(linkTable.getField("PROJ_EMP.EMP_ID"); Expression projToLink = linkTable.getField("PROJ_EMP .PROJ_ID").equal(emp.getParameter("PROJECT.PROJ_ID")); Expression extra = linkTable.getField("PROJ_EMP.TYPE").equal("W"); query.setSelectionCriteria((empToLink.and(projToLink)).and(extra));
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 class attribute names such as firstName. This offers the following advantages:
For more information about query keys with the OracleAS TopLink Mapping Workbench, see "Working with Query Keys," in the Oracle Application Server TopLink Mapping Workbench User's Guide.
OracleAS TopLink defines direct query keys for all direct mappings and has a special query key type for each mapping. You can use query keys to access fields that do not have direct mappings associated with them, such as the version field used for optimistic locking or the type field used for inheritance.
Vector employees = session.readAllObjects(Employee.class, new ExpressionBuilder().get("firstName").equal("Bob"));
OracleAS TopLink supports and defines query keys for relationship mappings. You can use query keys to join across a relationship. One-to-one query keys define a joining relationship. To access query keys for relationship mappings, use the get() method in expressions.
The following code example illustrates how to use a one-to-one query key within the OracleAS TopLink expression framework.
ExpressionBuilder employee = new ExpressionBuilder(); Vector employees = session.readAllObjects(Employee.class, employee.get("address").get("city").equal("Ottawa"));
To access one-to-many and many-to-many query keys that define a distinct join across a collection relationship, use the anyOf() method in expressions.
If no mapping exists for the relationship, you can also define relationship query keys manually. Relationship query keys are not supported directly by the OracleAS TopLink Mapping Workbench. To define a relationship query key, specify and write an amendment method, and use the addQueryKey() message to register the query keys.
The following code defines a one-to-one query key.
/* Static amendment method in Address class, addresses do not know their owners in the object-model, however you can still query on their owner if a user-defined query key is defined */ public static void addToDescriptor(Descriptor descriptor) { OneToOneQueryKey ownerQueryKey = new OneToOneQueryKey(); ownerQueryKey.setName("owner"); ownerQueryKey.setReferenceClass(Employee.class); ExpressionBuilder builder = new ExpressionBuilder(); ownerQueryKey.setJoinCriteria(builder.getField("EMPLOYEE.ADDRESS_ ID").equal(builder.getParameter("ADDRESS.ADDRESS_ID"))); descriptor.addQueryKey(ownerQueryKey); }
Table 6-1 and Table 6-2 summarize the most common public methods for ExpressionBuilder and Expression. For more information about the available methods for ExpressionBuilder and Expression, see the Oracle Application Server TopLink API Reference.
The expression framework enables you to define complex queries at the object level. If your application requires a more complex query, use SQL or stored procedure calls to create custom database operations.
For more information about stored procedure calls, see "Stored Procedure Calls".
You can provide a SQL string to any query instead of an expression, but the SQL string must return all data required to build an instance of the queried class. The SQL string can be a complex SQL query or a stored procedure call.
You can invoke SQL queries through the session read methods or through a read query instance.
Employee employee = (Employee) session.readObjectCall(Employee.class), new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44");
This example queries user and time information.
Vector rows = session.executeSelectingCall(new SQLCall("SELECT USER, SYSDATE FROM DUAL"));
OracleAS TopLink offers the following data-level queries to read or modify data (but not objects) in the database:
DataReadQuery: for reading rows of data
DirectReadQuery: for reading a single column of data
ValueReadQuery: for reading a single value of data
DataModifyQuery: for modifying data
This example uses SQL to read all employee IDs.
DirectReadQuery query = new DirectReadQuery(); query.setSQLString("SELECT EMP_ID FROM EMPLOYEE"); Vector ids = (Vector) session.executeQuery(query);
This example uses SQL to switch the database.
DataModifyQuery query = new DataModifyQuery(); query.setSQLString("USE SALESDATABASE"); session.executeQuery(query);
You can provide a StoredProcedureCall object to any query instead of an expression or SQL string, but the procedure must return all data required to build an instance of the class you query.
ReadAllQuery readAllQuery = new ReadAllQuery(); call = new StoredProcedureCall(); call.setProcedureName("Read_All_Employees"); call.useNamedCursorOutputAsResultSet("RESULT_CURSOR"); readAllQuery.setCall(call); Vector employees = (Vector) session.executeQuery(readAllQuery);
The StoredProcedureCall object allows you to use output parameters. Output parameters enable the stored procedure to return additional information. You can use output parameters to define a readObjectQuery if they return the fields required to build the object.
StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName("CHECK_VALID_POSTAL_CODE"); call.addNamedArgument("POSTAL_CODE"); call.addNamedOutputArgument("IS_VALID"); ValueReadQuery query = new ValueReadQuery(); query.setCall(call); query.addArgument("POSTAL_CODE"); Vector parameters = new Vector(); parameters.addElement("L5J1H5"); Number isValid = (Number) session.executeQuery(query,parameters);
Oracle databases use output parameters rather than result sets to return data from stored procedures. Cursored output parameters enable you to retrieve the result set in a cursored stream rather than as a single result set. When you use the Oracle JDBC drivers, configure a StoredProcedureCall object to pass a cursor to OracleAS TopLink as a normal result set.
StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName("READ_ALL_EMPLOYEES"); call.useNamedCursorOutputAsResultSet("RESULT_CURSOR"); ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.setCall(call); Vector employees = (Vector) Session.executequery(Query);
For more information about cursored streams, see "Java Streams".
OracleAS TopLink manages output parameter events for databases that support them. For example, if a stored procedure returns an error code that indicates that the application wants to check for an error condition, OracleAS TopLink raises the session event OutputParametersDetected to allow the application to process the output parameters.
StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName("READ_EMPLOYEE"); call.addNamedArgument("EMP_ID"); call.addNamedOutputArgument("ERROR_CODE"); ReadObjectQuery query = new ReadObjectQuery(); query.setCall(call); query.addArgument("EMP_ID"); ErrorCodeListener listener = new ErrorCodeListener(); session.getEventManager().addListener(listener); Vector args = new Vector(); args.addElement(new Integer(44)); Employee employee = (Employee) session.executeQuery(query, args);
Table 6-3 summarizes the most common public methods for the StoredProcedureCall. For more information about the available methods for the StoredProcedureCall, see the Oracle Application Server TopLink API Reference.
EJB QL is a query language that is similar to SQL, but differs because it presents queries from an object model perspective and includes path expressions that enable navigation over the relationships defined for entity beans and dependent objects. Although EJB QL is usually associated with Enterprise JavaBeans (EJBs), OracleAS TopLink enables you to use EJB QL with regular Java objects as well. In OracleAS TopLink, EJB QL enables users to declare queries, using the attributes of each abstract entity bean in the object model. This offers the following advantages:
SELECT to specify the query reference class (the class or entity bean you are querying against).
OracleAS TopLink support for EJB QL enables you to:
ReadQuery or the OracleAS TopLink session.
For more information about EJB QL queries with the OracleAS TopLink Mapping Workbench, see the Oracle Application Server TopLink Mapping Workbench User's Guide.
The basic API for a ReadAll query with EJB QL is as follows:
setEJBQLString("...")
Provide either a SELECT clause or a reference class, and execute the query normally.
ReadAllQuery theQuery = new ReadAllQuery(); theQuery.setReferenceClass(EmployeeBean.class); theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp"); ... Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);
This example defines the query similarly to Example 6-30, but creates, fills, and passes a vector of arguments to the executeQuery method.
// First define the query ReadAllQuery theQuery = new ReadAllQuery(); theQuery.setReferenceClass(EmployeeBean.class); theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = ?1"); ... // Next define the Arguments Vector theArguments = new Vector(); theArguments.add("Bob"); ... // Finally execute the query passing in the arguments Vector returnedObjects = (Vector)aSession.executeQuery(theQuery, theArguments);
You can execute EJB QL directly against the session. This returns a vector of the objects specified by the reference class. The basic API is:
aSession.readAllObjects(<ReferenceClass>, <EJBQLCall>) /* <ReferenceClass> is the return class type and <EJBQLCall> is the EJBQL string to be executed */ // Call ReadAllObjects on a session. Vector theObjects = (Vector)aSession.readAllObjects(EmployeeBean.class, new EJBQLCall( "SELECT OBJECT (emp) from EmployeeBean emp");
OracleAS TopLink supports all the EJB QL specification with the following exceptions:
Query by example enables you to specify queries when you provide sample instances of the persistent objects to be queried.
To define a query by example, provide a ReadObjectQuery or a ReadAllQuery with a sample persistent object instance and an optional query by example policy. The sample instance contains the data to query, and the query by example policy contains optional configuration settings, such as the operators to use and the attributes to consider or ignore.
Query by example enables you to query on any attribute that uses a direct mapping or a one-to-one relationship (including those with nesting). It does not support other relationship mapping types.
By default, OracleAS TopLink ignores attributes in the sample instance that contain zero (0), empty strings, and FALSE. To modify the list of values, see "Defining a Query by Example Policy". You can use any valid constructor to create a sample instance or example object. Set only the attributes on which you base the query; set all other attributes to null.
Query by example uses the AND operator to tie the attribute comparisons together.
This example queries the employee Bob Smith.
ReadObjectQuery query = new ReadObjectQuery(); Employee employee = new Employee(); employee.setFirstName("Bob"); employee.setLastName("Smith"); query.setExampleObject(employee); Employee result = (Employee) session.executeQuery(query);
This example queries across the employee's address.
ReadAllQuery query = new ReadAllQuery(); Employee employee = new Employee(); Address address = new Address(); address.setCity("Ottawa"); employee.setAddress(address); query.setExampleObject(employee); Vector results = (Vector) session.executeQuery(query);
OracleAS TopLink support for query by example includes a query by example policy. You can edit the policy to modify query by example default behavior. You can modify the policy to:
LIKE or other operations to compare attributes. By default, query by example allows only EQUALS.
FALSE.
isNull or notNull for attribute values.
To specify a query by example policy, include an instance of QueryByExamplePolicy with the query.
This example uses like for Strings and includes only objects whose salary is greater than zero.
ReadAllQuery query = new ReadAllQuery(); Employee employee = new Employee(); employee.setFirstName("B%"); employee.setLastName("S%"); employee.setSalary(0); query.setExampleObject(employee); /* Query by example policy section adds like and greaterThan */ QueryByExamplePolicy policy = new QueryByExamplePolicy(); policy.addSpecialOperation(String.class, "like"); policy.addSpecialOperation(Integer.class, "greaterThan"); policy.alwaysIncludeAttribute(Employee.class, "salary"); query.setQueryByExamplePolicy(policy); Vector results = (Vector) session.executeQuery(query);
This example uses key words for Strings and ignores -1.
ReadAllQuery query = new ReadAllQuery(); Employee employee = new Employee(); employee.setFirstName("bob joe fred"); employee.setLastName("smith mc mac"); employee.setSalary(-1); query.setExampleObject(employee); /* Query by example policy section */ QueryByExamplePolicy policy = new QueryByExamplePolicy(); policy.addSpecialOperation(String.class, "containsAnyKeyWords"); policy.excludeValue(-1); query.setQueryByExamplePolicy(policy); Vector results = (Vector) session.executeQuery(query);
To create more complex query by example queries, combine query by example with OracleAS TopLink expressions.
ReadAllQuery query = new ReadAllQuery();
Employee employee = new Employee();
employee.setFirstName("Bob");
employee.setLastName("Smith");
query.setExampleObject(employee);
/* This section specifies the expression */
ExpressionBuilder builder = new ExpressionBuilder();
query.setSelectionCriteria(builder.get("salary").between(100000,200000);
Vector results = (Vector) session.executeQuery(query);
Table 6-4 summarizes the most common public methods for QueryByExample. For more information about the available methods, see the Oracle Application Server TopLink API Reference.
OracleAS TopLink provides several options to execute queries, including:
The Session class and its subclasses (including DatabaseSession and UnitOfWork) provide methods to read, create, modify, and delete objects stored in a database. These methods, known as query methods, enable you to create queries against the object model. Session queries are easy to use and are flexible enough to perform most database operations.
The DatabaseSession class provides direct support to read and modify the database by offering read, write, insert, update, and delete operations.
The UnitOfWork class also provides methods to modify data. The Unit of Work is a safer approach to data modification than the DatabaseSession methods, because it isolates changes until they are complete. Whenever possible, use the Unit of Work to write or update rather than the write, insert, update, and delete methods available in the database session.
For more information, see "Unit of Work Basics".
The session provides the following methods to access the database:
readObject() method reads a single object from the database. Use this method with a primary key when looking for a specific object.
readAllObjects() method reads multiple objects from the database. Use this method to return a group of objects that match the selection criteria.
refreshObject() method refreshes objects in the cache with data from the database.
The readObject() method retrieves a single object from the database. The application must specify the class of object to read. If no object matches the criteria is found, null is returned.
For example, the basic read operation is:
session.readObject(MyDomainObject.class);
This example returns the first instance of MyDomainObject found in the table used for MyDomainObject. OracleAS TopLink provides the Expression class to specify querying parameters for a specific object.
When you search for a single, specific object using a primary key, the readObject() method is more efficient than the readAllObjects() method because readObject() can find an instance in the cache without accessing database. Because a readAllObjects() operation does not know how many objects match the criteria, it always searches the database to find matching objects, even if it finds matching objects in the cache.
import oracle.toplink.sessions.*; import oracle.toplink.expressions.*; /* Use an expression to read in the Employee whose last name is Smith. Create an expression using the Expression Builder and use it as the selection criterion of the search */ Employee employee = (Employee) session.readObject(Employee.class, new ExpressionBuilder().get("lastName").equal("Smith"));
The readAllObjects() method retrieves a Vector of objects from the database and does not order the returned objects. If the query does not find any matching objects, it returns an empty Vector.
Specify the class for the query. You can also include an expression to define more complex search criteria, as illustrated in Example 6-38.
// Returns a Vector of employees whose employee salary > 10000 Vector employees = session.readAllObjects(Employee.class,new ExpressionBuilder.get("salary").greaterThan(10000));
The refreshObject() method causes OracleAS TopLink to update the object in memory with data from the database. This operation refreshes any privately owned objects as well.
The Unit of Work provides the safest mechanism for writing objects in most OracleAS TopLink applications. However, when you can safely write directly to the database (for example: in a single-user or a two-tier application), session methods are the most efficient database writing tool. Database session provides the following methods to write to a database:
When you invoke the writeObject() method, the method performs a does-exist check to determine whether an object exists. If the object exists, writeObject() updates the object; if it does not exist, writeObject() inserts a new object.
The writeObject() method writes privately owned objects in the correct order to maintain referential integrity.
Call the writeObject() method when you cannot verify that an object exists on the database.
//Create an instance of employee and write it to the databaseEmployee susan = new Employee(); susan.setName("Susan"); ...//Initialize the susan object with all other instance variablessession.writeObject(susan);
You can call the writeAllObjects() method to write multiple objects to the database. The writeAllObjects() method performs the same does-exist check as the writeObject() method and then performs the appropriate insert or update operations.
// Read a Vector of all the current employees in the database. Vector employees = (Vector) session.readAllObjects(Employee.class); ...//Modify any employee data as necessary//Create a new employee and add it to the list of employeesEmployee susan = new Employee(); ...//Initialize the new instance of employeeemployees.add(susan);/* Write all employees to the database. The new instance of susan which is not currently in the database will be inserted. All the other employees which are currently stored in the database will be updated*/ session.writeAllObjects(employees);
The insertObject() method creates a new object on the database, but does not perform the does-exist check before it attempts the insert operation. The insertObject() method is more efficient than the writeObject() method if you are certain that the object does not yet exist on the database. If the object does exist, the database throws an exception when you execute the insertObject() call.
The updateObject() method updates existing objects in the database, but does not perform the does-exist check before it attempts the update operation. The updateObject() is more efficient than the writeObject()method if you are certain that the object does exist in the database. If the object does not exist, the database throws an exception when you execute the updateObject() call.
To delete an OracleAS TopLink object from the database, read the object from the database and then call the deleteObject() method. This method deletes both the specified object and any privately owned data.
Query objects are the standard devices OracleAS TopLink uses to interact with the database. They support database commands such as create, read, update, and delete, and accept search criteria specified in several ways, including OracleAS TopLink expressions.
OracleAS TopLink provides you with direct access to query objects, which support more complex queries than the session query API. You can build custom query objects to improve application performance or to support complex queries. Use the custom query object classes you create with the session or a descriptor's query manager to:
readObject() and writeObject().
The OracleAS TopLink Mapping Workbench provides graphical tools to create query objects. Although this section discusses query objects in the context of Java code, we recommend that you create query objects in the OracleAS TopLink Mapping Workbench.
OracleAS TopLink uses query objects to store information about a database query. A complete query object stores information about:
The following steps illustrate how to create a query object in Java code.
To execute a query, select one of the following query object classes:
ReadAllQuery: reads a collection of objects
ReadObjectQuery: reads a single object
ReportQuery: reads information about objects
DeleteObjectQuery: removes an object from the database
InsertObjectQuery: inserts new objects into the database
UpdateObjectQuery: updates existing objects
WriteObjectQuery: writes an object to the database, either with an insert (for new objects) or an update (for existing objects)
To execute SQL expressions, use the following query object classes:
ValueReadQuery: returns a single data value
DirectReadQuery: returns a collection of column values; can be used for direct collection queries
DataReadQuery: executes a SQL SELECT, returns a collection of database row (map) objects
DataModifyQuery: executes a non-selecting SQL string
The reference class specifies the class against which the query runs. Use the setReferenceClass() call to select a searchable class.
To specify how a query executes, call one of the following the methods:
setSelectionCriteria(): passes an expression to the query object
setSQLString(): passes a SQL string
setCall(): passes a database call
This setting is optional. If you do not specify read criteria, a ReadAllQuery returns every object of the reference class in the database, and a ReadObjectQuery returns the first object it encounters.
You can pass arguments to the query object by calling addArgument() in addition to the executeQuery() method. Arguments describe the objects for the query to return.
After initialization, use the addQuery() method to register the query object with the session. Name the query when you register it. The session then manages the query for you. This enables you to call the query by name.
Registering the query object with the session is optional. If you do not register the query object, specify the entire query every time you execute it, or manage it manually outside of the session.
To execute the query, use the executeQuery() call to call the object by name. As required, provide values for any defined arguments.
Although query objects support writing to a database, reading is their most common use. This section provides several examples that illustrate the use of query objects for reading the database.
Example 6-41 illustrates a simple read query. It uses an OracleAS TopLink expression, but does not use its own arguments for the query. Instead, it relies on the search parameters the expression provides. This example builds the expression within its code, but does not register the query with the session.
// This example returns a Vector of employees whose employee ID is > 100.
// Initialize the query object by specifying the query type
ReadAllQuery query = new ReadAllQuery();
//Set the reference class for the query.
query.setReferenceClass(Employee.class);
/* Configure the query execution. Because this example uses an expression, it
uses the setSelectionCriteria call */
query.setSelectionCriteria(new ExpressionBuilder.get("id").greaterThan(100));
// Execute the query
Vector employees = (Vector) session.executeQuery(query);
Example 6-42 illustrates a complex readObject query that uses all available configuration options.
// Define two expressions that map to the first and last name of the employee.ExpressionBuilder emp = new ExpressionBuilder(); Expression firstNameExpression = emp.get("firstName").equal(emp.getParameter("firstName")); Expression lastNameExpression = emp.get("lastName").equal(emp.getParameter("lastName")); //Initialize the query object by specifying the query type ReadObjectQuery query = new ReadObjectQuery(); //Set the reference class for the query. query.setReferenceClass(Employee.class); /* Configure the query execution. Because this example uses an expression, it uses the setSelectionCriteria call */ query.setSelectionCriteria(firstNameExpression.and(lastNameExpression)); //Specify the required arguments for the query. query.addArgument("firstName"); query.addArgument("lastName");// Add the query to the session.session.addQuery("getEmployeeWithName", query);/* Execute the query by referencing its name and providing values for the specified arguments*/ Employee employee = (Employee) session.executeQuery("getEmployeeWithName","Bob","Smith");
In addition to the query object configuration options discussed in "Creating a Query Object", several more specialized options are available for customizing query objects
Ordering is a common option for query objects. To order the collection of objects returned from a ReadAllQuery, use the addOrdering(), addAscendingOrdering(), or addDescendingOrdering() methods. You can apply order based on attribute names or query keys and expressions.
// Retrieves objects ordered by lastName then firstName in Ascending Order ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.addAscendingOrdering ("lastName"); query.addAscendingOrdering ("firstName"); Vector employees = (Vector) session.executeQuery(query);
/* Retrieves objects ordered by Street Address, descending case-insensitive order of Cities, and manager's Last Name */ ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); ExpressionBuilder emp = new ExpressionBuilder(); query.addOrdering (emp.getAllowingNull("address").get("street")); query.addOrdering (emp.getAllowingNull("address").get("city").toUpperCase().descending()); query.addOrdering(emp.getAllowingNull("manager").get("lastName")); Vector employees = (Vector) session.executeQuery(query);
Note the use of getAllowingNull, which creates an outer join for the address and manager relationships. This ensures that employees without an address or manager still appear in the list.
For more information, see "Join Reading".
To enable the parameterized SQL on individual queries, use the bindAllParameters() and cacheStatement() methods. This causes OracleAS TopLink to use a prepared statement, binding all SQL parameters and caching the prepared statement. When you re-execute this query, you avoid the SQL preparation, which improves performance.
For more information, see Chapter 10, "Tuning for Performance".
ReadObjectQuery query = new ReadObjectQuery(Employee.class); query.setShouldBindAllParameters(true); query.setShouldCacheStatement(true);
By default, a ReadAllQuery returns its result objects in a vector. You can configure the query to return the results in any collection class that implements the Collection or Map interface.
ReadAllQuery query = new ReadAllQuery(Employee.class); query.useCollectionClass(LinkedList.class); LinkedList employees = (LinkedList) getSession().executeQuery(query);
ReadAllQuery query = new ReadAllQuery(Employee.class); query.useMapClass(HashMap.class, "getFirstName"); HashMap employees = (HashMap) getSession().executeQuery(query); For more information about interfaces, see "Working with Interfaces" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
The ReadAllQuery class includes methods for cursored stream and scrollable cursor support. If you expect the result set to be large, streams and cursors enable you to handle the result sets more efficiently.
For more information, see "Cursors and Streams".
OracleAS TopLink supports both joins and batch reads to optimize database reads. When your query reads a large number of objects, these techniques dramatically decrease the number of times you need to access the database during a read operation. Use the addJoinedAttribute() and addBatchReadAttribute() methods to configure query optimization.
For more information, see "Query Object Performance Options", and Chapter 10, "Tuning for Performance".
Other options to optimize queries include the setMaxRows() method and partial object reading.
You can limit a query to a specified maximum number of rows. Use this feature to avoid queries that can return an excessive number of objects.
To specify a maximum number of rows, use the setMaxRows method, and pass an integer that represents the maximum number of rows for the query.
ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.setMaxRows(5); Vector employees = (Vector) session.executeQuery(query);
The setMaxRows method limits the number of rows the query returns, but does not enable you to acquire more records after the initial result set. If you want to browse the result set in fixed increments, use either cursors or cursored streams.
For more information, see "Java Streams" .
OracleAS TopLink enables you to query for partial objects. For example, you can create a read query that returns a subset of an object's attributes, rather than the entire object. This option improves read performance when the full object is not required. For example, use partial object reading to create a list of objects from which the client chooses the required object.
When you use partial object reading, be aware that:
Use the addPartialAttribute() method to configure partial object reading.
For more information, see "Query Object Performance Options", and Chapter 10, "Tuning for Performance".
You can implement a timeout for query objects. This enables you to automatically abort a hung or lengthy query after the specified time elapses. OracleAS TopLink throws a DatabaseException after the timeout.
To specify a timeout, implement the setQueryTimeout() call and pass the timeout interval as an integer representing the number of seconds before timeout occurs.
// Create the appropriate query and set timeout limitsReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.setQueryTimeout(2); try{ Vector employees = (Vector)session.executeQuery(query); } catch (DatabaseException ex) {// timeout occurs}
Predefined queries enable you to create efficient, reusable queries. OracleAS TopLink creates predefined queries and registers them with a session or descriptor when the application starts. You can then retrieve the queries by name and execute them.
The most common way to create a predefined query is to register the query to a descriptor by specifying an amendment method with the OracleAS TopLink Mapping Workbench for an after load event.
Predefined queries improve the performance of frequently called queries because when you create a query, it is saved and reused as required. Each time you use a query, you create three or more objects that OracleAS TopLink uses to build the SQL statement. If you use predefined queries, OracleAS TopLink creates these objects only once, at binding time. OracleAS TopLink stores the queries as SQL statements in the descriptor and makes them available for the duration of the session.
In addition to performance improvements, predefined queries add structure to a querying framework and give you more options for reading query structure from alternative sources, such as XML.
Named queries improve application performance, because they reduce the resources required to run a query.
The readAllObjects(Class c, Expression e) creates a ReadAllQuery, which builds the other objects it needs to perform its task. After the you execute the readEmployeesMatchingLastName method, the query, expression, expressionBuilder, and any other related objects become garbage. Each time you call this method, OracleAS TopLink creates these related objects again, uses them once, and then discards them.
The use of named queries eliminates this behavior. To configure named queries, use a descriptor amendment method. This creates named queries when you open a database session.
public class MyTopLinkManager { // some code that manages sessions, login, etc... ... // This method is called by front end when needing to query on last names public Vector readEmployeesMatchingLastName(String theName) { ExpressionBuilder eBuilder = new ExpressionBuilder(); Expression exp = eBuilder.get("lastName").like(theName+"%"); return session.readAllObjects(Employee.class, exp); } }
OracleAS TopLink stores named queries by name on a per descriptor basis. When the application needs a query, it calls the named query and passes the required arguments. Because OracleAS TopLink builds the query when it opens the database session, the query is immediately available. In addition, the query is named and bound to a descriptor, so it is reusable.
The first time you execute a named query, OracleAS TopLink calculates the core SQL based on your database platform and schema. OracleAS TopLink caches this information and reuses it if you reuse the query.
OracleAS TopLink creates and registers named queries in a centralized location, usually your descriptor amendment method. Storing all queries in one location facilitates the reuse of queries and simplifies query maintenance.
Rarely used queries may be more efficient when built on an as-needed basis. If you seldom use a given query, it may not be worthwhile to build and store that query when you invoke a session.
A named finder is an OracleAS TopLink query registered with an EJB container under a specific name. When using named finders, the find method on the Home interface must correspond to the name of an OracleAS TopLink query registered with the container. To implement and register the query with the container, use an OracleAS TopLink descriptor amendment method or session amendment class.
/* The named finder in this example uses an OracleAS TopLink query named
findCustomersInCity */
public Enumeration findCustomersInCity(String City)throws FinderException,
RemoteException;
Before you build and implement the findCustomersInCity finder shown in Example 6-51, define the corresponding named query, and register it with the project descriptor. To build the named query, employ:
Use EJB QL, SQL, or the OracleAS TopLink expression framework in the OracleAS TopLink Mapping Workbench to:
Use the OracleAS TopLink expression framework to add the query employing a user defined method. Define these methods in one of the following ways:
preLogin method to a session event listener class. Specify the session event listener classes using the event-listener-class element in the toplink-ejb-jar.xml descriptor (see Example 6-53).
/* This example defines the findCustomersInCity query in the amendment method
of the descriptor */
public static void amendment(Descriptor descriptor) {
// create a query...
descriptor.getQueryManager().addQuery("findCustomersInCity", query);
/* This example defines the findCustomersInCity query in the preLogin method of a session event listener class and specifies the session event listener class in the toplink-ejb-jar.xml deployment descriptor */ public void preLogin(SessionEvent event) {// create a query...event.getSession().getDescriptor(Customer.class).getQueryManager().addQuery("fin dCustomersInCity", query); }
To use the OracleAS TopLink expression framework, define the finder in the OracleAS TopLink Mapping Workbench to specify the finder as a query object. Set the reference class to the name of the bean against which you run the query.
For more information, see "Query Objects".
If you build your finder in code, use the builder.getParameter() call to retrieve the arguments defined in the query. Use the arguments for comparison, combining them with various predicates and operators, such as equal(), like(), and anyOf().
public static void addCustomerFinders(Descriptor descriptor) {/* This code supports the query, Enumeration findCustomersInCity(String aCity)Since this finder returns an Enumeration, it requires a ReadAllQuery. The finder is a "NAMED" finder that is registered with the QueryManager*///1 Define the query.ReadAllQuery query = new ReadAllQuery(); query.setName("findCustomersInCity"); query.addArgument("aCity"); query.setReferenceClass(CustomerBean.class);//2 Use an expressionExpressionBuilder builder = new ExpressionBuilder(); query.setSelectionCriteria builder.get("city").like(builder.getParameter("aCity"));/*3 You can set options on the query, such as query.refreshIdentityMapResult();*///4 Register the query with the querymanager.descriptor.getQueryManager().addQuery("findCustomersInCity",query); }
You can use a named query without the need to provide the matching implementation on the Home interface. To do this, use the Generic Named finder provided by OracleAS TopLink. This finder takes the name of the named query and a vector of arguments as parameters.
public Enumeration findAllByNamedQuery(String queryName, Vector arguments)
throws RemoteException, FinderException;
For more information about finders, see "EJB Finders".
To perform complex operations, you can combine query redirectors with the OracleAS TopLink query framework. To create a redirector, implement the oracle.toplink.queryframework.QueryRedirector interface. The query mechanism executes the Object invokeQuery(DatabaseQuery query, DatabaseRow arguments, Session session) method and waits for the results.
OracleAS TopLink provides one pre-implemented redirector, the MethodBasedQueryRedirector method. To use this redirector, create a static invoke method on a class, and use the setMethodName(String) call to specify the method to invoke.
ReadObjectQuery query = new ReadObjectQuery(Employee.class); query.setName("findEmployeeByAnEmployee"); query.addArgument("employee"); MethodBaseQueryRedirector redirector = new MethodBaseQueryRedirector(QueryRedirectorTest.class, "findEmployeeByAnEmployee"); query.setRedirector(redirector); Descriptor descriptor = getSession().getDescriptor(query.getReferenceClass()); descriptor.getQueryManager().addQuery(query.getName(), query); Vector arguments = new Vector(); arguments.addElement(employee); objectFromDatabase = getSession().executeQuery(query,arguments); public class QueryRedirectorTest{ public static Object findEmployeeByAnEmployee(DatabaseQuery query, oracle.toplink.publicinterface.DatabaseRow arguments, oracle.toplink.sessions.Session session) { ((ReadObjectQuery) query).setSelectionObject(arguments.get("employee")); return session.executeQuery(query); } }
Redirect finders enable you to specify a finder in which the implementation is defined as a static method on an arbitrary helper class. When you invoke the finder, it redirects the call to the specified static method.
The finder can have any arbitrary parameters. If the finder includes parameters, OracleAS TopLink packages them into a vector and passes them to the redirect method.
Because you define the redirect finder implementation independently from the bean that invokes it, you can build the redirect finder to accept any type and number of parameters. This enables you to create a generic redirect finder that accepts several different parameters and return types, depending on input parameters.
A common strategy for using redirect finders is to create a generic finder that:
The redirect method contains the logic required to extract the relevant data from the parameters and uses it to construct an OracleAS TopLink query.
Redirect finders are complex and can be difficult to configure. They also require an extra helper method to define the query.
ejb-jar.xml file, and leave the ejb-ql tag empty.
Home interface, the localHome interface, or both, as required.
For more information, see "Customizing OracleAS TopLink Descriptors with Amendment Methods".
The amendment method then adds a query to the descriptor's query manager, as follows:
ReadAllQuery query = new ReadAllQuery(); query.setRedirector(new MethodBaseQueryRedirector (examples.ejb.cmp20.advanced. FinderDefinitionHelper.class,"findAllEmployeesByStreetName")); descriptor.getQueryManager().addQuery ("findAllEmployeesByStreetName", query);
The redirect method must return either a single entity bean (object) or a vector. Here are the possible method signatures:
public static Object redirectedQuery2(oracle.toplink.sessions.Sessions, Vector args)
and
public static Vector redirectedQuery4(oracle.toplink.sessions.Sessions, Vector args)
When you implement the query method, ensure that the method returns the correct type. For methods that return more than one bean, set the return type to java.util.Vector. OracleAS TopLink converts this result to java.util.Enumeration (or Collection) if required.
|
Note: The redirect method also interprets an OracleAS TopLink session as a parameter. For more information about an OracleAS TopLink session, see Chapter 4, "Sessions". |
At runtime, the client invokes the finder from the entity bean home and packages the arguments into the args vector in order of appearance from the finder method signature. The client passes the vector to the redirect finder, which uses them to execute an OracleAS TopLink expression.
public class RedirectorTest { private Session session; private Project project; public static void main(String args[]) { RedirectorTest test = new RedirectorTest(); test.login(); try { // Create the arguments to be used in the query Vector arguments = new Vector(1); arguments.add("Smith"); // Run the query Object o = test.getSession() .executeQuery(test.redirectorExample(), arguments); o.toString(); } catch (Exception e) { System.out.println("Exception caught -> " + e); e.printStackTrace(); } } public ReadAllQuery redirectorExample() { // Create a redirector MethodBasedQueryRedirector redirector = new MethodBasedQueryRedirector(); // Set the class containgin the public static method redirector.setMethodClass(RedirectorTest.class); // Set the name of the method to be run redirector.setMethodName("findEmployeeByLastName"); // Create a query and add the redirector created above ReadAllQuery readAllQuery = new ReadAllQuery(Employee.class); readAllQuery.setRedirector(redirector); readAllQuery.addArgument("lastName"); return readAllQuery; } //Call the static method public static Object findEmployeeByLastName(oracle.toplink.sessions .Session session, Vector arguments) { // Create a query ReadAllQuery raq = new ReadAllQuery(); raq.setReferenceClass(Employee.class); raq.addArgument("lastName"); // Create the selection criteria ExpressionBuilder employee = new ExpressionBuilder(); Expression whereClause = employee.get("lastName").equal(arguments.firstElement()); // Set the selection criteria raq.setSelectionCriteria(whereClause); return (Vector)session.executeQuery(raq, arguments); } [...] }
You can define several types of queries with the OracleAS TopLink Mapping Workbench, including custom SQL queries and named queries (which you can build using OracleAS TopLink expressions, EJB QL, or SQL).
For more information about the features and options available to create queries with the OracleAS TopLink Mapping Workbench, see "Understanding Descriptors," in the Oracle Application Server TopLink Mapping Workbench User's Guide.
A query manager is a descriptor-owned object that controls descriptor access to the database. The query manager generates its own SQL to access the database in a transparent manner.
You can modify the query manager to do the following:
Query managers generate SQL for five database actions:
The OracleAS TopLink session class provides default query objects to perform these database functions. However, you can also use the query manager to provide custom query objects or SQL strings to perform these functions.
For example, to replace the OracleAS TopLink readObject function with a stored procedure call, specify the replacement code in the OracleAS TopLink Mapping Workbench. If you use a Sybase database, the stored procedure call to read an object looks like this:
EXEC PROC Read_Employee(@EMP_ID = 4653)
To implement this replacement code, add the following string to read the object:
EXEC PROC Read_Employee(@EMP_ID = #EMP_ID)
In the deployed project, the query manager substitutes the code you specified for the readObject call in any queries that include this call.
For more information about customizing default query methods in the OracleAS TopLink Mapping Workbench, see "Custom SQL Queries" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
To customize the query manager database access methods in Java code, use the getQueryManager() method to invoke the query manager. To change the default database access queries, use an amendment method listed in Table 6-5.
You can set the query manager to automatically append an expression to every query it performs on a class. For example, you can add an expression that filters the database for the valid instances of a given class.
Use this to:
The query manager provides the setAdditionalJoinExpression() and the setMultipleTableJoinExpression() methods for this purpose.
/* The join expression in this example filters invalid instances of employee from the query */
public static void addToDescriptor(Descriptor descriptor) { ExpressionBuilder builder = new ExpressionBuilder(); descriptor.getQueryManager().setAdditionalJoinExpression((builder.getField("EMP.STATUS ").notEqual("DELETED")).and(builder.getField("EMP.STATUS").notEqual("HISTORICAL"))); }
When OracleAS TopLink writes an object to the database, OracleAS TopLink runs an existence check to determine whether to perform an insert or an update.
The query manager enables you to substitute custom logic for the existence check.
For more information on how to implement a custom existence check, see "Specifying Identity Mapping" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
Use the following DescriptorQueryManager methods to modify the default existence checking:
checkCacheForDoesExist()assumeExistenceForDoesExist()assumeNonExistenceForDoesExist()checkDatabaseForDoesExist()setDoesExistQuery(DoesExistQuery)setDoesExistSQLString(String)
Queries can return different types of data, including:
Queries can also return EJBs in systems that use EJB finders.
For more information, see "EJB Finders".
OracleAS TopLink queries generally return Java objects as their result set. OracleAS TopLink queries can return
A collection is a group of Java objects related by a collection class that implements a Collection or Map interface. By default, ReadAll queries return results in a vector, but you can acquire the results in any collection class that implements the Collection or Map interface.
For more information on implementing Collection or Map interfaces, see the Oracle Application Server TopLink Mapping Workbench User's Guide.
A stream is a view of a collection, which can be a file, a device, or a Vector. A stream provides access to the collection, one element at a time in sequence. This makes it possible to implement stream classes in which the stream does not contain all the objects of a collection at the same time.
When a query is likely to generate a large result set, you can implement streams to improve performance.
For more information about streams, including advanced usage, see "Cursors and Streams".
Report query provides developers with a way to access information or data from a set of objects and their related objects. Report query supports database reporting functions and features. Although the report query returns data (not objects), it does enable you to query the returned data and specify it at the object level.
For more information, see "ReportQuery".
OracleAS TopLink caches objects written to and read from the database to maintain object identity. The sequence in which a query checks the cache and database affects query performance. By default, primary key queries check the cache before accessing the database, and all queries check the cache before rebuilding an object from its row.
|
Note: You can override the default behavior in the caching policy configuration information in the OracleAS TopLink descriptor. For more information, see "Explicit Query Refreshes" . |
This section illustrates ways to manipulate the query-cache relationship, including:
OracleAS TopLink maintains a client-side cache to reduce the number of reads required from the database.
The cache in an OracleAS TopLink application holds objects that have already been read from or written to the database. Use of the cache in an OracleAS TopLink application reduces the number of accesses to the database. Because accessing the database is a time-intensive and resource-intensive act, an effective caching strategy is important to the efficiency of your application.
For more information about configuring and using the cache, see Chapter 8, "Cache".
In-memory querying enables you to perform queries on the cache rather than the database. In-memory querying supports the following relationships:
You can configure in-memory query cache usage at the query level for both readObject and readAll queries. OracleAS TopLink supports the following in-memory query features:
checkCacheByPrimaryKey(): The default setting; if a read object query contains an expression that compares at least the primary key, you can obtain a cache hit if you process the expression against the objects in memory.
checkCacheByExactPrimaryKey(): If a read object query contains an expression where the primary key is the only comparison, you can obtain a cache hit if you process the expression against the object in memory.
checkCacheThenDatabase(): You can configure any read object query to check the cache completely before you resort to accessing the database.
checkCacheOnly(): You can configure any read all query to check only the cache and return the result from the cache without accessing the database.
conformResultsInUnitOfWork(): You can configure any read object or read all query within the context of a Unit of Work to conform the results with the changes to the object made within that Unit of Work. This includes new objects, deleted objects and changed objects.
Table 6-6 identifies the in-memory queries options OracleAS TopLink supports.
In-memory queries fail for several reasons, the most common of which are:
OracleAS TopLink provides a mechanism to handle indirection exceptions. To specify how the application must handle these exceptions, use InMemoryQueryIndirectionPolicy class:
Should throw indirection exception: The default setting; it is the only setting that throws indirection exceptions.
Should trigger indirection: Triggers all valueholders to eliminate the problem.
Should ignore exception return conformed: Returns conforming if an untriggered valueholder are encountered.
Should ignore exception return not conformed: Returns not conforming if an untriggered valueholder is encountered.
You can conform query results in the Unit of Work across one-to-many, as well as a combination of one-to-one and one-to-many relationships. The following is an example of a query across two levels of relationships, one-to-many and one-to-one.
Expression exp = bldr.anyOf("managedEmployees").get("address").get("city").equal("Perth");
Exceptions thrown by the conform feature are masked by default. However, OracleAS TopLink includes an API that allows exceptions to be thrown rather than masked. The API is: uow.setShouldThrowConformExceptions(ARGUMENT).
ARGUMENT is an integer with one of the following values:
For more information, see "Validating a Unit of Work".
When a query searches for a single object by primary key, OracleAS TopLink extracts the primary key from the query and attempts to return the object from the cache without accessing the database. If the object is not in the cache, the query executes against the database, builds the resulting object(s), and places it in the identity map.
If the query is based on a non-primary key selection criteria or is a readAll query, the query executes against the database (unless you have selected the checkCacheOnly() option). The query matches primary keys from the result set to objects in the cache and returns the cached objects, if any, in the result set.
If an object is not in the cache, OracleAS TopLink builds the object. If the query is a refreshing query, OracleAS TopLink updates the contents of any objects with the results from the query. Use Object identity (==) if you properly configure and use an identity map.
Clients can refresh objects when they want to ensure that they have the latest data at a particular time.
To disable the identity map cache update, which is normally performed by a read query, call the dontMaintainCache() method. This improves the query performance when you read objects that are not needed later by the application.
This example demonstrates how code reads Employee objects from the database and writes the information to a flat file.
// Reads objects from the employee table and writes them to an employee file. void writeEmployeeTableToFile(String filename, Session session) { Vector employeeObjects; ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.setSelectionCriteria(new ExpressionBuilder.get("id").greaterThan(100)); query.dontMaintainCache(); Vector employees = (Vector) session.executeQuery(query); // Write all the employee data to a file. Employee.writeToFile(filename, employees); }
You can refresh objects in the cache to ensure that they are current with the database while preserving object identity.
To refresh objects in the cache with the data in the database, call the session.refreshObject() method or the readObjectQuery.setShouldRefreshIdentityMapResult(true) method.
You can control the depth at which a refresh updates objects and their related objects. There are three options:
Include the refreshIdentityMapResult() method in a query to force an identity map refresh with the results of the query.
ReadObjectQuery query = new ReadObjectQuery(); query.setReferenceClass(Employee.class); query.setSelectionCriteria(new ExpressionBuilder().get("lastName").equal("Smith")); query.refreshIdentityMapResult(); Employee employee = (Employee) session.executeQuery(query);
The refreshIdentityMapResult() method refreshes the object's attributes, but not the attributes of its privately owned parts. However, under most circumstances, refresh an object's privately owned parts and other related objects to ensure consistency with the database.
To refresh privately owned or related parts, use the following methods:
cascadePrivateParts(): refreshes all privately owned objects
cascadeAllParts(): refreshes all related objects
ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.refreshIdentityMapResult(); query.cascadePrivateParts(); Vector employees = (Vector) session.executeQuery(query);
When an application executes a query, you can store the results of that query in the cache. This is useful for frequently executed queries that run against static data. Caching the results also ensures that the query returns the same results for a given period of time (for example: within the scope of a particular transaction) and then refreshes the data later if required.
Although OracleAS TopLink applications most often perform database write operations through a Unit of Work, you can also write to the database with query objects. This section describes some of the more common strategies for using write queries and includes discussions on:
To execute a write query, use a WriteObjectQuery instance instead of using the writeObject() method of the session. Likewise, substitute DeleteObjectQuery, UpdateObjectQuery and InsertObjectQuery objects for their respective Session methods.
WriteObjectQuery writeQuery = new WriteObjectQuery(); writeQuery.setObject(domainObject); session.executeQuery(writeQuery);
InsertObjectQuery insertQuery= new InsertObjectQuery(); insertQuery.setObject(domainObject); session.executeQuery(insertQuery); /* When you use UpdateObjectQuery without a Unit of Work, UpdateObjectQuery writes all direct attributes to the database */ UpdateObjectQuery updateQuery= new UpdateObjectQuery(); updateQuery.setObject(domainObject2); session.executeQuery(updateQuery); DeleteObjectQuery deleteQuery = new DeleteObjectQuery(); deleteQuery.setObject(domainObject2); session.executeQuery(deleteQuery);
When you execute a write query, it writes both the object and its privately owned parts to the database by default. To build write queries that do not update privately owned parts, include the dontCascadeParts() method in your query definition.
Use this method to:
// theEmployee is an existing employee read from the database. Employee.setFirstName("Bob"); UpdateObjectQuery query = new UpdateObjectQuery(); query.setObject(Employee); query.dontCascadeParts(); session.executeQuery(query);
When you write objects to the database, OracleAS TopLink copies them to the session cache by default. To disable this behavior within a query, call the dontMaintainCache() method within the query. This improves query performance when you insert objects into the database, but must only be used on objects that will not be required later by the application.
This code reads all the objects from a flat file and writes new copies of the objects into a table.
// Reads objects from an employee file and writes them to the employee table. void createEmployeeTable(String filename, Session session) { Iterator iterator; Employee employee; // Read the employee data file. List employees = Employee.parseFromFile(filename); Iterator iterator = employees.iterator(); while (iterator.hasNext()) { Employee employee = (Employee) iterator.next(); InsertObjectQuery query = new InsertObjectQuery(); query.setObject(employee); query.dontMaintainCache(); session.executeQuery(query); }
OracleAS TopLink provides default querying behavior for each of the read and write operations that is sufficient for most applications. In addition, applications can define their own custom queries where required:
For more information, see "Query Managers".
executeQuery() methods of DatabaseSession or UnitOfWork.
Several optimizations are available that improve the performance of your queries, including:
For more information about improving the performance of your application and information on how to optimize queries, see Chapter 10, "Tuning for Performance".
Batch reading propagates query selection criteria through an object's relationship attribute mappings. You can also nest batch reads down through complex object graphs. This significantly reduces the number of required SQL select statements and improves database access efficiency.
For example, in reading n employees and their related projects, OracleAS TopLink may require n + 1 selects. All employees are read at once, but the projects of each are read individually. With batch reading all related projects can also be read with one select by using the original selection criteria, for a total of only 2 selects.
To implement batch reading, use one of the following methods:
query.addBatchReadAttribute(Expression anExpression) API.
For example:
... ReadAllQuery raq = new ReadAllQuery(Trade.class); ExpressionBuilder tradeBuilder = raq.getBuilder(); ... Expression batchReadProduct = tradeBuilder.get("product"); readAllQuery.addBatchReadAttribute(batchReadProduct); Expression batchReadPricingDetails = batchReadProduct.get("pricingDetails"); readAllQuery.addBatchReadAttribute(batchReadPricingDetails); ...
setUsesBatchReading() API on the descriptor's relationship mappings.
For example:
public static void amendTradeDescriptor(Descriptor theDescriptor) { OneToOneMapping productOneToOneMapping = theDescriptor.getMappingForAttributeName("product"); productOneToOneMapping.setUsesBatchReading(true); }
You can combine batch reading and indirection to provide controlled reading of object attributes. For example, if you have one-to-one backpointer relationship attributes, you can defer backpointer instantiation until the end of the query, when all parent and owning objects are instantiated. This prevents unnecessary database access and optimizes OracleAS TopLink cache use.
Consider the following guidelines when you implement batch reading:
For more information, see "Reading Case 2: Batch Reading Objects".
When OracleAS TopLink queries, it 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 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 getAllowingNull() rather than get(), and 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. OracleAS 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. Of these, only Oracle supports the outer join semantics in or clauses.
You can also use outer joins with ordering.
For more information, see "Ordering for Read All Queries" .
Join reading enables you to read data from a one-to-one mapping in conjunction with data from the original query. Join reading is available only for one-to-one mappings. To implement join reading, use either of the following methods:
Query.addJoinedAttribute(Expression anExpression) API.
For example:
... ReadAllQuery raq = new ReadAllQuery(Trade.class); ExpressionBuilder tradeBuilder = raq.getBuilder(); ... Expression portfolio = tradeBuilder.get("portfolio"); readAllQuery.addJoinedAttribute(portfolio); ...
setUsesJoining() API on the OneToOneMapping class, as follows:
public static void amendTradeDescriptor(Descriptor theDescriptor) { OneToOneMapping portfolioOneToOneMapping = theDescriptor.getMappingForAttributeName("portfolio"); portfolioOneToOneMapping.setUsesJoining(true); }
For more information about joins as a performance tool, see Chapter 10, "Tuning for Performance".
Report query enables you to retrieve data from a set of objects and their related objects. Report query supports database reporting functions and features. Although the report query returns data rather than objects, it still enables you to query and specify the data at the object level.
The ReportQuery API returns a collection of ReportQueryResult objects, similar in structure and behavior to a DatabaseRow or a Map.
Report query allows you to:
SUM, MIN, MAX, AVG, and COUNT.
ReportQueryResult. This makes it easy to request the real object from a lightweight result.
This example reports the total and average salaries for Canadian employees grouped by their city.
ExpressionBuilder emp = new ExpressionBuilder(); ReportQuery query = new ReportQuery(emp); query.setReferenceClass(Employee.class); query.addMaximum("max-salary", emp.get("salary")); query.addAverage("average-salary", emp.get("salary")); query.addAttribute("city", emp.get("address").get("city")); query.setSelectionCriteria(emp.get("address").get("country").equal("Canada")); query.addOrdering(emp.get("address").get("city")); query.addGrouping(emp.get("address").get("city")); Vector reports = (Vector) session.executeQuery(query);
Table 6-7 summarizes the most common public methods for ReportQuery. For more information about the available methods for the ReportQuery, see the Oracle Application Server TopLink API Reference.
You can query for parts of objects rather than complete objects. For example, you can build a read query that returns a subset of an object's attributes rather than the entire object. This improves database read performance when you do not require the complete object.
To configure partial object reading, use the addPartialAttribute() method. For more information, see "Partial Object Reading".
Consider the following when you use partial object reading:
Query objects maintain an internal cache of the objects previously returned by the query. This improves query performance and ensures that the query always returns the same objects.
The internal cache is disabled by default. To enable it, use the cacheQueryResults() method in the query.
ReadObjectQuery query = new ReadObjectQuery(); query.setReferenceClass(Employee.class); query.cacheQueryResults(); // The query object reads from the database the first time you invoke it. Employee employee = (Employee) session.executeQuery(query); /* On this second call to execute the query, the query object does not read from the database, but reads from the query object's internal cache instead */ Employee employee = (Employee) session.executeQuery(query);
OracleAS TopLink supports the following Oracle enterprise enhancements for Oracle databases:
Oracle Hints is an Oracle database feature through which a developer makes decisions usually reserved for the optimizer. Developers 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:
addHintString("/*[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:
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 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:
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, set this value to NULL.
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.
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, set this value to NULL.
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);
This 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
OracleAS TopLink offers several advanced mechanisms and techniques that enhance your queries. This section describes the following:
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 the OracleAS TopLink Mapping Workbench or in Java.
For more information about implementing query keys with the OracleAS TopLink Mapping Workbench, see "Working with Query Keys" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
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
// 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);
When you define descriptors for an interface to enable querying, OracleAS TopLink supports querying on an interface, as follows:
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:
Cursors and streams are related mechanisms that enable you to work with large result sets efficiently.
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.
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:
afterLast() method to place the cursor after the last row in the result set.
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.
hasPrevious() method returns TRUE, 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.
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 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 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.
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, consider the following two code examples. Example 6-74 returns a Vector that contains all employee objects. If ACME has 10,000 employees, the Vector contains references to 10,000 Employee objects.
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 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();
To optimize CursoredStream performance, provide a threshold and page size to the useCursoredStream(Threshold, PageSize) method, as follows:
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.
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:
DirectQueryKeys to query for the possible implementors of the interface.
subSelect statement for each concrete class that implements the interface included in the query selection criteria.
ReportQuery.
/*The DirectQueryKeys as generated in the OracleAS TopLink project java source code from the OracleAS TopLink Mapping Workbench */...
descriptor.addDirectQueryKey("locationTypeCode","DEALLOCATION.DEALLOCATIONOBJECTTYPE"); descriptor.addDirectQueryKey("locationTypeId","DEALLOCATION.DEALLOCATIONOBJECTID");
The OracleAS TopLink query framework enables you to construct finders, which are queries that retrieve entity beans. This section describes OracleAS TopLink support for finders, and includes discussions on the following topics and techniques:
To define a finder method for an entity bean that uses the OracleAS TopLink query framework, follow these steps:
ejb-jar.xml file.
When you use OracleAS TopLink CMP, define finder methods on the bean's Home interface, not in the entity bean itself. OracleAS TopLink CMP provides this functionality and offers several strategies to create and customize finders. The EJB container and OracleAS TopLink automatically generate the implementation.
The ejb-jar.xml file contains a project's EJB entity bean information, including definitions for any finders used for the beans. To create and maintain the ejb-jar.xml file, use either a text editor or the OracleAS TopLink Sessions Editor.
The entity tag encapsulates a definition for an EJB entity bean. Each bean has its own entity tag that contains several other tags that define bean functionality, including bean finders.
Example 6-76 illustrates the structure of a typical finder defined within the ejb-jar.xml file.
<entity>... <query> <query-method> <method-name>findLargeAccounts</method-name> <method-params> <method-param>double</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(account) FROM AccountBean account WHERE
account.balance > ?1]]></ejb-ql> </query> ... </entity>
The entity tag contains zero or more query elements. Each query tag corresponds to a finder method defined on the bean's home or local Home interface.
Here are the elements defined in the query section of the ejb-jar.xml file:
description (optional): Provides a description of the finder.
query-method: Specifies the method for a finder or ejbSelect query.
method-name: Specifies the name of a finder or select method in the entity bean implementation class.
method-params: Contains a list of the fully-qualified Java type names of the method parameters.
method-param: Contains the fully-qualified Java type name of a method parameter.
result-type-mapping (optional): Specifies how to map an abstract schema type returned by a query for an ejbSelect method. You can map the type to an EJBLocalObject or EJBObject type. Valid values are Local or Remote
ejb-ql: Used for all EJB QL finders. It contains the EJB QL query string that defines the finder or ejbSelect query. Leave this element empty for non-EJB QL finders.
Call finders enable you to create queries dynamically and generate the queries at runtime rather than deployment time. Call finders pass an OracleAS TopLink SQLCall or StoredProcedureCall as a parameter and return an Enumeration.
OracleAS TopLink provides the implementation for Call finders. To use this feature in a bean, add the following finder definition to the Home interface of your bean.
public Enumeration findAll(Call call) throws RemoteException, FinderException;
When you execute a Call finder, OracleAS TopLink creates the call on the client using the OracleAS TopLink interface oracle.toplink.queryframework.Call. This call has three implementors: EJBQLCall, SQLCall and StoredProcedureCall.
{ SQLCall call = new SQLCall(); call.setSQLString("SELECT * FROM EMPLOYEE"); Enumeration employees = getEmployeeHome().findAll(call); }
{ StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName("READ_ALL_EMPLOYEES"); Enumeration employees = getEmployeeHome().findAll(call); }
To define finder query logic, use OracleAS TopLink expressions. Expression finders support dynamic queries that you generate at runtime rather than deployment time. To use an expression finder, pass the expression as a parameter to a finder that returns an Enumeration.
{ Expression expression = new ExpressionBuilder().get("firstName").like("J%"); Enumeration employees = getEmployeeHome().findAll(expression); }
EJB QL is the standard query language defined in the EJB 2.0 specification. OracleAS TopLink supports EJB QL for both EJB 1.1 and EJB 2.0 beans. EJB QL finders enable you to specify an EJB QL string as the implementation of the query.
EJB QL offers several advantages:
The disadvantage of EJB QL is that it is difficult to use when you construct complex queries.
remote interface.
localHome or the remoteHome interface.
ejb-jar.xml file to synchronize the project to the file.
The OracleAS TopLink Mapping Workbench synchronizes changes between the project and the ejb-jar.xml file.
The following is an example of a simple EJB QL query that requires one parameter. In this example, the question mark ("?") in?1 specifies a parameter.
SELECT OBJECT(employee) FROM Employee employee WHERE (employee.name =?1)
ejb-jar.xml file, and enter the EJB QL string in the ejb-ql tag.
Home interface, the localHome interface, or both, as required.
ejb-jar.xml file location and choose File > Updated Project from the ejb-jar.xml file to read in the finders.
The following is an example of a simple EJB QL query that requires one parameter. In this example, the question mark ("?") in?1 specifies a parameter.
SELECT OBJECT(employee) FROM Employee employee WHERE (employee.name =?1)
To execute a query normally, you supply either a reference class or a SELECT clause.
The basic API for a ReadAll query with EJB QL is:
ReadAllQuery setEJBQLString("...")
ReadAllQuery theQuery = new ReadAllQuery(); theQuery.setReferenceClass(EmployeeBean.class); theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp"); ... Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);
This code creates, populates, and passes a vector of arguments into the executeQuery method
// First define the queryReadAllQuery theQuery = new ReadAllQuery(); theQuery.setReferenceClass(EmployeeBean.class); theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = ?1"); theQuery.addArgument("1"); ...// Next define the ArgumentsVector theArguments = new Vector(); theArguments.add("Bob"); ...// Finally execute the query passing in the argumentsVector returnedObjects = (Vector)aSession.executeQuery(theQuery, theArguments);
When you execute EJB QL directly against the session, it returns a vector of the objects specified by the reference class. The basic API is as follows:
aSession.readAllObjects(<ReferenceClass>, <EJBQLCall>)
/* <EJBQLCall> is the EJBQL string to be executed and <ReferenceClass> is the return class type*/// Call ReadAllObjects on a session.Vector theObjects = (Vector)aSession.readAllObjects(EmployeeBean.class, new EJBQLCall( "SELECT OBJECT (emp) from EmployeeBean emp));
You can use custom SQL code to specify finder logic. SQL enables you to implement logic that might not be possible to express with OracleAS TopLink expressions or EJB QL.
ejb-jar.xml file, and leave the ejb-ql tag empty.
ejb-jar.xml file location and choose File > Updated Project from the ejb-jar.xml file to read in the finders.
The following is an example of a simple SQL finder that requires one parameter. In this example, the hash character, '#', is used to bind the argument projectName within the SQL string.
SELECT * FROM EJB_PROJECT WHERE (PROJ_NAME = #projectName)
OracleAS TopLink provides several predefined finders you can use to execute dynamic queries, in which the logic is determined by the user at runtime. The OracleAS TopLink runtime reserves the names for these finders; they cannot be reused for other finders.
The predefined finders are:
EJBObject findOneByEJBQL(String ejbql, Vector args)Collection findManyByEJBQL(String ejbql, Vector args)EJBObject findOneBySQL(String sql, Vector args)Collection findManyBySQL(String sql, Vector args)EJBObject findOneByQuery(DatabaseQuery query, Vector args)Collection findManyByQuery(DatabaseQuery query, Vector args)
You can also use each of these finders without a vector of arguments. For example, EJBObject findOneByEJBQL(String ejbql) is a valid dynamic finder, but you must replace the return type of EJBObject with your bean's component interface.
ejb-jar.xml file, and leave the ejb-ql tag empty.
Home interface, the localHome interface, or both, as required.
ejb-jar.xml file location and choose File > Updated Project from the ejb-jar.xml file to read in the finders.
If the advanced query options in "Advanced Finder Options" are not required, you need only complete steps 1 and 2.
Do not configure any query options for the
Notes:
findOneByQuery and findManyByQuery dynamic finders, because the client creates the query at runtime and passes it as a parameter to the finder. Set any required system options on that query.
In addition to the preceding dynamic finder, OracleAS TopLink provides a default findAll query that returns all the beans of a given type. As with other dynamic finders, the OracleAS TopLink runtime reserves the name findAll.
For more information about defining and configuring the finder, see "To create a dynamic finder:".
OracleAS TopLink creates the findByPrimaryKey finder to a bean class when the class initializes. You can configure the findByPrimaryKey finder with the various OracleAS TopLink query options.
Because the EJB 2.0 specification requires the container to implement the findByPrimaryKey call on each bean Home interface, do not delete this finder from a bean.
ReadAll finders enable you to create dynamic queries that you generate at runtime rather than deployment time. To use a ReadAll finder, pass an OracleAS TopLink ReadAllQuery as a parameter to a finder that returns an Enumeration.
OracleAS TopLink provides an implementation for ReadAll finders. To use this feature in a bean, add the following finder definition to the Home interface of your bean.
public Enumeration findAll(ReadAllQuery query) throws RemoteException, FinderException;
To execute a ReadAll finder, create the query on the client.
{ ReadAllQuery query = new ReadAllQuery(Employee.class); query.addJoinedAttribute("address"); Enumeration employees = getEmployeeHome().findAll(query); }
To optimize performance, choose the finder type that best suits your needs.
Using OracleAS TopLink expressions offers the following advantages:
Because expressions enable you to specify finder search criteria based on the object model, they are frequently the best choice for constructing your finders.
For more information about implementing finders using OracleAS TopLink expressions, see "Expression Finders".
Redirect finders enable you to implement a finder that is defined on an arbitrary helper class as a static method. When you invoke the finder, OracleAS TopLink redirects the call to the specified static method.
Redirect queries are complex and require an extra helper method to define the query. However, because they support complex logic, they are often the best choice when you need to implement logic unrelated to the bean on which the redirect method is called.
Using SQL to define a finder offers the following advantages:
SQL finders also have the following disadvantages:
SELECT may result in unpredictable errors.
The ejbSelect method is a query method intended for internal use within an entity bean instance. Specified on the abstract bean itself, the ejbSelect method is not directly exposed to the client in the home or component interface. Defined as abstract, each bean can include zero or more such methods.
Select methods have the following characteristics:
ejbSelect as its prefix.
throws clause must specify the javax.ejb.FinderException, although it may also specify application-specific exceptions as well.
result-type-mapping tag in the ejb-jar.xml file determines the return type for ejbSelects. Set the flag to Remote to return EJBObjects; set it to Local, to return EJBLocalObjects.
The format for an ejbSelect method definition looks like this:
public abstract type ejbSelect<METHOD>(...);
The ejbSelect query return type is not restricted to the entity bean type on which the ejbSelect is invoked. Instead, it can return any type corresponding to a container-managed relationship or container-managed field.
Although the select method is not based on the identity of the entity bean instance on which it is invoked, it can use the primary key of an entity bean as an argument. This creates a query that is logically scoped to a particular entity bean instance.
ejbSelect in the ejb-jar.xml file, enter the EJB QL string in the ejb-ql tag, and specify the return type in the result-type-mapping tag (if required).
ejbSelect on the abstract bean class.
ejb-jar.xml file location, and choose File > Updated Project from the ejb-jar.xml file to read in the finders.
ejbSelect query.
The default finder configuration is appropriate for most applications. However, finders also allow for several advanced configuration options.
You can apply various configurations to the underlying query to achieve the correct caching behavior for the application. There are several ways to control the caching options for queries. For most queries, you can set caching options with the OracleAS TopLink Mapping Workbench. For more information, see "Caching objects" in the Oracle Application Server TopLink Mapping Workbench User's Guide.
You can set the caching options on a per-finder basis. Table 6-8 lists the valid values.
For more information about the OracleAS TopLink queries as well as the OracleAS TopLink Unit of Work and how it integrates with JTS, see Chapter 7, "Transactions".
By default, OracleAS TopLink adds all returned objects to the session cache. However, if you know the set of returned objects is very large, and you want to avoid the expense of storing these objects, you can disable this behavior. To override the default configuration, implement the dontMaintainCache() call on the query, or disable returned object caching for the query in the OracleAS TopLink Mapping Workbench.
For more information about disabling caching for returned finder results, see the Oracle Application Server TopLink Mapping Workbench User's Guide.
A finder may return information from the database for an object whose primary key is already in the cache. When set to true, the Refresh Cache option (in the OracleAS TopLink Mapping Workbench) causes the query to refresh the object's nonprimary key attributes with the returned information. This occurs on findByPrimaryKey finders as well as all expression and SQL finders for the bean.
If you build a query in Java code, you can set this option by including the refreshIdentityMapResult() method. This method automatically cascades changes to privately owned parts of the beans. If you require different behavior, configure the query using a dynamic finder instead.
If your application includes an OptimisticLock field, use the refresh cache option in conjunction with the onlyRefreshCacheIfNewerVersion() option. This ensures that the application refreshes objects in the cache only if the version of the object in the database is newer than the version in the cache.
For finders that have no refresh cache setting, the onlyRefreshCacheIfNewerVersion() method has no effect.
Large result sets can be resource intensive to collect and process. To give the client more control over the returned results, configure OracleAS TopLink finders to use cursors. This combines OracleAS TopLink's CursoredStream with the ability of the database to cursor data, and breaks up the result set into smaller, more manageable pieces.
The behavior of a finder including a cursored stream differs from other finder as follows:
CursoredEnumerator.
UserTransaction begin() and commit() to ensure that reads beyond the first page of the cursor have a transaction in which to work.
You can configure any finder that returns a java.util.Enumeration (under EJB 1.1) or a java.util.Collection (under EJB 2.0) to use a cursor. When you create the query for the finder, add the useCursoredStream() option to enable cursoring.
ReadAllQuery raq = new ReadAllQuery(); ExpressionBuilder bldr = new ExpressionBuilder(); raq.setReferenceClass(ProjectBean.class); raq.useCursoredStream(); raq.addArgument("projectName"); raq.setSelectionCriteria(bldr.get("name"). like(bldr.getParameter("projectName"))); descriptor.getQueryManager().addQuery ("findByNameCursored", query);
OracleAS TopLink offers additional elements for traversing finder results. These elements include:
hasMoreElements(): Returns a boolean indicating whether there are any more elements in the result set.
nextElement(): Returns the next available element.
nextElements(int count): Retrieves a Vector of at most count elements from the available results, depending on how many elements remain in the result set.
close(): Closes the cursor on the server. The client must send this message, or the database connection does not close.
Example 6-85 illustrates client-code executing a cursored finder.
import oracle.toplink.ejb.cmpwls11. CursoredEnumerator;//... other imports as necessarygetTransaction().begin(); CursoredEnumerator cursoredEnumerator = (CursoredEnumerator)getProjectHome()
.findByNameCursored("proj%"); Vector projects = new Vector(); for (int index = 0; index < 50; i++) { Project project = (Project)cursoredEnumerator.nextElement(); projects.addElement(project); }// Rest all at once ...Vector projects2 = cursoredEnumerator.nextElements(50); cursoredEnumerator.close(); getTransaction().commit();
As with EJB 1.1, OracleAS TopLink offers additional elements for traversing finder results under EJB 2.0. These elements include:
isEmpty(): As with java.util.Collection, isEmpty() returns a boolean indicating whether the Collection is empty.
size(): As with java.util.Collection, size() returns an integer indicating the number of elements in the Collection.
iterator(): As with java.util.Collection, iterator() returns a java.util.Iterator for enumerating the elements in the Collection.
OracleAS TopLink also offers an extended protocol for oracle.toplink.ejb.cmp.wls.CursoredIterator (based on java.util.Iterator):
close(): Closes the cursor on the server. The client must send this message to close the database connection.
hasNext(): Returns a boolean indicating whether any more elements are in the result set.
next(): Returns the next available element.
next(int count): Retrieves a Vector of at most count elements from the available results, depending on how many elements remain in the result set.
Example 6-86 illustrates client code executing a cursored finder.
//import both CursoredCollection and CursoredIteratorimport oracle.toplink.ejb.cmp.wls.*;//... other imports as necessarygetTransaction().begin(); CursoredIterator cursoredIterator = (CursoredIterator) getProjectHome().findByNameCursored("proj%").iterator(); Vector projects = new Vector(); for (int index = 0; index < 50; i++) { Project project = (Project)cursoredIterator.next(); projects.addElement(project); }// Rest all at once ...Vector projects2 = cursoredIterator.next(50); cursoredIterator.close(); getTransaction().commit();
Most exceptions in queries are database exceptions, resulting from a failure in the database operation. Write operations can also throw an OptimisticLockException on a write, update, or delete operation in applications that use optimistic locking. To catch these exceptions, execute all database operations within a try-catch block.
{ try { Vector employees = session.readAllObjects(Employee.class); } catch (DatabaseException exception) { // Handle exception } }
For more information about exceptions in a OracleAS TopLink application, see Appendix C, "Error Codes and Messages".
|
 Copyright © 2000, 2003 Oracle Corporation. All Rights Reserved. |
|