Executing Queries

Read Operation

The readObject() method retrieves a single object from the database. The application must specify the class of object to read. If no object matching the criteria is found, null is returned.

For example, the basic read operation is:

session.readObject(MyDomainObject.class);

This example returns the first instance of MyDomainObject that is found in the table used for MyDomainObject. OracleAS TopLink provides the Expression class to specify querying parameters for a specific object.

When you search for a single, specific object using a primary key, the readObject() method is more efficient than the readAllObjects() method because readObject() can find an instance in the cache without accessing the database. Because a readAllObjects() operation does not know how many objects match the criteria, it always searches the database to find matching objects, even if it finds matching objects in the cache.

import oracle.toplink.sessions.*;
import oracle.toplink.expressions.*;

/* Use an expression to read in the Employee whose last name is Smith. Create an expression using the Expression Builder and use it as the selection criterion of the search */
Employee employee = (Employee) session.readObject(Employee.class, new ExpressionBuilder().get("lastName").equal("Smith"));

Read All Operation

The readAllObjects() method retrieves a Vector of objects from the database and does not order the returned objects. If the query does not find any matching objects, it returns an empty Vector.

Specify the class for the query. You can also include an expression to define more complex search criteria, as illustrated in Example 6-38.

// Returns a Vector of employees whose employee salary > 10000
Vector employees = session.readAllObjects(Employee.class,new ExpressionBuilder.get("salary").greaterThan(10000));

Refresh Operation

The refreshObject() method causes OracleAS TopLink to update the object in memory with data from the database. This operation refreshes any privately owned objects as well.

Writing a Single Object to the Database

When you invoke the writeObject() method, the method performs a does-exist check to determine whether an object exists. If the object exists, then writeObject() updates the object; if it does not exist, then writeObject() inserts a new object.

The writeObject() method writes privately owned objects in the correct order to maintain referential integrity.

Call the writeObject() method when you cannot verify that an object exists on the database.

//Create an instance of employee and write it to the database
Employee susan = new Employee();
susan.setName("Susan");
...
//Initialize the susan object with all other instance variables
session.writeObject(susan); 

Writing All Objects to 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.

// 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 which is not currently in the database will be inserted. All the other employees which are currently stored in the database will be updated */
session.writeAllObjects(employees);

Adding New Objects to the Database

The insertObject() method creates a new object on the database, but does not perform the does-exist check before it attempts the insert operation. The insertObject() method is more efficient than the writeObject() method if you are certain that the object does not yet exist on the database. If the object does exist, the database throws an exception when you execute the insertObject() call.

Modifying Existing Objects in the Database

The updateObject() method updates existing objects in the database, but does not perform the does-exist check before it attempts the update operation. The updateObject() is more efficient than the writeObject()method if you are certain that the object does exist in the database. If the object does not exist, the database throws an exception when you execute the updateObject() call.

Deleting Objects in the Database

To delete an OracleAS TopLink object from the database, read the object from the database and then call the deleteObject() method. This method deletes both the specified object and any privately owned data.

Ordering for Read All Queries

Ordering is a common option for query objects. To order the collection of objects returned from a ReadAllQuery, use the addOrdering(), addAscendingOrdering(), or addDescendingOrdering() methods. You can apply order based on attribute names or query keys and expressions.

// Retrieves objects ordered by lastName then firstName in Ascending Order 
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.addAscendingOrdering ("lastName");
query.addAscendingOrdering ("firstName");
Vector employees = (Vector) session.executeQuery(query);

/* Retrieves objects ordered by Street Address, descending case-insensitive order of Cities, and manager's Last Name */
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
ExpressionBuilder emp = new ExpressionBuilder();
query.addOrdering (emp.getAllowingNull("address").get("street"));
query.addOrdering (emp.getAllowingNull("address").get("city").toUpperCase().descending());
query.addOrdering(emp.getAllowingNull("manager").get("lastName"));
Vector employees = (Vector) session.executeQuery(query);

Parameterized SQL in Query Objects

To enable the parameterized SQL on individual queries, use the bindAllParameters() and cacheStatement() methods. This causes OracleAS TopLink to use a prepared statement, binding all SQL parameters and caching the prepared statement. When you re-execute this query, you avoid the SQL preparation, which improves performance.

For more information, see Chapter 10, "Tuning for Performance".

ReadObjectQuery query = new ReadObjectQuery(Employee.class);
query.setShouldBindAllParameters(true);
query.setShouldCacheStatement(true);

Collection Classes

By default, a ReadAllQuery returns its result objects in a vector. You can configure the query to return the results in any collection class that implements the Collection or Map interface.

ReadAllQuery query = new ReadAllQuery(Employee.class);
query.useCollectionClass(LinkedList.class);
LinkedList employees = (LinkedList) getSession().executeQuery(query);

ReadAllQuery query = new ReadAllQuery(Employee.class);
query.useMapClass(HashMap.class, "getFirstName");
HashMap employees = (HashMap) getSession().executeQuery(query);

For more information about interfaces, see "Working with Interfaces" in the Oracle Application Server TopLink Mapping Workbench User's Guide.

Using Cursoring for a ReadAllQuery

The ReadAllQuery class includes methods for cursored stream and scrollable cursor support. If you expect the result set to be large, streams and cursors enable you to handle the result sets more efficiently.

For more information, see "Cursors and Streams".

Maximum Rows Returned

You can limit a query to a specified maximum number of rows. Use this feature to avoid queries that can return an excessive number of objects.

To specify a maximum number of rows, use the setMaxRows method, and pass an integer that represents the maximum number of rows for the query.

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.setMaxRows(5);
Vector employees = (Vector) session.executeQuery(query);

Partial Object Reading

OracleAS TopLink enables you to query for partial objects. For example, you can create a read query that returns a subset of an object's attributes, rather than the entire object. This option improves read performance when the full object is not required. For example, use partial object reading to create a list of objects from which the client chooses the required object.

When you use partial object reading, be aware that:

• You cannot cache or edit partial objects.

• OracleAS TopLink does not automatically include primary key information in a partially populated object. If you need primary key information (for example, if you want to re-query or edit the object), specify it as one of the required attributes.

Use the addPartialAttribute() method to configure partial object reading.

For more information, see "Query Object Performance Options", and Chapter 10, "Tuning for Performance".

Query Timeout

You can implement a timeout for query objects. This enables you to automatically abort a hung or lengthy query after the specified time elapses. OracleAS TopLink throws a DatabaseException after the timeout.

To specify a timeout, implement the setQueryTimeout() call, and pass the timeout interval as an integer representing the number of seconds before timeout occurs.

// Create the appropriate query and set timeout limits
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.setQueryTimeout(2);
try{
    Vector employees = (Vector)session.executeQuery(query);
} catch (DatabaseException ex) {
    // timeout occurs
}

Use and Reuse

OracleAS TopLink stores named queries by name on a per descriptor basis. When the application needs a query, it calls the named query and passes the required arguments. Because OracleAS TopLink builds the query when it opens the database session, the query is immediately available. In addition, the query is named and bound to a descriptor, so it is reusable.

The first time you execute a named query, OracleAS TopLink calculates the core SQL based on your database platform and schema. OracleAS TopLink caches this information and reuses it if you reuse the query.

Centralized Query Management

OracleAS TopLink creates and registers named queries in a centralized location, usually your descriptor amendment method. Storing all queries in one location facilitates the reuse of queries and simplifies query maintenance.

When Not To Use Named Queries

Rarely used queries may be more efficient when built on an as-needed basis. If you seldom use a given query, it may not be worthwhile to build and store that query when you invoke a session.

OracleAS TopLink Mapping Workbench Using EJB QL, SQL, or Expressions

Use EJB QL, SQL, or the OracleAS TopLink expression framework in OracleAS TopLink Mapping Workbench to:

• Define the query in OracleAS TopLink Mapping Workbench. Specify the query in the Queries tab of the bean descriptor.

• Add the query to the descriptor in a user-defined method.

Java Code Using the OracleAS TopLink Expression Framework

Use the OracleAS TopLink expression framework to add the query employing a user defined method. Define these methods in one of the following ways:

• Use OracleAS TopLink Mapping Workbench to specify a descriptor amendment method on the bean descriptor (see Example 6-52).

• Add a preLogin method to a session event listener class. Specify the session event listener classes using the event-listener-class element in the toplink-ejb-jar.xml descriptor (see Example 6-53).

/* This example defines the findCustomersInCity query in the amendment method of the descriptor */
public static void amendment(Descriptor descriptor) {
// create a query...

descriptor.getQueryManager().addQuery("findCustomersInCity", query);

/* This example defines the findCustomersInCity query in the preLogin method of a session event listener class and specifies the session event listener class in the toplink-ejb-jar.xml deployment descriptor */
public void preLogin(SessionEvent event) {
// create a query...
event.getSession().getDescriptor(Customer.class).getQueryManager().addQuery("findCustomersInCity", query);
}

OracleAS TopLink Expression Framework

To use the OracleAS TopLink expression framework, define the finder in OracleAS TopLink Mapping Workbench to specify the finder as a query object. Set the reference class to the name of the bean against which you run the query.

For more information, see "Query Objects".

If you build your finder in code, use the builder.getParameter() call to retrieve the arguments defined in the query. Use the arguments for comparison, combining them with various predicates and operators, such as equal(), like(), and anyOf().

public static void addCustomerFinders(Descriptor descriptor) {
/* This code supports the query, Enumeration findCustomersInCity(String aCity)
Since this finder returns an Enumeration, it requires a ReadAllQuery. The finder is a "NAMED" finder that is registered with the QueryManager */
//1 Define the query.
ReadAllQuery query = new ReadAllQuery();
query.setName("findCustomersInCity");
query.addArgument("aCity");
query.setReferenceClass(CustomerBean.class);
//2 Use an expression
ExpressionBuilder builder = new ExpressionBuilder();
query.setSelectionCriteria
builder.get("city").like(builder.getParameter("aCity"));
/*3 You can set options on the query, such as  query.refreshIdentityMapResult(); */
//4 Register the query with the querymanager.
descriptor.getQueryManager().addQuery("findCustomersInCity",query);
}

Generic Named Finder

You can use a named query without the need to provide the matching implementation on the Home interface. To do this, use the Generic Named finder provided by OracleAS TopLink. This finder takes the name of the named query and a vector of arguments as parameters.

public Enumeration findAllByNamedQuery(String queryName, Vector arguments) throws RemoteException, FinderException;

Advantages

Because you define the redirect finder implementation independently from the bean that invokes it, you can build the redirect finder to accept any type and number of parameters. This enables you to create a generic redirect finder that accepts several different parameters and return types, depending on input parameters.

A common strategy for using redirect finders is to create a generic finder that:

• Includes logic to perform several tasks

• Reads the first passed parameter to identify the type of finder requested and select the appropriate logic

The redirect method contains the logic required to extract the relevant data from the parameters and uses it to construct an OracleAS TopLink query.

Disadvantages

Redirect finders are complex and can be difficult to configure. They also require an extra helper method to define the query.

To create a redirect finder:

1. Declare the finder in the ejb-jar.xml file, and leave the ejb-ql tag empty.

2. Declare the finder on the Home interface, the localHome interface, or both, as required.

3. Create an amendment method.

   For more information, see "Customizing OracleAS TopLink Descriptors with Amendment Methods".

4. Start OracleAS TopLink Mapping Workbench.

5. Choose Advanced Properties > After Load from the menu for the bean.

6. Specify the class and name of the static method to enable the amendment method for the descriptor.

The amendment method then adds a query to the descriptor query manager, as follows:

ReadAllQuery query = new ReadAllQuery();
query.setRedirector(new MethodBaseQueryRedirector (examples.ejb.cmp20.advanced.
  FinderDefinitionHelper.class,"findAllEmployeesByStreetName"));
descriptor.getQueryManager().addQuery ("findAllEmployeesByStreetName", query);

The redirect method must return either a single entity bean (object) or a vector. Here are the possible method signatures:

public static Object redirectedQuery2(oracle.toplink.sessions.Sessions, Vector args)

and

public static Vector redirectedQuery4(oracle.toplink.sessions.Sessions, Vector args)

When you implement the query method, ensure that the method returns the correct type. For methods that return more than one bean, set the return type to java.util.Vector. OracleAS TopLink converts this result to java.util.Enumeration (or Collection) if required.

At runtime, the client invokes the finder from the entity bean home and packages the arguments into the args vector in order of appearance from the finder method signature. The client passes the vector to the redirect finder, which uses it to execute an OracleAS TopLink expression.

public class RedirectorTest {
    private Session session;
    private Project project;
    public static void main(String args[]) {

       RedirectorTest test = new RedirectorTest();

       test.login();

            try {
            // Create the arguments to be used in the query
                Vector arguments = new Vector(1);
                arguments.add("Smith");

            // Run the query
                Object o = test.getSession()
                .executeQuery(test.redirectorExample(), arguments);
                o.toString();
            }
            catch (Exception e) {
                System.out.println("Exception caught -> " + e);
                e.printStackTrace();
            }
    }

    public ReadAllQuery redirectorExample() {

       // Create a redirector
       MethodBasedQueryRedirector redirector = new MethodBasedQueryRedirector();

       // Set the class containgin the public static method
       redirector.setMethodClass(RedirectorTest.class);

       // Set the name of the method to be run
       redirector.setMethodName("findEmployeeByLastName");

       // Create a query and add the redirector created above
       ReadAllQuery readAllQuery = new ReadAllQuery(Employee.class);
       readAllQuery.setRedirector(redirector);
       readAllQuery.addArgument("lastName");

       return readAllQuery;
    }
    //Call the static method
    public static Object findEmployeeByLastName(oracle.toplink.sessions
      .Session
    session, Vector arguments) {

       // Create a query
       ReadAllQuery raq = new ReadAllQuery();
       raq.setReferenceClass(Employee.class);
       raq.addArgument("lastName");

       // Create the selection criteria
       ExpressionBuilder employee = new ExpressionBuilder();
       Expression whereClause =
       employee.get("lastName").equal(arguments.firstElement());

       // Set the selection criteria
       raq.setSelectionCriteria(whereClause);

       return (Vector)session.executeQuery(raq, arguments);
    }
[...]
}

Customize the Default Query Methods in Java Code

To customize the query manager database access methods in Java code, use the getQueryManager() method to invoke the query manager. To change the default database access queries, use an amendment method listed in Table 6-5.

update Employee set F_NAME = #F_NAME, VERSION = VERSION + 1 where (EMP_ID = #EMP_ID) AND (VERSION = #VERSION)