|
Oracle TopLink Developer's Guide
10g Release 3 (10.1.3) B13593-01 |
|
 Previous |
 Next |
|
Oracle TopLink Developer's Guide
10g Release 3 (10.1.3) B13593-01 |
|
|
Previous |
Next |
This section describes creating and executing DatabaseQuery queries to perform a variety of basic persistence operations, including the following:
Creating, Updating, and Deleting Objects with a DatabaseQuery
Using Parameterized SQL and Statement Caching in a DatabaseQuery
This section provides examples that illustrate how to read objects using a DatabaseQuery, including the following:
Example 98-5 illustrates a simple read query. It uses a 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.
Example 98-5 A Simple ReadAllQuery
// This example returns a Vector of employees whose employee ID is > 100 // Initialize the DatabaseQuery 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 method */ query.setSelectionCriteria(new ExpressionBuilder.get("id").greaterThan(100)); // Execute the query Vector employees = (Vector) session.executeQuery(query);
Example 98-6 illustrates a complex readObject query that uses all available configuration options.
Example 98-6 A Named Read Query with Two Arguments
// Define two expressions that map to the first and last names 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 DatabaseQuery 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 method */ 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");
Example 98-7 demonstrates the use of partial object reading. It reads only the last name and primary key for the employees. This reduces the amount of data read from the database.
Example 98-7 Optimization Through Partial Object Reading
/* Read all the employees from the database, ask the user to choose one and return it. This uses partial object reading to read just the last name of the employees. Since TopLink automatically includes the primary key of the object, the full object can easily be read for editing */ List list; // Fetch data from database and add to list box ReadAllQuery query = new ReadAllQuery(Employee.class); query.addPartialAttribute("lastName"); // The next line avoids a query exception query.dontMaintainCache(); Vector employees = (Vector) session.executeQuery(query); list.addAll(employees); // Display list box .... // Get selected employee from list Employee selectedEmployee = (Employee)session.readObject(list.getSelectedItem()); return selectedEmployee;
Example 98-8 reports the total and average salaries for Canadian employees grouped by their city.
Example 98-8 Querying Reporting Information on Employees
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);
The ReportQuery class provides an extensive reporting API, including methods for computing average, maximum, minimum, sum, standard deviation, variance, and count of attributes. For more information about the available methods for the ReportQuery, see the Oracle TopLink API Reference.
|
Note: BecauseReportQuery inherits from ReadAllQuery, it also supports most ReadAllQuery properties.
|
Query by example enables you to specify query selection criteria in the form of a sample object instance that you populate with only the attributes you want to use for the query.
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, optionally, a QueryByExamplePolicy (see "Defining a QueryByExamplePolicy") that specifies configuration settings, such as the operators to use and the attribute values to ignore. You can also combine a query by example with an expression (see "Combining Query by Example and Expressions").
For more information, see "Query by Example".
Example 98-9 Using Query by Example
Example 98-9 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 98-10 Using Query by Example
Example 98-10 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);
Defining a QueryByExamplePolicy
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 do the following:
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 98-11 Query by Example Policy Using like Operator
Example 98-11 uses like operator 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 98-12 Query by Example Policy Using Keywords
Example 98-12 uses keywords for strings and ignores the value -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);
Combining Query by Example and Expressions
To create more complex query by example queries, combine query by example with TopLink expressions, as shown in Example 98-13.
Example 98-13 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);
Ordering is a common DatabaseQuery option. Use the Order tab in TopLink Workbench to order the collection of objects returned from a ReadAllQuery, or the addOrdering, addAscendingOrdering, or addDescendingOrdering methods in Java code. You can apply order based on attribute names or query keys and expressions.
Example 98-14 A Query with Simple Ordering
// Retrieves objects ordered by last name then first name in ascending order
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.addAscendingOrdering ("lastName");
query.addAscendingOrdering ("firstName");
Vector employees = (Vector) session.executeQuery(query);
Example 98-15 A Query with Complex Ordering
/* 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 about configuring read ordering, see "Configuring Read All Query Order".
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, as shown in Example 98-16.
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, as shown in Example 98-18.
Example 98-18 Setting the Maximum Returned Object Size
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 let you 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 "Handling Cursor and Stream Query Results" .
You can set the maximum amount of time that TopLink waits for results from a query. This forces a hung or lengthy query to abort after the specified time has elapsed. TopLink throws a DatabaseException after the timeout interval.
To specify a timeout interval on a per-query basis, use DatabaseQuery method setQueryTimeout and pass the timeout interval as an integer representing the number of seconds before the timeout interval should occur, as Example 98-19 shows.
Example 98-19 DatabaseQuery Timeout
// Create the appropriate query and set timeout limits ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employee.class); query.setQueryTimeout(2); try{ Vector employees = (Vector)session.executeQuery(query); } catch (DatabaseException ex) { // timeout occurs }
To specify a timeout interval for all queries on a particular object type, configure a query timeout interval at the descriptor level (see "Configuring Query Timeout at the Descriptor Level").
Batch reading propagates query selection criteria through an object's relationship attribute mappings. You can also nest batch read operations down through complex object graphs. This significantly reduces the number of required SQL select statements and improves database access efficiency.
Consider the following guidelines when you implement batch reading:
Use batch reading for processes that read in objects and all their related objects.
Do not enable batch reading for both sides of a bidirectional relationship.
Avoid nested batch read operations, because they result in multiple joins on the database, slowing query execution.
For more information, see "Reading Case 2: Batch Reading Objects".
For example, in reading n employees and their related projects, TopLink may require n + 1 select operations. 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 operation by using the original selection criteria, for a total of only two select operations.
To implement batch reading, use one of the following methods:
To add the batch read attribute to a query, use the 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);
…
Add batch reading at the mapping level for a descriptor. Use either TopLink Workbench or a descriptor amendment method to add the 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 back pointer relationship attributes, you can defer back pointer instantiation until the end of the query, when all parent and owning objects are instantiated. This prevents unnecessary database access and optimizes TopLink cache use.
Use join reading to configure a query for a class to return the data to build the instances of that class and its related objects. For more information, see "Join Reading and Object-Level Read Queries".
To add one or more joined attributes to a query, you can use either TopLink Workbench or Java.
To add one or more joined attributes to a query using TopLink Workbench, configure joined attributes when you define named queries (see "Configuring Named Query Optimization") or Java. You cannot use TopLink Workbench to create an ObjectLevelReadQuery with a join expression on a one-to-many mapped attribute: you must use Java.
You can use ObjectLevelReadQuery API to add joined attributes for one-to-one and one-to-many relationships.
Use the ObjectLevelReadQuery method addJoinedAttribute(Expression attributeExpression) to add join expressions to the query. Using this method, you can add multiple joined attributes for one-to-one and one-to-many relationships, including nested joins. The source and target can be the same class type. You cannot use the ObjectLevelReadQuery method addJoinedAttribute with a join expression on a many-to-many mapped attribute.
Use the ObjectLevelReadQuery method addJoinedAttribute with a join expression on a one-to-one mapped attribute to get the class of the ObjectLevelReadQuery and the target of the one-to-one mapped attribute of that class with a single database hit.
Use the ObjectLevelReadQuery method addJoinedAttribute with a join expression on a one-to-many mapped attribute to get the class of the ObjectLevelReadQuery and the target collection of the one-to-many mapped attribute of that class with a single database hit.
Example 98-20 is based on the TopLink ThreeTierEmployee example project. It shows a ReadAllQuery configured to join-read multiple attributes. This query produces the SQL that Example 98-21 shows.
Example 98-20 Join Reading Multiple Attributes
ReadAllQuery query = new ReadAllQuery(Employee.class);
Expression managedEmployees = query.getExpressionBuilder().anyOfAllowingNone(
"managedEmployees"
);
query.addJoinedAttribute(managedEmployees);
query.addJoinedAttribute(managedEmployees.get("address"));
query.addJoinedAttribute(managedEmployees.anyOf("phoneNumbers"));
Vector employees = (Vector)getSession().executeQuery(query);
Example 98-21 SQL for Multiple Attribute Join Reading
SELECT DISTINCT t2.VERSION, t3.EMP_ID, t2.GENDER, t3.SALARY, t2.EMP_ID, t2.F_NAME, t2.L_NAME, t2.MANAGER_ID, t2.ADDR_ID, t2.END_DATE, t2.START_DATE, t2.END_TIME, t2.START_TIME, t0.VERSION, t1.EMP_ID, t0.GENDER, t1.SALARY, t0.EMP_ID, t0.F_NAME, t0.L_NAME, t0.MANAGER_ID, t0.ADDR_ID, t0.END_DATE, t0.START_DATE, t0.END_TIME, t0.START_TIME FROM SALARY t3, EMPLOYEE t2, SALARY t1, EMPLOYEE t0 WHERE ((t3.EMP_ID = t2.EMP_ID) AND ((t0.MANAGER_ID (+) = t2.EMP_ID) AND (t1.EMP_ID (+) = t0.EMP_ID)))
Use the ObjectLevelReadQuery method addJoinedAttribute(java.lang.String attributeName) to configure the query to join-read a single attribute, as Example 98-22 shows.
This section describes the following:
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.
Example 98-23 Using a WriteObjectQuery
WriteObjectQuery writeQuery = new WriteObjectQuery(); writeQuery.setObject(domainObject); session.executeQuery(writeQuery);
Example 98-24 Using InsertObjectQuery, UpdateObjectQuery, and DeleteObjectQuery
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);
Use an UpdateAllQuery to update a large number of objects at once. With this query, you can update a large number of objects with a single SQL statement, instead of reading the objects into memory and updating them individually. Example 98-25 shows an UpdateAllQuery to give all full-time employees a raise.
Since multiple tables cannot be updated from the same SQL statement, the UpdateAllQuery does not support objects that span multiple tables or inheritance Additionally, the UpdateAllQuery must be executed from its own transaction–the unit of work must contain only the query.
Example 98-25
/* Give all full time employees a 10% raise
UpdateAllQuery updateQuery = new UpdateAllQuery();
updateQuery.setReferenceClass(Employee.class);
ExpressionBuilder eb = new ExpressionBuilder();
updateQuery.setSelectionCriteria(eb.get("status").equal("FULL_TIME"));
updateQuery.addUpdateExpression("salary", "salary", "* 1.10);
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 do the following:
Increase performance when you know that only the object's direct attributes have changed.
Resolve referential integrity dependencies when you write large groups of new, independent objects.
|
Note: Because the unit of work resolves referential integrity internally, this method is not required if you use the unit of work to write to the database. |
When you write objects to the database, TopLink copies them to the session cache by default. To disable this within a query, call the dontMaintainCache method within the query. This improves query performance when you insert objects into the database, but must be used only on objects that will not be required later by the application.
Example 98-27 Disabling the Identity Map Cache During a Write Query
Example 98-27 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); }
|
Note: Disable the identity map only when object identity is unimportant in subsequent operations. |
This section describes the following:
You can use a DataReadQuery to execute a selecting SQL string that returns a Collection of the DatabaseRows representing the result set, as Example 98-28 shows.
You can use a DirectReadQuery to read a single column of data (that is, one field) that returns a Collection of the DatabaseRows representing the result set, as Example 98-29 shows.
You can use a ValueReadQuery to read a single data value (that is, one field). A single data value is returned, or null if no rows are returned, as Example 98-30 shows.
Example 98-30 Using a ValueReadQuery
ValueReadQuery valueReadQuery = new ValueReadQuery();
valueReadQuery.setSQLString("SELECT DISTINCT CURRENT TIMESTAMP FROM SYSTABLES");
// result is a single Object value
Object result = session.executeQuery(valueReadQuery);
|
WARNING: Allowing an unverified SQL string to be passed into methods (for example: |
You can use a DataModifyQuery to execute a nonselecting SQL statement (directly or as an SQLCall), as Example 98-31 shows. This is equivalent to Session method executeNonSelectingCall (see "Using an SQLCall").
All DatabaseQuery objects provide a setSQLString method that you can use to define a custom SQL string.
For more information about using custom SQL in queries, see "Using SQL Calls".
Example 98-32 uses SQL to read all employee IDs.
Example 98-32 A Direct Read Query with SQL
DirectReadQuery query = new DirectReadQuery();
query.setSQLString("SELECT EMP_ID FROM EMPLOYEE");
Vector ids = (Vector) session.executeQuery(query);
Example 98-33 uses SQL to switch to a different database.
Example 98-33 A Data Modify Query with SQL
DataModifyQuery query = new DataModifyQuery();
query.setSQLString("USE SALESDATABASE");
session.executeQuery(query);
|
WARNING: Allowing an unverified SQL string to be passed into methods (for example: |
All DatabaseQuery objects provide a setEJBQLString method that you can use to specify a custom EJB QL string.
For more information about using custom EJB QL in queries, see "Using EJB QL Calls".
Provide both a reference class and a SELECT clause, and execute the query in the usual manner.
Example 98-34 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 98-35 defines the query similarly to Example 98-34, but creates, fills, and passes a vector of arguments to the executeQuery method.
Example 98-35 A Simple ReadAllQuery Using EJB QL and Passing Arguments
// First define the query ReadAllQuery theQuery = new ReadAllQuery(); theQuery.setReferenceClass(EmployeeBean.class); theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = ?1"); theQuery.addArgument("1", String.class); ... // 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);
To enable the parameterized SQL on individual queries, use DatabaseQuery methods bindAllParameters and cacheStatement. This causes TopLink to use a prepared statement, binding all SQL parameters and caching the prepared statement. When you reexecute this query, you avoid the SQL preparation, which improves performance.
Example 98-36 A Simple ReadObjectQuery with Parameterized SQL
ReadObjectQuery query = new ReadObjectQuery(Employee.class); query.setShouldBindAllParameters(true); query.setShouldCacheStatement(true);
Alternatively, you can configure parameterized SQL and binding at the Login level for all queries (see "Configuring JDBC Options").
For more information about using parameterized SQL and binding for data access optimization, see "Parameterized SQL (Binding) and Prepared Statement Caching".
|
Note: For applications using a J2EE data source or external connection pool, you must configure statement caching in the J2EE server's data source–not in TopLink. |
