|
Oracle® Application Server TopLink Application Developer's Guide
10g Release 2 (10.1.2) Part No. B15901-01 |
|
 Previous |
 Next |
|
Oracle® Application Server TopLink Application Developer's Guide
10g Release 2 (10.1.2) Part No. B15901-01 |
|
|
Previous |
Next |
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 Expression class represents an expression, which can be anything from a simple constant to a complex clause with boolean logic. You can manipulate, group, and integrate expressions in several ways.
The ExpressionBuilder class is the factory for constructing new expressions.
The OracleAS TopLink expression framework provides methods through the following classes:
The Expression class provides most general functions, such as toUpperCase.
The 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.
Example 6-2 Using the ExpressionMath Class
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:
The attribute, which represents a mapped attribute or query key of the persistent class
The operator, which is an expression method that implements boolean logic, such as GreaterThan, Equal, or Like
The constant or comparison, which refers to the value used to select the object
In the following code fragment:
expressionBuilder.get("lastName").equal("Smith");
The attribute is lastName.
The operator is equal().
The constant is the string ÒSmithÓ.
The expressionBuilder substitutes for the object or objects to be read from the database. In this example, expressionBuilder represents employees.
Expressions offer the following advantages over SQL when you access a database:
Expressions are easier to maintain because the database is abstracted.
Changes to descriptors or database tables do not affect the querying structures in the application.
Expressions enhance readability by standardizing the 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");
Expressions allow read queries to transparently query between two classes that share a relationship. If these classes are stored in multiple tables in the database, then OracleAS TopLink automatically generates the appropriate join statements to return information from both tables.
Expressions simplify complex operations. For example, the following Java code retrieves all Employees that live on "Meadowlands" whose salary is greater than 10,000:
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:
like()
notLike()
toUpperCase()
toLowerCase()
toDate()
rightPad()
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, in which the argument is the name of a function. For example, note the following expression, which calls a function known as 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, note 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 perform a query.
|
Note: An instance ofExpressionBuilder is specific to a particular query. Do not attempt to build another query using an existing builder, because it still contains information related to the first query.
|
Example 6-3 A Simple Expression Builder Expression
This example uses the query key lastName to reference the field name L_NAME.
Expression expression = new ExpressionBuilder().get("lastName").equal("Young");
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.
Example 6-6 A Subquery Expression Using a Comparison and Count Operation
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);
Example 6-7 A Subquery Expression Using a Comparison and Max Operation
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"));
Example 6-8 A Subquery Expression Using a Not Exists Operation
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.
Example 6-9 A Parallel Expression on Two Independent Employees
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:
Customize mappings
Create reusable queries
Define EJB finders
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.
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" .
Example 6-11 The Use of a Parameterized Expression in a Mapping
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")));
Example 6-12 A Parameterized Expression in a Custom Query
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);
Example 6-13 Nested Parameterized Expressions
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 may 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:
The selector is the identifier (id) by which users refer to the function.
The strings are the constant strings used in printing the function. When printed, the strings alternate with the function arguments.
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 operator, it prints afterwards.
Example 6-14 Creating a New Expression Operator—The toUpperCase Operator
Add the following in a sublass of DatabasePlatform:
ExpressionOperator toUpper = new ExpressionOperator();
toUpper.setSelector();
Vector v = new Vector();
v.addElement("UPPER(");
v.addElement(")");
toUpper.printAs(v);
toUpper.bePrefix();
toUpper.setNodeClass(FunctionExpression.class);
addOperator(toUpper);
// To add this operator for all database
ExpressionOperator.addOperator(toUpper);
// To add to a specific platform
DatabasePlatform platform = session.getLogin().getPlatform();
platform.addOperator(toUpper);
Example 6-15 Accessing a User-Defined Function
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 the class of that object; 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.
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.
Example 6-17 Using getTable() and getField() Together
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.
Example 6-18 Using a Data Query Against a Link Table
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:
Query keys enhance code readability when you define OracleAS TopLink expressions.
Query keys increase portability by making code independent of the database schema. If you rename a field, you can redefine the query key without changing any code that references it.
Unlike interface descriptors that define only common query keys shared by their implementors, aliased fields can have different names in each of the implementor tables.
For more information about query keys with 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.
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.
Example 6-20 One-to-One Query Key
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 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.
Example 6-21 Defining One-to-One Query Key Example
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.
Table 6-1 Elements for Expression Builder
| Element | Method Name |
|---|---|
| Constructors |
ExpressionBuilder() ExpressionBuilder(Class aClass) |
| Expression creation methods |
get(String queryKeyName) getAllowingNull(String queryKeyName) anyOf(String queryKeyName) anyOfAllowingNone(String queryKeyName) getField(String fieldName) in(ReportQuery subQuery) |
Table 6-2 Elements for Expression
| Element | Method Name |
|---|---|
| Constructors | Never use the Expression constructors. Always use an ExpressionBuilder to create a new expression.
|
| Expression operators |
equal(Object object) notEqual(Object object) greaterThan(Object object) lessThan(Object object) isNull() notNull() |
| Logical operators |
and(Expression theExpression) not() or(Expression theExpression) |
| Key word searching |
equalsIgnoreCase(String theValue) likeIgnoreCase(String theValue) |
| Aggregate functions (for use with report query) |
minimum() maximum() |
| Relationship operators |
anyOf(String queryKeyName) anyOfAllowingNone(String queryKeyName) get(String queryKeyName) getAllowingNull(String queryKeyName) getField(String fieldName) |
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.
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
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.
Example 6-26 A Read All Query With a Stored Procedure
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.
|
Note: Not all databases support the use of output parameters to return data. However, because these databases generally support returning result sets from stored procedures, they do not require output parameters. |
Example 6-27 Stored Procedure Call with an Output Parameter
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("POSTAL_CODE");
call.addNamedOutputArgument("IS_VALID", "IS_VALID", Integer.class);
ValueReadQuery query = new ValueReadQuery();
query.bindAllParameters();
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.
Example 6-28 Stored Procedure with a Cursored Output Parameter
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.
Example 6-29 Stored Procedure with Reset Set and Output Parameter Error Code
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.
Table 6-3 Elements for Stored Procedure Call
| Element | Method Name |
|---|---|
| Selection specification |
setProcedureName(String name)
|
| Input parameters |
addNamedArgument(String name) addNamedArgument(String dbName, String javaName) addNamedArgumentValue(String dbName, Object value) addUnnamedArgument(String javaName) addUnnamedArgumentValue(Object value) |
| Input/Output parameters |
addNamedInOutputArgument(String name) addNamedInOutputArgument(String dbName, String javaName, String javaName, Class type) addNamedInOutputArgumentValue(String dbName, Object value, String javaName, Class type) public void addUnnamedInOutputArgument(String inArgumentFieldName, String outArgumentFieldName, Class type) public void addUnnamedInOutputArgumentValue(Object inArgumentValue, String outArgumentFieldName, Class type) |
| Output parameters |
addNamedOutputArgument(String name) addNamedOutputArgument(String dbName, String javaName) addNamedOutputArgument(String dbName, String javaName, Class javaType) addUnnamedOutputArgument(String javaName) public void addunnamedOutputArgument(String argumentFieldName, Class type) |
| Cursor output parameters |
useNamedCursorOutputAsResultSet(String argumentName) useUnnamedCursorOutputAsResultSet() |
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:
You do not need to know the database structure (tables, fields).
You can use relationships in a query to provide navigation from attribute to attribute.
You can construct queries using the attributes of the entity beans instead of using database tables and fields.
EJB QL queries are portable because they are database-independent.
You can use 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:
Add EJB QL queries to descriptors in OracleAS TopLink Mapping Workbench.
Build and use EJB QL dynamically at runtime, using a ReadQuery or the OracleAS TopLink session.
For more information about EJB QL queries with 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.
Example 6-30 A Simple ReadAllQuery Using EJB QL
ReadAllQuery theQuery = new ReadAllQuery();
theQuery.setReferenceClass(EmployeeBean.class);
theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp");
…
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);
Example 6-31 A Simple ReadAllQuery Using EJB QL and Passing Arguments
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. Here is the basic API:
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");
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.
|
Note: Query by example is not available for EJB 2.0 beans. |
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.
Example 6-32 Using Query by Example
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);
Example 6-33 Using Query by Example
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:
Use LIKE or other operations to compare attributes. By default, query by example allows only EQUALS.
Modify the set of values query by example ignores (the IGNORE set). The default ignored values are zero (0), empty strings, and FALSE.
Force query by example to consider attribute values, even if the value is in the IGNORE set.
Use isNull or notNull for attribute values.
To specify a query by example policy, include an instance of QueryByExamplePolicy with the query.
Example 6-34 Query by Example Policy Using Like
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);
Example 6-35 Query by Example Policy Using Key Words
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.
Example 6-36 Combining Query by Example with 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.
Table 6-4 Elements for Query By Example Policy
| Element | Method Name |
|---|---|
| Special operations |
addSpecialOperation(Class theClass, String operation)
|
| Forced inclusion |
alwaysIncludeAttribute(java.lang.Class exampleClass, java.lang.String attributeName)
|
|
|
includeAllValues()
|
| Attribute exclusion |
excludeValue(Object value) excludeDefaultPrimitiveValues() |
| Null equality |
setShouldUseEqualityForNulls(boolean flag)
|
