In traditional (declarative) JDOQL (JDO 1.0) it was necessary to specify the component parts (filter, candidate class, ordering, etc) of the query using the mutator methods on the Query. In JDO 2 you can now specify it all in a single string. This string has to follow a particular pattern, but provides the convenience that many people have been asking for. The pattern to use is as follows

SELECT [UNIQUE] [<result>] [INTO <result-class>]
        [FROM <candidate-class> [EXCLUDE SUBCLASSES]]
        [WHERE <filter>]
        [VARIABLES <variable declarations>]
        [PARAMETERS <parameter declarations>]
        [<import declarations>]
        [GROUP BY <grouping>]
        [ORDER BY <ordering>]
        [RANGE <start>, <end>]

The "keywords" in the query are shown in UPPER CASE but can be in UPPER or lower case.

Lets give an example of a query using this syntax

SELECT UNIQUE FROM org.jpox.samples.Employee ORDER BY departmentNumber

so we form the parts of the query as before, yet here we just specify it all in a single call.

Sometimes you know that the query can only every return 0 or 1 objects. In this case you can simplify your job by adding

query.setUnique(true);

In this case the return from the execution of the Query will be a single Object, so you've no need to use iterators, just cast it to your candidate class type.

The normal behaviour of JDOQL queries is to return a List of Objects of the type of the candidate class. Sometimes you want to have the query perform some processing and return things like count(), min(), max() etc. You specify this with

query.setResult("count(param1), max(param2), param3");

In this case the results will be List<Object[]> since there are more than 1 column in each row. If you have only 1 column in the results then the results would be List<Object>. If you have only aggregates (sum, avg, min, max, count) in the result clause then there will be only 1 row in the results and so the results will be of the form Object[] (or Object if only 1 aggregate).

In JDOQL you can connect two parts of a query using something known as a variable. For example, we want to retrieve all objects with a collection that contains a particular element, and where the element has a particular field value. We define a query like this

Query query = pm.newQuery("SELECT FROM org.jpox.samples.store.Supplier WHERE products.contains(prod) && prod.name == \"Beans\"");

So we have a variable in our query called "prod" that connects the two parts. We now declare it

query.declareVariables("org.jpox.samples.store.Product prod");

This is known as an explicit variable, since it is declared explicitly. Multiple variables can be declared using semi-colon (;) to separate variable declarations.

query.declareVariables("String var1; String var2");

JDO2 also defines implicit variables, where we skip the declareVariables call and rely on the JDO implementation to work out the type of the variable. This is supported by JPOX from version 1.1.0-beta-5.

When specifying JDOQL queries you can use normal Java classes in the query. You need to define to JDOQL which class you are talking about. You can do this via the following calls

query.declareImports("import mypackage.myclass; import mypackage2.*");
query.declareParameters("String myparam1, Date myparam2");

This tells JDO (and more specifically JPOX) to look for any classes specified as parameters, in the defined list of imports. java.lang is imported automatically here so you don't need to specify this. It should be noted that if you specify a package in the imports (using * notation), then JPOX will have to search for your class in that package and this can have a (small) performance impact. You can get around this by not using "*" notation on imports. What are shown above are called explicit parameters.

In JDO2 you can alternatively utilise implicit parameters. This is done by specifying the parameter name in the query directly (prefixed by a colon). So you may write a Single-String query like

Query query = pm.newQuery("SELECT FROM org.jpox.samples.store.Product WHERE price < :limit");
List results = (List)query.execute(new Double(200.0));

Here we haven't declared the parameter. We simply prefixed it with a colon, and its type will be determined implicitly from the query. This is supported in JPOX from 1.1.0-beta-5.

In JDOQL you can declare parameters and variables. Just like in Java it is often convenient to just declare a variable as say Date, and then have an import in your Java file importing the java.util.Date class. The same applies in JDOQL. Where you have defined parameters or variables in shorthand form, you can specify their imports like this

query.declareVariables("Date startDate");
query.declareParameters("Locale myLocale");
query.declareImports("import java.util.Locale; import java.util.Date;");

Just like in Java, if you declare your parameters or variables in fully-specified form (for example "java.util.Date myDate") then you do not need any import.

The JDOQL uses the imports declaration to create a type namespace for the query. During query compilation, the classes used in the query, if not fully qualified, are searched in this namespace. The type namespace is built with the following:

• primitives types
• java.lang.* package
• package of the candidate class
• import declarations (if any)

To resolve a class, the JDOQL compiler will use the class fully qualified name to load it, but if the class is not fully qualified, it will search by prefixing the class name with the imported package names declared in the type namespace.

All classes loaded by the query must be acessible by either the candidate class classloader, the PersistenceManager classloader or the current Thread classloader.

The search algorithm for a class in the JDOQL compiler is the following:

• if the class is fully qualified, load the class.
• if the class is not fully qualified, iterate each package in the type namespace and try to load the class from that package. This is done until the class is loaded, or the type namespace package names are exausted. If the class cannot be loaded an exception is thrown.

Note that the search algorithm can be problematic in performance terms if the class is not fully qualified or declared in imports using package notation. To avoid such problems, either use fully qualified class names or import the class in the imports declaration. The 2 below queries are examples of good usage:

query.declareImports("import java.util.Locale;");
query.declareParameters("Locale myLocale");

query.declareParameters("java.util.Locale myLocale");

However, the below example will suffer in performance, due to the search algorithm.

query.declareImports("import java.math.*; import java.util.*;");
query.declareParameters("Locale myLocale");

With JDOQL you can specify the ordering using the normal JDOQL syntax for a parameter, and then add ascending or descending (UPPER or lower case are both valid) are to give the direction. In addition the abbreviated forms of asc and desc (again, UPPER and lower case forms are accepted) to save typing. For example, you may set the ordering as follows

query.setOrdering("productId DESC");

Sometimes you have a Query that returns a large number of objects. You may want to just display a range of these to your user. In this case you can do

query.setRange(10,20);

This has the effect of only returning items 10 through to 19 (inclusive) of the query's results. The clear use of this is where you have a web system and you're displaying paginated data, and so the user hits page down, so you get the next "n" results.

setRange is implemented efficiently for MySQL, Postgresql, HSQL (using the LIMIT SQL keyword) and Oracle (using the ROWNUM keyword), with the query only finding the objects required by the user directly in the datastore. For other RDBMS the query will retrieve all objects up to the "to" record, and will not pass any unnecessary objects that are before the "from" record.

By default with JDOQL queries when you run the query it will load all FetchPlan fields of the objects of the candidate class. The FetchPlan used can be modified via

FetchPlan fp = query.getFetchPlan();

In some situations you don't want all FetchPlan fields retrieving, and JPOX provides an extension to turn this off, like this

Query query = pm.newQuery(...);
query.addExtension("org.jpox.query.useFetchPlan", "false");

JDOQL queries are converted into SQL and use a mix of INNER and LEFT OUTER joins. In some situations the chosen join may not be optimal and you may know that using only INNER joins would be more efficient. If you have this situation you can use a JPOX extension to enable this behaviour

Query query = pm.newQuery(...);
query.addExtension("org.jpox.jdoql.useInnerJoinsOnly", "true");

You can also specify this for all queries using a PMF property "org.jpox.jdoql.useInnerJoinsOnly".

JPOX allows control over whether objects found by a fetch (JDOQL query) are locked during that transaction so that other transactions can't update them in the meantime. This is enabled on a per-query basis by doing

Query query = pm.newQuery(...);
query.addExtension("org.jpox.jdoql.useUpdateLock", "true");

You can also specify this for all queries for all PMs using a PMF property "org.jpox.useUpdateLock". In addition you can perform this on a per-transaction basis by doing

((org.jpox.store.rdbms.RDBMSTransaction)pm.currentTransaction()).setUseUpdateLock(true);

In JDOQL you access fields in the query by referring to the field name. For example, if you are querying a class called Product and it has a field "price", then you access it like this

Query query = pm.newQuery(org.jpox.samples.store.Product.class, "price < 150.0");

In addition to the persistent fields, you can also access "public static final" fields of any class. You can do this as follows

Query query = pm.newQuery(org.jpox.samples.store.Product.class, "taxPercent < org.jpox.samples.store.Product.TAX_BAND_A");

So this will find all products that include a tax percentage less than some "BAND A" level. Where you are using "public static final" fields you can either fully-qualify the class name or you can include it in the "imports" section of the query (see later).

JDOQL supports the following literals: IntegerLiteral, FloatingPointLiteral, BooleanLiteral, CharacterLiteral, StringLiteral, and NullLiteral.

The following list describes the operator precedence in JDOQL.

1. Cast
2. Unary ("~") ("!")
3. Unary ("+") ("-")
4. Multiplicative ("*") ("/") ("%")
5. Additive ("+") ("-")
6. Relational (">=") (">") ("<=") ("<") ("instanceof")
7. Equality ("==") ("=!")
8. Boolean logical AND ("&")
9. Boolean logical OR ("|")
10. Conditional AND ("&&")
11. Conditional OR ("||")

The concatenation operator(+) concatenates a String to either another String or Number. Concatenations of String or Numbers to null results in null.

Here's a simple example for finding the elements of a class with a field below a particular threshold level. Here we pass in the threshold value (limit), and sort the output in order of ascending price.

Declarative JDOQL :
Query query = pm.newQuery(org.jpox.samples.store.Product.class,"price < limit");
query.declareParameters("double limit");
query.setOrdering("price ascending");
List results = (List)query.execute(150.00);

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM org.jpox.samples.store.Product WHERE " +
                "price < limit PARAMETERS double limit ORDER BY price ASCENDING");
List results = (List)query.execute(150.00);

For completeness, the class is shown here

class Product
{
    String name;
    double price;
    java.util.Date endDate;
    ...
}

<jdo>
    <package name="org.jpox.samples.store">
        <class name="Product">
            <field name="name">
                <column length="100" jdbc-type="VARCHAR"/>
            </field>
            <field name="abreviation">
                <column length="20" jdbc-type="VARCHAR"/>
            </field>
            <field name="price"/>
            <field name="endDate"/>
        </class>
    </package>
</jdo>

Here's another example using the same Product class as above, but this time comparing to a Date field. Because we are using a type in our query, we need to import it ... just like you would in a Java class if you were using it there.

Declarative JDOQL :
Query query = pm.newQuery(org.jpox.samples.store.Product.class,
                          "endDate > best_before_limit");
query.declareImports("import java.util.Date");
query.declareParameters("Date best_before_limit");
query.setOrdering("endDate descending");
Collection results = (Collection)query.execute(my_date_limit);

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM org.jpox.samples.store.Product " +
                "WHERE endDate > best_before_limit " +
                "PARAMETERS Date best_before_limit " +
                "import java.util.Date ORDER BY endDate DESC");
List results = (List)query.execute(my_date_limit);

This example makes use of the JDO 2.0 ability to specify queries in the Meta-Data for a class. Here we show the sample Meta-Data for one of these queries, and how to invoke it. The following query searches for employees with a salary below 12, ordering them in ascending salary order.

<query name="SalaryBelow12" language="javax.jdo.query.JDOQL"><![CDATA[
    SELECT FROM org.jpox.samples.company.Employee WHERE salary < 12 ORDER BY salary ASC
]]></query>

To use this named query, we do as follows

Query query = pm.newNamedQuery(org.jpox.samples.company.Employee.class, "SalaryBelow12");
List results = (List)query.execute();

This example demonstrates use of the "instanceof" operator. We have a class A that has a field "b" of type B and B has subclasses B1, B2, B3. Clearly the field "b" of A can be of type B, B1, B2, B3 etc, and we want to find all objects of type A that have the field "b" that is of type B2. We do it like this

Declarative JDOQL :
Query query = pm.newQuery(org.jpox.samples.A.class);
query.setFilter("b instanceof org.jpox.samples.B2");
List results = (List)query.execute();

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM org.jpox.samples.A WHERE b instanceof org.jpox.samples.B2");
List results = (List)query.execute();