[ODB1] Chapter 7 - JDOQL Queries

There are various ways to retrieve objects from an ObjectDB database, as shown in section 6.3. An Extent, for instance, can be used to retrieve all the instances of a specified persistent class. When a more selective retrieval is needed, JDOQL (JDO Query Language) is used. JDOQL for JDO is like SQL for RDBMS. It provides object retrieval from the database according to a specified selectioPlan to boost recycling rates in Manchester approved

7.1  Introduction to JDOQL

A basic JDOQL query has the following three components:

• A candidate collection containing persistent objects (usually an Extent)
• A candidate class (usually a persistent class)
• A filter, which is a boolean expression in a Java like syntax

The query result is a subset of objects from the candidate collection that contains only the instances of the candidate class satisfying the given filter. In addition to these three main components, queries can include other optional components, such as parameters, variables and import and order expressions, which are discussed later in this chapter.

A First Query

The following query retrieves all the people whose age is 18 or older:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  Collection result = (Collection)query.execute();

Queries are represented by the javax.jdo.Query interface. A Query instance is constructed by one of the PersistenceManager's newQuery(...) methods. For example, in the query above, the candidate class is Person and the filter is "this.age >= 18". When a candidate collection is not specified explicitly, as it is in this query, the entire Extent of the candidate class is used, and the candidate collection contains all the non embedded instances of the candidate class. In such cases, if an Extent is not managed for the candidate class, the query is not valid.

The execute() method compiles and runs the query. If there is no age field in class Person or if the field exists but its type cannot be compared with an int value, a JDOUserException is thrown. If the query compilation succeeds, the Extent of Person instances in the database is iterated object by object, and the filter is evaluated for every Person instance. The this keyword in the filter represents the iterated object. Only instances for which the evaluation of the filter expression is true are included in the result collection. If an index is defined for the age field (see section 4.5), iteration over the objects in the database would not be required, and the query would execute much faster. The execute() method returns a Collection instance, but its declared return type is Object (for future JDO extensions), so casting is required.

Compilation and execution of queries can also be separated into two commands:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  query.compile();
  Collection result = (Collection)query.execute();

The compile() method checks the syntax of the query and prepares it for execution without executing it. In most cases, an explicit call to compile() is not needed, because when execute() is invoked it automatically compiles the query, if it has not been done so already.

The Result Collection

The query result is represented by a java.util.Collection instance and managed like any ordinary Java collection. For example, the number of retrieved objects can be obtained using the size() method:

  int count = result.size();

Iteration over the result collection is done by a java.util.Iterator instance:

  Iterator itr = result.iterator();
  while (itr.hasNext())
    System.out.println(itr.next());

Similarly, other methods of java.util.Collection can be used on the result collection. There is, however, one important difference between the result collection and other Java collections. Rather than relying on the garbage collector to cleanup when the collection is no longer used, a result collection has to be closed explicitly. This is especially important in client server mode because the result collection on the client side may hold resources on the server side. The closing is performed using methods of the Query instance:

  // Close a single result collection obtained from this query:
  query.close(result);

  // Close all the result collections obtained from this query:
  query.closeAll();

The complete code for printing all the people whose age is 18 or older (using the toString() method of Person) might have the following structure:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  Collection result = (Collection)query.execute(); 
  try {
    Iterator itr = result.iterator();
    while (itr.hasNext())
      System.out.println(itr.next());
  }
  finally {
    query.close(result);
  }

The finally block ensures result collection closing regardless of whether the operations succeeded or threw an exception. An exception is thrown on any attempt to use a closed result collection.

Query Construction

In most queries, the Extent of the candidate class is used as the candidate collection . A Query instance for this type of query can be obtained by one of the following two newQuery(...) forms:

Query newQuery(Extent candidates, String filter)
Query newQuery(Class cls, String filter)

In the first form, the candidate class is automatically set to the class of the specified candidate Extent, and in the second form, the candidate collection is automatically set to the Extent of the specified candidate class (which also covers subclasses). The first form is slightly more flexible because it enables using an Extent with or without subclass support.

There is another form of newQuery(...) for a case in which the candidate collection is a regular Java collection, rather than an Extent:

Query newQuery(Class cls, Collection candidates, String filter)

Querying a collection is less common than querying an Extent. It is useful for filtering a collection in memory, and for requerying the result of a previous JDOQL query.

A query can also be executed without a filter. The result of such a query contains all the candidate objects that are instances of the candidate class. This may be useful, for instance, to obtain the number of objects of a specific class (an operation that is not supported directly by an Extent). The following forms of newQuery(...) are equivalent to the forms described above, but without a filter:

Query newQuery(Extent candidates);
Query newQuery(Class cls);
Query newQuery(Class cls, Collection candidates);

A Query instance can even be constructed by an empty newQuery() form:

Query newQuery();

However, a candidate collection must be provided before query execution (explicitly or implicitly by specifying a candidate class), otherwise an execution of the query throws a JDOUserException. Query components that are not specified when invoking one of the PersistenceManager interface newQuery methods can be assigned later using methods in the Query interface itself:

 void setClass(Class cls) 
 void setCandidates(Collection pcs) 
 void setCandidates(Extent pcs) 
 void setFilter(String filter)

7.2  Query Filter Syntax

A query filter is a string containing a boolean expression in a Java like syntax. It has to be valid in the scope of the candidate class. For example, "this.age >= 18" is a valid filter if the candidate class contains a field with the name age and a type comparable to int. This section describes all the elements that can be used in a JDOQL query filter, except for parameters and variables that are discussed later in this chapter.

Literals

All types of Java literals are supported by JDOQL, as demonstrated by the following table:

As shown in the next section (section 7.3), parameters could be used instead of constant literals to make queries more generic. Parameter values are provided when the query is executed so that the same query can be executed with different parameter values.

The 'this' Keyword

During query evaluation, each object in the candidate collection that is an instance of the candidate class is evaluated by the filter. The evaluated object is represented in the filter by the keyword this, whose type is the candidate class. Usually this is used for accessing fields of the candidate object, but it can also be used to reference a candidate object in other operations, such as method calls and comparison expressions.

Fields

Persistent fields of the candidate class play an important role in query filters. For instance, if a candidate class has a persistent field, verified, of boolean type, the expression "this.verified" is a valid query filter. It selects all the objects with the true value in that field. Other types of persistent fields can participate in queries in combination with comparison expressions, as in "this.age >= 18". The this. prefix can be omitted in Java, therefore, "verified" and "age >= 18" are also valid filter expressions. Field accessing is not limited to the this reference. Persistent fields of any entity that represents a persistent object can be accessed. This includes fields of parameter objects and variable objects (parameters and variables are discussed later in this chapter). The expression this.address.city is also valid if the candidate class contains a persistent field address whose type is a persistent class with a persistent field city.
In Java, a NullPointerException is thrown on any attempt to access a field or a method using a null reference. JDOQL behavior is different. An attempt to access a field or invoke a method using a null reference results in a false evaluation for the containing expression but no exception is thrown. Notice that the candidate object can still be included in the result collection if the failed expression is only a subexpression of the whole filter expression (for example, when the || operator is used).

Operators

Comparison operators

Comparison operators (==, !=, <, >, <=, >=) generate boolean expressions, which are very useful in queries. Comparison operators act in JDOQL as in Java, with a few exceptions:

• Equality operators (==, !=) compare the identity of instances of user persistent classes (as in Java), but use the equals(...) method for system types (String, Date, ArrayList, ...).
• String instances can be compared using all six comparison operators.
• Date instances can be compared using all six comparison operators.
• Numeric wrapper types (Byte, Short, Character, Integer, Long, Float, Double), as well as BigInteger and BigDecimal can participate in comparisons as if they were primitive numeric types (byte, short, char, int, long, float, double).

Logical Operators

Logical operators (&&, ||, !) generate complex boolean expressions out of simpler boolean expressions. These operators function in JDOQL exactly as they do in Java.

Arithmetic Operators

In JDOQL, arithmetic operators (+, -, *, /) can be applied to numeric wrapper types (Byte, Character, Short, Integer, Long, Float, Double, and also BigInteger and BigDecimal). The concatenation operator '+' can only be applied to two strings, but not to a string and some other type as in Java. Besides that, their behavior is the same as in Java.

Bitwise Operators

Only the ~ operator is supported by JDOQL. Binary bitwise operators (&, |) are not supported.

Methods

ObjectDB supports using any method that does not modify persistent objects, including instance methods and static methods. Notice, however, that the minimum requirement of JDO implementation includes support for two string methods (startsWith and endsWith), and two collection methods (contains and isEmpty, where isEmpty() also returns true when invoked on a null value). Therefore, using other methods in queries might be less portable. One of the useful string methods that is not supported by JDO 1.0 but supported in ObjectDB (and will be supported by JDO 2.0) is the match(...) method. It provides string comparison using regular expressions (a possible replacement to the like operator of SQL). To use methods of user defined classes in queries, the user code must be available. In embedded mode, user classes are always available. In client-server mode, on the other hand, the classes are usually available on the client side, but not on the server side. Therefore, to enable user defined methods in query execution on the server side, classes have to be added to the classpath of the server as well.

Casting

Casting is also supported by JDOQL. If a ClassCastException occurs during evaluation the expression is evaluated as false but an exception is not thrown (similarly to handling NullPointerException by JDOQL, as explained above).

Gathering Elements

Usually, a query filter contains a combination of elements. For example:

  Query query = pm.newQuery(Person.class);
  query.setFilter("!this.children.isEmpty() && " +
    "this.age - ((Person)this.children.get(0)).age < 25");
  Collection result = (Collection)query.execute();

This query retrieves all the parents who are older than their older child by not more than 25 years (assuming the child at position 0 is the older child). Support of the get(...) method is an extension of ObjectDB, but all the other elements are standard JDOQL elements. A JDO portable version of this query is shown in section 7.4.

7.3  Query Parameters

Using parameters instead of literals produces more generic queries, which can be executed with different argument values. The role of parameters in JDOQL, however, is for much more than just making more generic queries. The only way to include objects with no literal representation in queries (as Date instances, and instances of user defined classes) is to use parameters.

Primitive Type Parameters

The first query in this chapter (in Section 7.1) includes an int literal:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  Collection result = (Collection)query.execute();

The literal, 18, can be replaced by a parameter, age, whose type is int:

  Query query = pm.newQuery(Person.class, "this.age >= age");
  query.declareParameters("int age");
  Collection result1 = (Collection)query.execute(new Integer(18));
  Collection result2 = (Collection)query.execute(new Integer(21));

Parameters are declared by the declareParameters(...) method, with a syntax that is similar to the syntax of parameter declarations in Java. The name of a parameter has to be a valid identifier in Java. Every parameter that is used in the filter has to be declared; otherwise, the compilation of the query fails. Notice that, in the above query, the this. prefix in this.age is required to distinguish the field from the parameter. This code demonstrates the execution of a query with two different arguments. An attempt to use the no-arg execute() method on a query with parameters throws a JDOUserException, because when a query is executed an argument has to be provided for each of its declared parameters. Wrapper values (Boolean, Byte, Short, Character, Integer, Long, Float, Double) are used instead of primitive values because execute(...) only accepts Object arguments.

Reference Type Parameters

The following query retrieves all the children who were born in the third millennium:

  Calendar calendar = Calendar.getInstance();
  calendar.clear();
  calendar.set(2000, 0, 1);
  Date date = calendar.getTime(); 
 
  Query query = pm.newQuery(Person.class, "this.birthDate >= date");
  query.declareParameters("java.util.Date date");
  Collection result = (Collection)query.execute(date);

The first four lines of code prepare a Date instance representing the first day of the year 2000. The query is declared with a parameter date of type Date, and an argument for this parameter is provided when the query is executed. Class Person must have a persistent field with the name birthDate and type Date. Otherwise, the query compilation fails. As explained in section 7.2, the comparison of Date instances is supported by JDOQL. A full class name, java.util.Date, has to be specified in the parameter declaration, unless an import statement is used, as explained in section 7.5.

Date parameters are required to include Date values in queries. String values, on the other hand, can be represented in queries by literals. Double quote characters are specified in Java strings using the \" sequence:

  Query query = pm.newQuery(Person.class, "this.firstName == \"Steve\"");
  Collection result = (Collection)query.execute();

Alternatively, the literal can be replaced by a parameter:

  Query query = pm.newQuery(Person.class, "this.firstName == firstName");
  query.declareParameters("String firstName");
  Collection result = (Collection)query.execute("Steve");

The parameter type can be specified here as String rather than as java.lang.String because classes of java.lang are automatically imported. More details can be found in section 7.5.

Instances of user defined classes can also be used as parameters:

  Query query = pm.newQuery(Person.class, "this != p1 && this != p2");
  query.declareParameters("Person p1, Person p2");
  Collection result = (Collection)query.execute(person1, person2);

With the above query, all the Person instances are retrieved, except the two Person instances that are specified as arguments. To execute the query, two Person arguments for the two declared parameters must be provided. Also, person1 and person2 must be instances of class Person (or its subclasses), otherwise the query execution throws a JDOUserException.

Array and Maps of Parameters

Queries with up to three parameters can be executed using the following Query interface methods:

 void execute() 
 void execute(Object p1) 
 void execute(Object p1, Object p2) 
 void execute(Object p1, Object p2, Object p3)

For example, all the people in a range of ages can be retrieved by:

  Query query = pm.newQuery(Person.class);
  query.declareParameters("int age1, int age2");
  query.setFilter("this.age >= age1 && this.age <= age2");
  Collection result =
    (Collection)query.execute(new Integer(20), new Integer(60));

The same query can be executed using other Query interface methods:

 void executeWithArray(Object[] parameters) 
 void executeWithMap(Object[] parameters)

Arguments can be passed in an array:

  Query query = pm.newQuery(Person.class);
  query.declareParameters("int age1, int age2");
  query.setFilter("this.age >= age1 && this.age <= age2");
  Integer[] args = new Integer[] { new Integer(20), new Integer(60) };
  Collection result = (Collection)query.executeWithArray(args);

Arguments can also be passed in a map (by names rather than by order):

  Query query = pm.newQuery(Person.class);
  query.declareParameters("int age1, int age2");
  query.setFilter("this.age >= age1 && this.age <= age2");
  Map args = new HashMap();
  args.put("age1", new Integer(20));
  args.put("age2", new Integer(60));
  Collection result = (Collection)query.executeWithMap(args);

Arrays and maps of parameters are useful mainly in executing queries with more than three parameters.

7.4  Query Variables

There are two types of variables in JDOQL:

• Bound variables, whose main role is in querying collection fields
• Unbound variables, which serve as a replacement for the JOIN operation of SQL

Support of bound variables is mandatory, but support of unbound variables is optional in JDO. ObjectDB supports both types of variables.

The contains(...) Method

Bound variables in JDOQL go side by side with the contains(...) method, which is one of the few methods that every JDO implementation should support (see section 7.2). The following query, which does not use variables but does use contains(...), retrieves all the people who live in cities from a specified collection myCities:

  Query query = pm.newQuery(
    Person.class, "cities.contains(this.address.city)");
  query.declareParameters("Collection cities");
  Collection result = (Collection)query.execute(myCities);

The contains(...) method is used mainly in queries with bound variables, but as shown above, it is also useful in queries without variables. The contains(...) method works in JDOQL as in Java, except that JDOQL returns false when contains(...) is called on a null reference, while Java throws a NullPointerException.

Bound Variables

A bound variable is an identifier that appears in the query filter as an argument of contains(...), and all its other appearances in the filter (usually at least one additional appearance) are in subexpressions that are ANDed with that contains(...) clause. In ObjectDB the order of the ANDed expressions is not important, but according to JDO, the contains(...) clause must come first (i.e. on the left side), before any other appearance of the variable in the query filter. The following query retrieves people with at least one child living in London:

  Query query = pm.newQuery(Person.class);
  query.declareVariables("Person child");
  query.setFilter(
    "this.children.contains(child) && child.address.city == \"London\"");
  Collection result = (Collection)query.execute();

A variable child whose type is Person is declared by the declareVariables(...) method. Variables (as well as parameters) can be declared before or after setting the query filter, but they must be declared before the query is compiled and executed, otherwise the query compilation fails because of unknown identifiers.

When an AND expression that includes a bound variable is evaluated, all the possible values for that variable are considered. The expression is evaluated as true if there is at least one variable value for which the expression value is true. In the query above, when a candidate Person is evaluated as this, only child values contained in its children collection are considered, because of the contains(...) clause. If there is such a child for whom the expression on the right side of the && operator is true, the entire AND expression is evaluated to true, and the candidate object is added to the result collection.

Negative Contains(...)

The next query retrieves all the parents who are older than their older child by not more than 25 years (a similar query, but not JDO portable, is shown in section 7.2):

  Query query = pm.newQuery(Person.class);
  query.declareVariables("Person child");
  query.setFilter(
    "this.children.contains(child) && this.age - child.age <= 25"
  Collection result = (Collection)query.execute();

A single child satisfying the "this.age - child.age <= 25" expression is sufficient to include this in the result collection. On the other hand, to retrieve only parents with all children satisfying some condition, a trick has to be used. The following query uses De Morgan's rules to retrieve all the people who are older than every one of their children by not more than 25 years (including people without any children):

  Query query = pm.newQuery(Person.class);
  query.declareVariables("Person child");
  query.setFilter(
    "!(this.children.contains(child) && this.age - child.age > 25)"
  Collection result = (Collection)query.execute();

The expression in parenthesis refers to the people who are older than at least one of their children by more than 25 year. But because of the ! operator, eventually, the result collection contains all the people but them, i.e. all the people who are older than every one of their children by not more than 25 years.

Nested Bound Variables

In some cases, more than one contains(...) clause is needed. The following query retrieves all the people that have at least one child with a cat:

  Query query = pm.newQuery(Person.class);
  query.declareVariables("Person child; Pet pet;");
  query.setFilter("this.children.contains(child) && " +
   "child.pets.contains(pet) && pet.isCat()");
  Collection result = (Collection)query.execute();

When more than one variable is declared, a semicolon is used as a separator. A semicolon at the end of the string is optional. Multiple variables are required above because two collection fields are involved, children and pets. Only Person instances, with a combination of child and pet satisfying the AND expression, are included in the result collection. Because child is dependent on this and pet is dependent on child, the three subexpressions must be ordered as shown above, in order to make the query JDO portable.

Unbound Variables

Unbound variables are variables that are not constrained by a contains(...). The following query retrieves all the people except the oldest and the youngest:

  Query query = pm.newQuery(Person.class);
  query.declareVariables("Person p1; Person p2");
  query.setFilter("this.age > p1.age && this.age < p2.age"); 
  Collection result = (Collection)query.execute();

The type of every unbound variable is expected to be a persistent class with Extent support (not necessarily the candidate class as in this example). The variable can have as values any objects in the Extent. The result collection contains all the instances of the candidate collection (excluding non instances of the candidate class), for which there is at least one combination of variables that makes the query filter evaluate to true.

Unbound variables are supported by ObjectDB, but considered optional by JDO. Queries with unbound variables are similar to JOIN queries in SQL because every combination of variables has to be checked for every candidate object. Just like JOIN queries in SQL, queries with unbound variables may become very slow, so caution is needed when using them.

7.5  Import Declarations

Names of classes are used in many components of JDOQL, including the declaration of parameters and variables, when casting and when accessing static methods and fields (supported by ObjectDB as an extension to JDO). As in Java, full class names that include a package name can always be used. The package name can be omitted only for classes in the java.lang package (e.g. String instead of java.lang.String), for classes that are in the same package as the candidate class, and when a proper import declaration is used.

The following query, previously discussed in section 7.3, serves as a good example:

  Query query = pm.newQuery(Person.class, "this.birthDate >= date");
  query.declareParameters("java.util.Date date");
  Collection result = (Collection)query.execute(date);

The full name of class Date is specified because that class is in neither package java.lang nor the package of the candidate class. Using a short class name (without the package name) causes a query compilation error unless a proper declareImports(...) declaration exists, as shown next:

  Query query = pm.newQuery(Person.class, "this.birthDate >= date");
  query.declareImports("import java.util.Date");
  query.declareParameters("Date date");
  Collection result = (Collection)query.execute(date);

Multiple import statements can also be declared:

  query.declareImports(
    "import java.util.*; import directory.pc.Category;");

The argument of declareImports(...) is expected to use the Java syntax for import statements. Multiple import statements are separated by semicolons. A semicolon at the end of the string is optional.

Of all the JDOQL components, import declarations are the most rarely used because most of the classes in queries belong to java.lang or to the package of the candidate class and because classes of other packages can be specified by their full name without using an import declaration.

7.6  Ordering the Results

A result collection can be ordered by a query. For example:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  query.setOrdering("this.age ascending");
  Collection result = (Collection)query.execute();

As a result of calling setOrdering(...), an ordered collection is returned by the query. Iterating over the result collection using Iterator returns people in order from younger to older. As usual, the this. prefix can be omitted. The ascending keyword specifies ordering from low to high. To order the results in reverse order, the descending keyword can be used:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  query.setOrdering("this.age descending");
  Collection result = (Collection)query.execute();

Iteration over the new result collection returns people in order from older to younger. Note that when setOrdering(...) is not used, ObjectDB orders result collections by object IDs from lower to higher, which means that objects will be returned in the order in which they were added to the database (but this behavior is not specified by JDO).

The syntax of order expressions is similar to the syntax of query filters, except that variables and parameters cannot be used, and the type of each order expression is some comparable type rather than boolean. Valid types include numeric primitive types (byte, short, char, int, long, float, double), numeric wrapper types (Byte, Short, Character, Integer, Long, Float, Double), String, Date, BigInteger and BigDecimal.

Multiple order expressions can also be specified:

  Query query = pm.newQuery(Person.class, "this.age >= 18");
  query.setOrdering("age descending, children.size() ascending");
  Collection result = (Collection)query.execute();

The primary ordering is age and the secondary ordering is children.size() (which is supported by ObjectDB as an extension to JDO). Results are ordered by the primary order expression. The secondary order expression is only needed when two or more result instances share the same primary ordering evaluation.