Skip Headers

Oracle TopLink Developer's Guide 10g Release 3 (10.1.3) B13593-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# Using DatabaseQuery Queries

This section describes creating and executing `DatabaseQuery` queries to perform a variety of basic persistence operations, including the following:

- Reading Objects Using a DatabaseQuery

- Creating, Updating, and Deleting Objects with a DatabaseQuery

- Reading Data with a DatabaseQuery

- Updating Data With a DatabaseQuery

- Specifying a Custom SQL String in a DatabaseQuery

- Specifying a Custom EJB QL String in a DatabaseQuery

- Using Parameterized SQL and Statement Caching in a DatabaseQuery

## Reading Objects Using a DatabaseQuery

This section provides examples that illustrate how to read objects using a `DatabaseQuery`, including the following:

- Basic DatabaseQuery Read Operations

- Reading Objects Using Partial Object Queries

- Reading Objects Using Report Queries

- Reading Objects Using Query By Example

- Specifying Read Ordering

- Specifying a Collection Class

- Specifying the Maximum Rows Returned

- Configuring Query Timeout at the Query Level

- Using Batch Reading

- Using Join Reading

### Basic DatabaseQuery Read Operations

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");

### Reading Objects Using Partial Object Queries

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;

### Reading Objects Using Report Queries

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 `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: Because `ReportQuery` inherits from `ReadAllQuery`, it also supports most `ReadAllQuery` properties.

### Reading Objects Using Query By Example

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);

### Specifying Read Ordering

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".

### Specifying a Collection Class

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.

Example 98-16 Specifying the Collection Class for a Collection

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

Example 98-17 Specifying the Collection Class for a Map

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

### Specifying the 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, 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" .

### Configuring Query Timeout at the Query Level

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").

### Using Batch Reading

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.

### Using Join Reading

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.

#### Using TopLink Workbench

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.

#### Using 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.

Example 98-22 Join Reading a Single Attribute

    ReadAllQuery query = new ReadAllQuery(Employee.class);
    query.addJoinedAttribute("address");
    Vector employees = (Vector)getSession().executeQuery(query);

## Creating, Updating, and Deleting Objects with a DatabaseQuery

This section describes the following:

- Write Query Overview

- Noncascading Write Queries

- Disabling the Identity Map Cache During a Write Query

### Write Query Overview

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);

### UpdateAll Queries

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);

### Noncascading Write Queries

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.

Example 98-26 Performing a Noncascading Write Query

    // theEmployee is an existing employee read from the database
    Employee.setFirstName("Bob");
    UpdateObjectQuery query = new UpdateObjectQuery();
    query.setObject(Employee);
    query.dontCascadeParts();
    session.executeQuery(query);

### Disabling the Identity Map Cache During a Write Query

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.

## Reading Data with a DatabaseQuery

This section describes the following:

- Using a DataReadQuery

- Using a DirectReadQuery

- Using a ValueReadQuery

### Using a DataReadQuery

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.

Example 98-28 Using a DataReadQuery

    DataReadQuery dataReadQuery = new DataReadQuery();
    dataReadQuery.setSQLStatement(sqlStatement);

    // queryResults is a Vector of DatabaseRow objects
    Vector queryResults = (Vector)session.executeQuery(dataReadQuery);

### Using a DirectReadQuery

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.

Example 98-29 Using a DirectReadQuery

    DirectReadQuery directReadQuery = new DirectReadQuery();
    directReadQuery.setSQLStatement(sqlStatement);

    // queryResults is a Vector of DatabaseRow objects
    Vector queryResults = (Vector)session.executeQuery(directReadQuery);

### Using a ValueReadQuery

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: `setSQLString` method) makes your application vulnerable to SQL injection attacks.

## Updating Data With a DatabaseQuery

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").

Example 98-31 Using a DataModifyQuery

    DataModifyQuery query = new DataModifyQuery(new SQLCall("Delete from Employee"));
    session.executeQuery(query);

## Specifying a Custom SQL String in a DatabaseQuery

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: `setSQLString` method) makes your application vulnerable to SQL injection attacks.

## Specifying a Custom EJB QL String in a DatabaseQuery

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);

## Using Parameterized SQL and Statement Caching in a DatabaseQuery

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.