Oracle® TopLink Developer's Guide 10g Release 3 (10.1.3.1.0) Part Number B28218-01 |
|
|
View PDF |
This chapter explains the following essential TopLink query API calls most commonly used throughout the development cycle:
For more information, see "Using Advanced Query API".
This section provides examples of using the session query methods for the following:
Note: Oracle recommends that you perform all data source operations using a unit of work: doing so is the most efficient way to manage transactions, concurrency, and referential constraints. For more information, see "Understanding TopLink Transactions". |
For more information, see "Session Queries".
Using the session query API, you can perform the following read operations:
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, a null value 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
. 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
method 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.
Example 94-1 readObject Using an Expression
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 put the returned objects in order. 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 94-2.
Using the session query API, you can perform the following create, update, and delete operations:
Writing a Single Object to the Database With a Session Query
Modifying Existing Objects in the Database With a Session Query
When you invoke the writeObject
method, the method performs a does-exist check to determine whether or not 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 in the database.
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.
Example 94-4 Writing Several Objects Using writeAllObjects
// 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 employees Employee susan = new Employee(); ... // Initialize the new instance of employee employees.add(susan); /* Write all employees to the database. The new instance of susan not currently in the database will be inserted. All the other employees currently stored in the database will be updated */ session.writeAllObjects(employees);
The insertObject
method creates a new object in 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 in the database. If the object does exist, the database throws an exception when you execute the insertObject
method.
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
method.
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 94-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 94-5 A Simple ReadAllQuery
// This example returns a Vector of employees whose employee ID is > 100 // Initialize the DatabaseQuery by specifying the query type // and set the reference class for the query ReadAllQuery query = new ReadAllQuery(Employee.class); // Retrieve ExpressionBuilder from the query ExpressionBuilder builder = query.getExpressionBuilder(); /* Configure the query execution. Because this example uses an expression, it uses the setSelectionCriteria method */ query.setSelectionCriteria(builder.get("id").greaterThan(100)); // Execute the query Vector employees = (Vector) session.executeQuery(query);
Example 94-6 illustrates a complex readObject
query that uses all available configuration options.
Example 94-6 A Named Read Query with Two Arguments
// Initialize the DatabaseQuery by specifying the query type // and set the reference class for the query ReadObjectQuery query = new ReadObjectQuery(Employee.class); // Retrieve ExpressionBuilder from the query ExpressionBuilder builder = query.getExpressionBuilder(); // Define two expressions that map to the first and last names of the employee Expression firstNameExpression = emp.get("firstName").equal(emp.getParameter("firstName")); Expression lastNameExpression = emp.get("lastName").equal(emp.getParameter("lastName")); /* 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 94-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 94-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 94-8 reports the total and average salaries for Canadian employees grouped by their city.
Example 94-8 Querying Reporting Information on Employees
ExpressionBuilder emp = new ExpressionBuilder(); ReportQuery query = new ReportQuery(Employee.class, emp); 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 Report
Q
uery
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 Report
Q
uery
, 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 94-9 Using Query-by-Example to Query an Employee
Example 94-9 queries the employee Bob Smith.
Employee employee = new Employee();
employee.setFirstName("Bob");
employee.setLastName("Smith");
// Create a query and set Employee as its reference class
ReadObjectQuery query = new ReadObjectQuery(Employee.class);
query.setExampleObject(employee);
Employee result = (Employee) session.executeQuery(query);
Example 94-10 Using Query-by-Example to Query an Employee's Address
Example 94-10 queries across the employee's address.
Employee employee = new Employee();
Address address = new Address();
address.setCity("Ottawa");
employee.setAddress(address);
// Create a query and set Employee as its reference class
ReadObjectQuery query = new ReadObjectQuery(Employee.class);
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 94-11 Query-by-Example Policy Using like Operator
Example 94-11 uses like
operator for strings and includes only objects whose salary is greater than zero.
Employee employee = new Employee(); employee.setFirstName("B%"); employee.setLastName("S%"); employee.setSalary(0); // Create a query and set Employee as its reference class ReadAllQuery query = new ReadAllQuery(Employee.class); 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 94-12 Query-by-Example Policy Using Keywords
Example 94-12 uses keywords for strings and ignores the value -1.
Employee employee = new Employee(); employee.setFirstName("bob joe fred"); employee.setLastName("smith mc mac"); employee.setSalary(-1); // Create a query and set Employee as its reference class ReadAllQuery query = new ReadAllQuery(Employee.class); 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 94-13.
Example 94-13 Combining Query-by-Example with Expressions
Employee employee = new Employee(); employee.setFirstName("Bob"); employee.setLastName("Smith"); // Create a query and set Employee as its reference class ReadAllQuery query = new ReadAllQuery(Employee.class); query.setExampleObject(employee); // Specify expression ExpressionBuilder builder = query.getExpressionBuilder(); 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 94-14 A Query with Simple Ordering
// Retrieves objects ordered by last name then first name in ascending order
ReadAllQuery query = new ReadAllQuery(Employee.class);
query.addAscendingOrdering ("lastName");
query.addAscendingOrdering ("firstName");
Vector employees = (Vector) session.executeQuery(query);
Example 94-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(Employee.class);
ExpressionBuilder emp = query.getExpressionBuilder();
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 94-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 94-18.
Example 94-18 Setting the Maximum Returned Object Size
ReadAllQuery query = new ReadAllQuery(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 94-19 shows.
Example 94-19 DatabaseQuery Timeout
// Create the appropriate query and set timeout limits ReadAllQuery query = new ReadAllQuery(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 94-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 94-21 shows.
Example 94-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 94-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 94-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 94-23 Using a WriteObjectQuery
WriteObjectQuery writeQuery = new WriteObjectQuery(); writeQuery.setObject(domainObject); session.executeQuery(writeQuery);
Example 94-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 94-25 shows an UpdateAllQuery
to give all full-time employees a raise.
Example 94-25 Using UpdateAllQuery
// Give all full time employees a 10% raise
UpdateAllQuery updateQuery = new UpdateAllQuery(Employee.class);
ExpressionBuilder employee = updateQuery.getExpressionBuilder();
updateQuery.setSelectionCriteria(eb.get("status").equal("FULL_TIME"));
updateQuery.addUpdateExpression(employee.get("salary"),
ExpressionMath.multiply(employee.get("salary"), new Float(1.10)));
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. Use the UnitOfWork
method executeQuery
.
In a non-JTA transaction, TopLink provides support for the unit of work and session execute; in a JTA transaction, only the unit of work is supported–there is no support for the session execute. For more information on transactions, see Chapter 97, "Understanding TopLink Transactions".
UpdateAllQuery takes the cache into consideration and ensures that the cache is kept up to date. You can configure the UpdateAllQuery to invalidate cache (see "Cache Invalidation") by setting the cache usage to INVALIDATE_CACHE
(default), or to not use the cache by specifying NO_CACHE
option. You can manipulate these settings through the setCacheUsage
method. You can only update the cache for expressions that can conform. For more information on cache, see Chapter 87, "Understanding the Cache".
UpdateAll queries only support unidirectional one-to-one relationships; there is a full support for direct, and a partial support for aggregate mappings (see Part IX, "Mappings").
Note: You can set an attribute within an aggregate only, but not an entire aggregate. |
These queries do not support foreign key updates, therefore you cannot use these queries to set foreign key fields to null
.
You can use an UpdateAll query with optimistic locking (see "Understanding Descriptors and Locking") at the level of updating a row in a database–there should be no updates in the cache. we will update the locking field on the database. There is also support for version and timestamp locking, as well as indirect support for field locking.
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 94-27 Disabling the Identity Map Cache During a Write Query
Example 94-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 94-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 94-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 94-30 shows.
Example 94-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 94-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 94-32 uses SQL to read all employee IDs.
Example 94-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 94-33 uses SQL to switch to a different database.
Example 94-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 94-34 EJB QL
ReadAllQuery query = new ReadAllQuery(EmployeeBean.class); query.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp"); … Vector returnedObjects = (Vector)session.executeQuery(query);
Example 94-35 defines the query similarly to Example 94-34, but creates, fills, and passes a vector of arguments to the executeQuery
method.
Example 94-35 A Simple ReadAllQuery Using EJB QL and Passing Arguments
// First define the query ReadAllQuery query = new ReadAllQuery(EmployeeBean.class); query.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp WHERE emp.firstName = ?1"); query.addArgument("1", String.class); ... // Next define the arguments Vector arguments = new Vector(); arguments.add("Bob"); ... // Finally, execute the query passing in the arguments Vector returnedObjects = (Vector)session.executeQuery(query, arguments);
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 94-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. |
Named queries improve application performance because they are prepared once and they (and all their associated supporting objects) can be efficiently reused thereafter making them well suited for frequently executed operations.
You can configure named queries at the session (see "Configuring Named Queries at the Session Level") or descriptor (see "Configuring Named Queries at the Descriptor Level") level.
For a session-level named query, you can execute the query using any of the following Session
API calls:
executeQuery(String queryName)
executeQuery(String queryName, arg1)
executeQuery(String queryName, arg1, arg2)
executeQuery(String queryName, arg1, arg2, arg3)
executeQuery(String queryName, Vector args)
Example 94-37 Executing a Session-Level Named Query
Vector args = new Vector(); args.add("Sarah"); Employee sarah = (Employee)session.executeQuery( "employeeReadByFirstName", args );
For a descriptor-level named query, you can execute the query using any of the following Session
API calls, as Example 94-38 shows:
executeQuery(String queryName, Class domainClass)
executeQuery(String queryName, Class domainClass, arg1)
executeQuery(String queryName, Class domainClass, arg1, arg2)
executeQuery(String queryName, Class domainClass, arg1, arg2, arg3)
executeQuery(String queryName, Class domainClass, Vector args)
Example 94-38 Executing a Descriptor Level Named Query
Vector args = new Vector(); args.add("Sarah"); Employee sarah = (Employee)session.executeQuery( "ReadByFirstName", Employee.class, args );
For more information, see "Named Queries"
The TopLink expression framework enables you to define complex queries at the object level. If your application requires a more complex query or one that accesses data or stored procedures directly, you can specify a custom SQL string in an SQL Call
object and provide that Call
object to any query.
You can also specify an SQL string directly on DatabaseQuery
. For more information, see "Specifying a Custom SQL String in a DatabaseQuery".
When using SQL calls, you can use a ReturningPolicy
to control whether or not TopLink writes a parameter out or retrieves a value generated by the database. For more information, see "Configuring Returning Policy".
This section describes the following:
You can provide an SQLCall
object to any query instead of an expression, but the SQL string contained in the SQLCall
must return all data required to build an instance of the queried class.
The SQL string can be a complex SQL query, a stored procedure call, or a stored function call. You can specify input, output, and input/output parameters.
You can invoke an SQLCall
through a session query method (as Example 94-39 illustrates) or through a DatabaseQuery
.
Example 94-39 Session Read Query With Custom SQL
List result = session.executeSelectingCall( new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44"));
WARNING: Allowing an unverified SQL string to be passed into methods makes your application vulnerable to SQL injection attacks. |
TopLink assumes that a token in the custom SQL string of an SQLCall
is a parameter if it is prefixed with one or more number signs ( #
). You can bind values to these parameters using query API, as the following sections describe:
In Example 94-40, you specify last_name
as an input parameter by prefixing its name with one number sign ( #
). Example 94-41 shows how to bind a value to this input parameter when you execute the query.
In Example 94-42, you specify employee_id
as an output parameter by prefixing its name with three number signs ( ###
). You specify the type of the output parameter with SQLCall
method setCustomSQLArgumentType
. You continue to specify last_name
as an input parameter by prefixing its name with a number sign ( #
).
Example 94-42 Specifying a SQLCall with an Output Parameter Using the ### Prefix
SQLCall sqlCall = new SQLCall("begin; INSERT INTO EMPLOYEE (L_NAME) VALUES (#L_NAME) RETURNING EMP_ID INTO ###employee_id; end"); sqlCall.setCustomSQLArgumentType("employee_id", Integer.class);
Example 94-43 Executing a SQLCall with an Output Parameter
ValueReadQuery query = new ValueReadQuery();
query.setCall(sqlCall);
query.addArgument("last_name"); // input
Vector args = new Vector();
args.add("MacDonald");
Number employeeID = (Number)getSession().executeQuery(query, args);
You can also obtain results for an output parameter declared to be of type CURSOR
.
In Example 94-44, you specify in_out
as an input and output parameter by prefixing its name with four number signs (####
). The type of the input value determines the type of the output value. In this example, a String
("MacDonald") is passed in and the output value (for EMP_ID
) is returned as a String
.
Example 94-44 Specifying an Input and Output Parameter Using the #### Prefix
SQLCall sqlCall = new SQLCall( "INSERT INTO EMPLOYEE (L_NAME) VALUES (####in_out) RETURNING EMP_ID INTO ####in_out");
Example 94-45 Executing a SQLCall with an Input and Output Parameter
ValueReadQuery query = new ValueReadQuery();
query.setCall(sqlCall);
query.addArgument("in_out"); // input and outpu
Vector args = new Vector();
args.add("MacDonald");
Number employeeID = (Numbere)getSession().executeQuery(query, args);
You can provide a StoredProcedureCall
object to any query instead of an expression or a SQL string, but the procedure must return all data required to build an instance of the class you query.
Example 94-46 A Read-All Query with a Stored Procedure
ReadAllQuery readAllQuery = new ReadAllQuery(); call = new StoredProcedureCall(); call.setProcedureName("Read_All_Employees"); call.useNamedCursorOutputAsResultSet("RESULT_SET"); readAllQuery.setCall(call); Vector employees = (Vector) session.executeQuery(readAllQuery);
Using a StoredProcedureCall
, you can access the following:
Note: You no longer need to useDatabaseQuery method bindAllParameters when using a StoredProcedureCall with OUT or INOUT parameters. However, you should always specify the Java type for all OUT and INOUT parameters. If you do not, be aware of the fact that they default to type String . |
In Example 94-47, you specify the parameter POSTAL_CODE
as an input parameter using the StoredProcedureCall
method addNamedArgument
, and you can specify the value of the argument using method addNamedArgumentValue
.
Example 94-47 Stored Procedure Call with an Input Parameter
StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName("CHECK_VALID_POSTAL_CODE"); call.addNamedArgument("POSTAL_CODE"); call.addNamedArgumentValue("POSTAL_CODE", "L5J1H5"); call.addNamedOutputArgument( "IS_VALID", // procedure parameter name "IS_VALID", // out argument field name Integer.class // Java type corresponding to type returned by procedure ); ValueReadQuery query = new ValueReadQuery(); query.setCall(call); Number isValid = (Number)session.executeQuery(query);
Output parameters enable the stored procedure to return additional information. You can use output parameters to define a ReadObjectQuery
if they return all the fields required to build the object.
In Example 94-48, you specify the parameter IS_VALID as an output parameter using the StoredProcedureCall
method addNamedOutputArgument
.
Example 94-48 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", // procedure parameter name "IS_VALID", // out argument field name Integer.class // Java type corresponding to type returned by procedure ); 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);
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. |
If you are using an Oracle database, you can make use of TopLink cursor and stream query results.
In Example 94-49, you specify the parameter LENGTH
as an input/output parameter and specify the value of the argument when it is passed to the stored procedure using the StoredProcedureCall
method addNamedInOutputArgumentValue
. If you do not want to specify a value for the argument, use method addNamedInOutputArgument
.
Example 94-49 Stored Procedure Call with an Input/Output Parameter
StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName("CONVERT_FEET_TO_METERs"); call.addNamedInOutputArgumentValue( "LENGTH", // procedure parameter name new Integer(100), // in argument value "LENGTH", // out argument field name Integer.class // Java type corresponding to type returned by procedure ) ValueReadQuery query = new ValueReadQuery(); query.setCall(call); Integer metricLength = (Integer)session.executeQuery(query);
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, TopLink raises the session event OutputParametersDetected
to allow the application to process the output parameters.
Example 94-50 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", // procedure parameter name "ERROR_CODE", // out argument field name Integer.class // Java type corresponding to type returned by procedure ); call.useNamedCursorOutputAsResultSet("RESULT_SET"); 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);
You use a StoredProcedureCall
to invoke stored procedures defined on databases that support them. You can also use a StoredFunctionCall
to invoke stored functions defined on databases that support them, that is, on databases for which the DatabasePlatform
method supportsStoredFunctions
returns true
.
In general, both stored procedures and stored functions let you specify input parameters, output parameters, and input and output parameters. For more information, see "Using a StoredProcedureCall". However, stored procedures need not return values, while stored functions always return a single value.
The StoredFunctionCall
class extends StoredProcedureCall
to add one new method: setResult
. Use this method to specify the name (and alternatively both the name and type) under which TopLink stores the return value of the stored function.
When TopLink prepares a StoredFunctionCall
, it validates its SQL and throws a ValidationException
under the following circumstances:
If your current platform does not support stored functions. Stored functions are supported only for Oracle.
If you fail to specify the return type
In Example 94-51, note that the name of the stored function is set using StoredFunctionCall
method setProcedureName
.
Example 94-51 Creating a StoredFunctionCall
StoredFunctionCall functionCall = new StoredFunctionCall(); functionCall.setProcedureName("CHECK_VALID_EMPLOYEE"); functionCall.addNamedArgument("EMP_ID"); functionCall.setResult("FUNCTION_RESULT", String.class); ValueReadQuery query = new ValueReadQuery(); query.setCall(functionCall); query.addArgument("EMP_ID"); Vector args = new Vector(); args.addElement(new Integer(44)); String valid = (String) session.executeQuery(query, args);
The TopLink expression framework lets you define complex queries at the object level. Alternatively, you can specify a custom EJB QL string in an EJB QL Call
object and provide that Call
object to any query.
You can also specify an EJB QL string directly in a DatabaseQuery
. For more information, see "Specifying a Custom EJB QL String in a DatabaseQuery".
You can provide an EJBQLCall
object to any query instead of an expression or EJB QL string, but the procedure must return all data required to build an instance of the class you query.
You can invoke EJB QL queries through the session query methods or through a DatabaseQuery
.
For an EIS root descriptor, you can define EIS interactions to invoke methods on an EIS.
TopLink represents EIS interactions using instances of oracle.toplink.eis.interactions.EISInteraction
. These classes implement the Call
interface and can be used wherever a Call
can be used.
Table 94-1 lists the type of EIS interactions that TopLink supports.
Table 94-1 EIS Interactions
EIS Interaction Type | Description |
---|---|
|
Defines the specification for a call to a J2C interaction that uses indexed records. Builds the input and output records from the arguments by position. |
|
Defines the specification for a call to a J2C interaction that uses mapped records. Builds the input and output records from the arguments by name. |
|
Specifies an instance of |
|
Specifies an instance of |
|
Specifies an instance of |
You can use TopLink to define an interaction for each basic persistence operation (insert
, update
, delete
, read object
, read all
, or does exist
) so that when you query and modify your EIS-mapped objects, the TopLink runtime will use the appropriate EIS interaction. For more information, see "Configuring Custom EIS Interactions for Basic Persistence Operations".
You can also use TopLink to define an interaction as a named query for read object and read-all object queries. These queries are not called for basic persistence operations; you can call these additional queries by name in your application for special purposes. For more information, see "Creating an EIS Interaction for a Named Query".
Most exceptions in queries are database exceptions, resulting from a failure in the database operation (see "Database Exceptions (4002 – 4018)"). 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
}
See Chapter 14, "TopLink Workbench Error Reference" for more information about exceptions in a TopLink application.
TopLink provides a useCollectionClass
method to all subclasses of DataReadQuery
and ReadAllQuery
, that you can use to configure a query to return results as any concrete instance of Collection
or Map
.
Do not confuse collection query result configuration with a mapping container policy (see "Configuring Container Policy"): there is no relationship between the two. Collection query result configuration determines how TopLink returns multiobject results from a particular query. A mapping container policy tells TopLink how your domain object implements a data member that contains a collection.
For example, consider a class Employee
with a data member phoneNumbers
. In your implementation of Employee
, the getPhoneNumbers
method returns a Vector
. Using TopLink Workbench, you map the phoneNumbers
data member as a one-to-many mapping. You configure the mapping container policy so that the mapping contains its value (many PhoneNumber
objects) in a Vector
. This corresponds to your implementation of Employee
.
You define a ReadAllQuery
named localPhoneNumbers
on the DescriptorQueryManager
of the PhoneNumber
. The localPhoneNumbers
query takes one argument, the ID of an Employee
object, and returns all the phone numbers from its phoneNumbers
data member whose area code is 613.
You get this query by name from the DescriptorQueryManager
for PhoneNumber
. You call the useCollectionClass
method on this ReadAllQuery
, passing in the ArrayList
class. You execute the query, passing in the ID of an Employee
. The query returns all the PhoneNumber
objects from the Employee
object's phoneNumbers
data member whose area code is 613. The query returns these results as an ArrayList
.
Table 94-2 lists the ReportQuery
methods you can use to configure how a ReportQuery
returns its results.
Table 94-2 Report Query Result Options
Method | Query Returns | Description |
---|---|---|
|
|
Returns a single attribute (not wrapped in a |
|
|
Returns only the first |
|
Object |
Returns only a single value. Use this option if you know that the |
For more information, see the following: