Using Queries

Creating and Executing a Query

The following methods create and destroy an instance of EventQuery<>:

• Event.createQuery() — takes a String argument containing the text of the SQL query and returns an instance of EventQuery<E>, where parameter E is the target class of the parent Event.

• EventQuery<>.close() — closes this EventQuery<> instance and releases the resources associated with it.

Queries submitted by an instance of EventQuery<E> will return Java objects of the specified generic type E (the target class of the Event instance that created the query object). Queries supported by the EventQuery<> class are a subset of SQL select statements, as follows:

• Queries must consist of a SELECT clause, a FROM clause, and (optionally) standard SQL clauses such as WHERE and ORDER BY.

• The SELECT and FROM clauses must be syntactically legal, but they are actually ignored during query execution. All fields that have been mapped are always fetched from the extent of target class E.

• SQL expressions may not refer to arrays of any type, nor to embedded objects or fields of embedded objects.

• The Caché system-generated object ID may be referred to as %ID. Due to the leading %, this will not conflict with any field called id in a Java class.

The following EventQuery<> methods define and execute the query:

• setParameter() — binds a parameter for the SQL query associated with this EventQuery<>. Takes int index and Object value as arguments, where index specifies the parameter to be set, and value is the value to bind to the specified parameter.

• execute() — executes the SQL query associated with this EventQuery<>. If the query is successful, this EventQuery<> will contain a resultset that can be accessed by the methods described later (see “Processing Query Data”).

The following example executes a simple query on events in the xep.samples.SingleStringSample extent (see “XEP Sample Applications” for information on the sample programs that define and use the SingleStringSample class.).

Using Queries: Creating and executing a query

  Event newEvent = myPersister.getEvent("xep.samples.SingleStringSample");
  String sql = 
    "SELECT * FROM xep_samples.SingleStringSample WHERE %ID BETWEEN ? AND ?";

  EventQuery<SingleStringSample> myQuery = newEvent.createQuery(sql);
  myQuery.setParameter(1,3);  // assign value 3 to first SQL parameter
  myQuery.setParameter(2,12);  // assign value 12 to second SQL parameter
  myQuery.execute();   // get resultset for IDs between 3 and 12

The EventPersister.getEvent() method creates an Event instance named newEvent with SingleStringSample as the target class.

The Event.createQuery() method creates an instance of EventQuery<> named myQuery, which will execute the SQL query and hold the resultset. The sql variable contains an SQL statement that selects all events in the target class with IDs between two parameter values.

The EventQuery<>.setParameter() method is called twice to assign values to the two parameters.

When the EventQuery<>.execute() method is called, the specified query is executed for the extent of the target class, and the resultset is stored in myQuery.

By default, all data is fetched for each object in the resultset, and each object is fully initialized. See “Defining the Fetch Level” for options that limit the amount and type of data fetched with each object.

Processing Query Data

After a query has been executed, the methods described here can be used to access items in the query resultset, and update or delete the corresponding persistent events in the database. The EventQueryIterator<> class implements java.util.Iterator<E> (where E is the target class of the parent EventQuery<E> instance). The following EventQuery<> method creates an instance of EventQueryIterator<E>:

• getIterator() — returns an EventQueryIterator<E> iterator for the current resultset.

EventQueryIterator<> implements java.util.Iterator<E> methods hasNext(), next(), and remove(), plus the following method:

• set() — takes an object of the target class and uses it to update the persistent event most recently fetched by next().

The following example creates an instance of EventQueryIterator<> and uses it to update each item in the resultset:

Using Queries: Iteration with EventQueryIterator<>

  myQuery.execute();  // get resultset
  EventQueryIterator<xep.samples.SingleStringSample> myIter = myQuery.getIterator();
  while (myIter.hasNext()) {
    currentEvent = myIter.next();
    currentEvent.name = "in process: " + currentEvent.name;
    myIter.set(currentEvent);
  }

The call to EventQuery<>.execute() runs the query described in the previous example (see “Creating and Executing a Query”), and the resultset is stored in myQuery. Each item in the resultset is a SingleStringSample object.

The call to getIterator() creates iterator myIter for the resultset currently stored in myQuery.

In the while loop, hasNext() returns true until all items in the resultset have been processed:

• The call to next() returns the next SingleStringSample object from the resultset and assigns it to currentEvent.

• The currentEvent.name property is changed.

• The set() method is called, storing the updated currentEvent object in the database.

Alternate Query Iteration Methods

The EventQuery<> class also provides methods that can be used to process a resultset without using EventQueryIterator<>. (This is an alternative for developers who prefer iteration methods similar to those provided by ObjectScript). After a query has been executed, the following EventQuery<> methods can be used to access items in the query resultset, and update or delete the corresponding persistent events in the database:

• getNext() — returns the next object of the target class from the resultset. Returns null if there are no more items in the resultset. It requires null or an object of the target class as an argument. (In this release, the argument is a placeholder that has no effect on the query).

• updateCurrent() — takes an object of the target class as an argument and uses it to update the persistent event most recently returned by getNext().

• deleteCurrent() — deletes the persistent event most recently returned by getNext() from the database.

• getAll() — uses getNext() to get all items from the resultset, and returns them in a List. Cannot be used for updating or deleting. getAll() and getNext() cannot access the same resultset — once either method has been called, the other method cannot be used until execute() is called again.

See “Accessing Stored Events” for a description of how to access and modify persistent events identified by Id or IdKey.

Important:

Never use EventQuery<> and EventQueryIterator<> iteration methods together

Although query results can be accessed either by direct calls to EventQuery<> methods or by getting an instance of EventQueryIterator<> and using its methods, these access methods must never be used at the same time. Getting an iterator and calling its methods while also making direct calls to the EventQuery<> methods can lead to unpredictable results.

Using Queries: Updating and Deleting Query Data

  myQuery.execute();   // get resultset
  SingleStringSample currentEvent = myQuery.getNext(null);
  while (currentEvent != null) {
    if (currentEvent.name.startsWith("finished")) {
      myQuery.deleteCurrent();   // Delete if already processed
    } else {
      currentEvent.name = "in process: " + currentEvent.name;
      myQuery.updateCurrent(currentEvent);    // Update if unprocessed
    }
    currentEvent = myQuery.getNext(currentEvent);
  }
  myQuery.close();

In this example, the call to EventQuery<>.execute() is assumed to execute the query described in the previous example (see “Creating and Executing a Query”), and the resultset is stored in myQuery. Each item in the resultset is a SingleStringSample object.

The first call to getNext() gets the first item from the resultset and assigns it to currentEvent.

In the while loop, the following process is applied to each item in the resultset:

• If currentEvent.name starts with the string "finished", deleteCurrent() deletes the corresponding persistent event from the database.

• Otherwise, the value of currentEvent.name is changed, and updateCurrent() is called. It takes currentEvent as its argument and uses it to update the persistent event in the database.

• The call to getNext() returns the next SingleStringSample object from the resultset and assigns it to currentEvent.

After the loop terminates, close() is called to release the resources associated with myQuery.

Note:

Always call close() to release resources

Always call close() on an instance of EventQuery<> before it goes out of scope to ensure that all locks, licenses, and other resources associated with the connection are released.

Defining the Fetch Level

The fetch level is an Event property that can be used to control the amount of data returned when running a query. This is particularly useful when the underlying event is complex and only a small subset of event data is required.

The following EventQuery<> methods set and return the current fetch level:

• getFetchLevel() — returns an int indicating the current fetch level of the Event.

• setFetchLevel() — takes one of the values in the Event fetch level enumeration as an argument and sets the fetch level for the Event.

The following fetch level values are supported:

• Event.OPTION_FETCH_LEVEL_ALL — This is the default. All data is fetched, and the returned event is fully initialized.

• Event.OPTION_FETCH_LEVEL_DATATYPES_ONLY — Only datatype fields are fetched. This includes all primitive types, all primitive wrappers, java.lang.String, java.math.BigDecimal, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp and enum types. All other fields are set to null.

• Event.OPTION_FETCH_LEVEL_NO_ARRAY_TYPES — All types are fetched except arrays. All fields of array types, regardless of dimension, are set to null. All datatypes, object types (including serialized types) and collections are fetched.

• Event.OPTION_FETCH_LEVEL_NO_COLLECTIONS — All types are fetched except implementations of java.util.List, java.util.Map, and java.util.Set.

• Event.OPTION_FETCH_LEVEL_NO_OBJECT_TYPES — All types are fetched except object types. Serialized types are also considered object types and are not fetched. All datatypes, array types and collections are fetched.