JDO : JDOQL Queries

JDO defines ways of querying objects persisted into the datastore. It provides its own object-based query language (JDOQL). JDOQL is designed as the Java developers way of having the power of SQL queries, yet retaining the Java object relationship that exist in their application model. A typical JDOQL query may be created in several ways. Here's an example expressed in the 3 supported ways

Single-String JDOQL :
Query q = pm.newQuery(
    "SELECT FROM mydomain.Person WHERE lastName == 'Jones' && " +
    "age < age_limit PARAMETERS int age_limit");
List<Person> results = (List<Person>)q.execute(20);

Declarative JDOQL :
Query q = pm.newQuery(Person.class);
q.setFilter("lastName == 'Jones' && age < age_limit");
q.declareParameters("int age_limit");
List<Person> results = (List<Person>)q.execute(20);

Typesafe JDOQL (DataNucleus) :
TypesafeQuery<Person> tq = pm.newTypesafeQuery(Person.class);
QPerson cand = QPerson.candidate();
List<Person> results = 
    tq.filter(cand.lastName.eq("Jones").and(cand.age.lt(tq.intParameter("age_limit"))))
        .setParameter("age_limit", "20").executeList();

So here in our example we select all "Person" objects with surname of "Jones" and where the persons age is below 20. The language is intuitive for Java developers, and is intended as their interface to accessing the persisted data model. As can be seen above, the query is made up of distinct parts. The class being selected (the SELECT clause in SQL), the filter (which equates to the WHERE clause in SQL), together with any sorting (the ORDER BY clause in SQL), etc.

In this section we will express all examples using the single-string format since it is the simplest to highlight how to use JDOQL, so please refer to the Declarative JDOQL and Typesafe JDOQL guides for details if wanting to use those.


JDOQL Single-String syntax

JDOQL queries can be defined in a single-string form, 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 (but not MiXeD case). So giving an example

SELECT UNIQUE FROM mydomain.Employee ORDER BY departmentNumber

Candidate Class

By default the candidate "class" with JDOQL has to be a persistable class. This can then be referred to in the query using the this keyword (just like in Java). Also by default your query will return instances of subclasses of the candidate class. You can restrict to just instances of the candidate by specifying to exclude subclasses (see EXCLUDE SUBCLASSES in the single-string syntax, or by setSubclasses(false) when using the declarative API).

DataNucleus also allows you to specify a candidate class as persistent interface. This is used where we want to query for instances of implementations of the interface. Let's take an example. We have an interface

@PersistenceCapable
public interface ComputerPeripheral
{
    @PrimaryKey
    long getId();
    void setId(long val);

    @Persistent
    String getManufacturer();
    void setManufacturer(String name);

    @Persistent
    String getModel();
    void setModel(String name);
}

and we have the following implementations

@PersistenceCapable
public class Mouse implements ComputerPeripheral
{
    ...
}

@PersistenceCapable
public class Keyboard implements ComputerPeripheral
{
    ...
}

So we have made our interface persistable, and defined the identity property(ies) there. The implementations of the interface will use the identity defined in the interface. To query it we simply do

Query q = pm.newQuery("SELECT FROM " + ComputerPeripheral.class.getName());
List<ComputerPeripheral> results = (List<ComputerPeripheral>)q.execute();

The key rules are

  • You must define the interface as persistent
  • The interface must define the identity/primary key member(s)
  • The implementations must have the same definition of identity and primary key

Filter

The most important thing to remember when defining the filter for JDOQL is that think how you would write it in Java, and its likely the same. The filter has to be a boolean expression, and can include the candidate, fields/properties, literals, methods, parameters, variables, operators, instanceof, subqueries and casts.

Fields/Properties

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

price < 150.0

Note that, just like in Java, if you want to refer to a field/property of the candidate you can prefix the field by this

this.price < 150.0

You can also chain field references if you have a candidate class Product with a field of (persistable) type Inventory, which has a field name, so you could do

this.inventory.name == 'Backup'

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

taxPercent < mydomain.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).

An important thing to remember with JDOQL is that you do not do explicit joins. You instead use the fields/properties and navigate to the object you want to make use of in your query.

With 1-1/N-1 relations this is simply a reference to the field/property, and place some restriction on it, like this

this.inventory.name == 'MyInventory'

With 1-N/M-N relations you would use something like

containerField.contains(elemVar)

and thereafter refer to elemVar for the element in the collection to place restrictions on the element. Similarly you can use elemVar in the result clause


Methods

When writing the "filter" for a JDOQL Query you can make use of some methods on the various Java types. The range of methods included as standard in JDOQL is not as flexible as with the true Java types, but the ones that are available are typically of much use. While DataNucleus supports all of the methods in the JDO standard, it also supports several yet to be standardised (extension) method. The tables below also mark whether a particular method is supported for evaluation in-memory.

Please note that you can easily add support for other methods for evaluation "in-memory" using this DataNucleus plugin point

Please note that you can easily add support for other methods with RDBMS datastore using this DataNucleus plugin point

String Methods

Method Description Standard In-Memory
startsWith(String) Returns if the string starts with the passed string
startsWith(String, int) Returns if the string starts with the passed string, from the passed position
endsWith(String) Returns if the string ends with the passed string
indexOf(String) Returns the first position of the passed string
indexOf(String,int) Returns the position of the passed string, after the passed position
substring(int) Returns the substring starting from the passed position
substring(int,int) Returns the substring between the passed positions
toLowerCase() Returns the string in lowercase
toUpperCase() Retuns the string in UPPERCASE
matches(String pattern) Returns whether string matches the passed expression. The pattern argument follows the rules of java.lang.String.matches method.
charAt(int) Returns the character at the passed position
length() Returns the length of the string
trim() Returns a trimmed version of the string
concat(String) Concatenates the current string and the passed string
equals(String) Returns if the strings are equal
equalsIgnoreCase(String) Returns if the strings are equal ignoring case
replaceAll(String, String) Returns the string with all instances of str1 replaced by str2
trimLeft() Returns a trimmed version of the string (trimmed for leading spaces). Only on RDBMS
trimRight() Returns a trimmed version of the string (trimmed for trailing spaces) Only on RDBMS

Here's an example using a Product class, looking for objects which their abbreviation is the beginning of a trade name. The trade name is provided as parameter.

Declarative JDOQL :
Query query = pm.newQuery(mydomain.Product.class);
query.setFilter(":tradeName.startsWith(this.abbreviation)");
List results = (List)query.execute("Workbook Advanced");

Single-String JDOQL :
Query query = pm.newQuery(
    "SELECT FROM mydomain.Product " +
    "WHERE :tradeName.startsWith(this.abbreviation)");
List results = (List)query.execute("Workbook Advanced");

Collection Methods

Method Description Standard In-Memory
isEmpty() Returns whether the collection is empty
contains(value) Returns whether the collection contains the passed element
size() Returns the number of elements in the collection
get(int) Returns the element at that position of the List

Here's an example demonstrating use of contains(). We have an Inventory class that has a Collection of Product objects, and we want to find the Inventory objects with 2 particular Products in it. Here we make use of a variable (prd to represent the Product being contained

Declarative JDOQL :
Query query = pm.newQuery(mydomain.Inventory.class);
query.setFilter("products.contains(prd) && (prd.name=='product 1' || prd.name=='product 2')");
List results = (List)query.execute();

Single-String JDOQL:
Query query = pm.newQuery(
    "SELECT FROM mydomain.Inventory " + 
    "WHERE products.contains(prd) && (prd.name=='product 1' || prd.name=='product 2')");
List results = (List)query.execute();

Map Methods

Method Description Standard In-Memory
isEmpty() Returns whether the map is empty
containsKey(key) Returns whether the map contains the passed key
containsValue(value) Returns whether the map contains the passed value
get(key) Returns the value from the map with the passed key
size() Returns the number of entries in the map
containsEntry(key, value) Returns whether the map contains the passed entry

Here's an example using a Product class as a value in a Map. Our example represents an organisation that has several Inventories of products. Each Inventory of products is stored using a Map, keyed by the Product name. The query searches for all Inventories that contain a product with the name "product 1".

Declarative JDOQL :
Query query = pm.newQuery(mydomain.Inventory.class, "products.containsKey('product 1')");
List results = (List)query.execute();

Single-String JDOQL :
Query query = pm.newQuery(
    "SELECT FROM mydomain.Inventory " +
    "WHERE products.containsKey('product 1')");
List results = (List)query.execute();

Here's the source code for reference

class Inventory
{
    Map<String, Product> products;
    ...
}
class Product
{
    ...
}

java.util.Date Temporal Methods

Method Description Standard In-Memory
getDate() Returns the day (of the month) for the date
getMonth() Returns the month for the date
getYear() Returns the year for the date
getHour() Returns the hour for the time
getMinute() Returns the minute for the time
getSecond() Returns the second for the time

java.time Temporal Methods

Method Description Standard In-Memory
getDayOfMonth() Returns the day (of the month) for the date (java.time.LocalXXX types)
getMonthValue() Returns the month for the date (java.time.LocalXXX types)
getYear() Returns the year for the date (java.util.Date types)
getHour() Returns the hour for the time (java.time.LocalXXX types)
getMinute() Returns the minute for the time (java.time.LocalXXX types)
getSecond() Returns the second for the time (java.time.LocalXXX types)

Jodatime Temporal Methods

Method Description Standard In-Memory
getStart() Returns the start of an org.joda.time.Interval
getEnd() Returns the end of an org.joda.time.Interval

Enum Methods

Method Description Standard In-Memory
ordinal() Returns the ordinal of the enum (not implemented for enum expression when persisted as a string)
toString() Returns the string form of the enum (not implemented for enum expression when persisted as a numeric)

Other Methods

Class Method Description Standard In-Memory
java.awt.Point getX() Returns the X coordinate. Only on RDBMS
java.awt.Point getY() Returns the Y coordinate. Only on RDBMS
java.awt.Rectangle getX() Returns the X coordinate. Only on RDBMS
java.awt.Rectangle getY() Returns the Y coordinate. Only on RDBMS
java.awt.Rectangle getWidth() Returns the width. Only on RDBMS
java.awt.Rectangle getHeight() Returns the height. Only on RDBMS
{} length Returns the length of an array. Only on RDBMS
{} contains(object) Returns true if the array contains the object. Only on RDBMS

Static Methods

Method Description Standard In-Memory
Math.abs(number) Returns the absolute value of the passed number
Math.sqrt(number) Returns the square root of the passed number
Math.cos(number) Returns the cosine of the passed number
Math.sin(number) Returns the absolute value of the passed number
Math.tan(number) Returns the tangent of the passed number
Math.acos(number) Returns the arc cosine of the passed number
Math.asin(number) Returns the arc sine of the passed number
Math.atan(number) Returns the arc tangent of the passed number
Math.ceil(number) Returns the ceiling of the passed number
Math.exp(number) Returns the exponent of the passed number
Math.floor(number) Returns the floor of the passed number
Math.log(number) Returns the log(base e) of the passed number
Math.toDegrees(number) Returns the degrees of the passed radians value
Math.toRadians(number) Returns the radians of the passed degrees value
JDOHelper.getObjectId(object) Returns the object identity of the passed persistent object
JDOHelper.getVersion(object) Returns the version of the passed persistent object
SQL_rollup({object}) Perform a rollup operation over the results. Only for some RDBMS e.g DB2, MSSQL, Oracle
SQL_cube({object}) Perform a cube operation over the results. Only for some RDBMS e.g DB2, MSSQL, Oracle
SQL_boolean({sql}) Embed the provided SQL and return a boolean result. Only on RDBMS
SQL_numeric({sql}) Embed the provided SQL and return a numeric result. Only on RDBMS

Literals

JDOQL supports literals of the following types : Number, boolean, character, String, and null. When String literals are specified using single-string format they should be surrounded by single-quotes '.


Parameters

With a query you can pass values into the query as parameters. This is useful where you don't want to embed particular values in the query itself, so making it reusable with different values. JDOQL allows two types of parameters.

Explicit Parameters

If you declare the parameters when defining the query (using the PARAMETERS keyword in the single-string form, or via the declareParameters method) then these are explicit parameters. This sets the type of the parameter, and when you pass the value in at execute it has to be of that type. For example

Query query = pm.newQuery(
    "SELECT FROM mydomain.Product WHERE price < limit PARAMETERS double limit");
List results = (List)query.execute(150.00);

Note that if declaring multiple parameters then they should be comma-separated.

Implicit Parameters

If you don't declare the parameters when defining the query but instead prefix identifiers in the query with : (colon) then these are implicit parameters. For example

Query query = pm.newQuery(
    "SELECT FROM mydomain.Product WHERE price < :limit");
List results = (List)query.execute(150.00);

In some situations you may have a map of parameters keyed by their name, yet the query in question doesn't need all parameters. Normal JDO execution would throw an exception here since they are inconsistent with the query. You can omit this check by setting

q.addExtension("datanucleus.query.checkUnusedParameters", "false");

Variables

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 mydomain.Supplier " +
    "WHERE this.products.contains(prod) && prod.name == 'Beans' VARIABLES mydomain.Product prod");

So we have a variable in our query called prod that connects the two parts. You can declare your variables (using the VARIABLES keyword in the single-string form, or via the declareVariables method) if you want to define the type like here (explicit), or you can leave them for the query compilation to determine (implicit).

Another example, in this case our candidate (Product) has no relation, but a class (Inventory) has a relation (1-N) to it (field "products") and we want to query based on that relation, returning the product name for a particular inventory.

Query q = pm.newQuery("SELECT this.name FROM mydomain.Product WHERE inv.products.contains(this) AND inv.name == 'Sale' VARIABLES mydomain.Inventory inv");

Note that if declaring multiple variables then they should be semicolon-separated. See also this blog post which demonstrates variables across 1-1 "relations" where you only have the "id" stored rather than a real relation.


Imports

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


IF ELSE expressions

For particular use in the result clause, you can make use of a IF ELSE expression where you want to return different things based on some condition(s). Like this

SELECT p.personNum, IF (p.age < 18) 'Youth' ELSE IF (p.age >= 18 && p.age < 65) 'Adult' ELSE 'Old' FROM mydomain.Person p");

So in this case the second result value will be a String, either "Youth", "Adult" or "Old" depending on the age of the person. Note that this is new in JDO3.1. The BNF structure of the JDOQL IF ELSE expression is

IF (conditional_expression) scalar_expression {ELSE IF (conditional_expression) scalar_expression}* ELSE scalar_expression
                

Operators

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.

instanceof

JDOQL allows the Java keyword instanceof so you can compare objects against a class.

Let's take an example. 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(A.class);
query.setFilter("b instanceof mydomain.B2");
List results = (List)query.execute();

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

casting

JDOQL allows use of Java-style casting so you can type-convert fields etc.

Let's take an example. We have a class A that has a field "b" of type B and B has subclasses B1, B2, B3. The B2 subtype has a field "other", and we know that the filtered A will have a B2. You could specify a filter using the "B2.other" field like this

((mydomain.B2)b).other == :someVal"

Subqueries

With JDOQL the user has a very flexible query syntax which allows for querying of the vast majority of data components in a single query. In some situations it is desirable for the query to utilise the results of a separate query in its calculations. JDO allows subqueries, so that both calculations can be performed in one query. Here's an example, using single-string JDOQL

SELECT FROM org.datanucleus.Employee WHERE salary > 
    (SELECT avg(salary) FROM org.datanucleus.Employee e)

So we want to find all Employees that have a salary greater than the average salary. In single-string JDOQL the subquery must be in parentheses (brackets). Note that we have defined the subquery with an alias of "e", whereas in the outer query the alias is "this".

We can specify the same query using the Declarative API, like this

Query averageSalaryQuery = pm.newQuery(Employee.class);
averageSalaryQuery.setResult("avg(this.salary)");

Query q = pm.newQuery(Employee.class, "salary > averageSalary");
q.declareVariables("double averageSalary");
q.addSubquery(averageSalaryQuery, "double averageSalary", null, null);
List results = (List)q.execute();

So we define a subquery as its own Query (that could be executed just like any query if so desired), and the in the main query have an implicit variable that we define as being represented by the subquery.

Referring to the outer query in the subquery

JDOQL subqueries allows use of the outer query fields within the subquery if so desired. Taking the above example and extending it, here is how we do it in single-string JDOQL

SELECT FROM org.datanucleus.Employee WHERE salary > 
    (SELECT avg(salary) FROM org.datanucleus.Employee e WHERE e.lastName == this.lastName)

So with single-string JDOQL we make use of the alias identifier "this" to link back to the outer query.

Using the Declarative API, to achieve the same thing we would do

Query averageSalaryQuery = pm.newQuery(Employee.class);
averageSalaryQuery.setResult("avg(this.salary)");
averageSalaryQuery.setFilter("this.lastName == :lastNameParam");

Query q = pm.newQuery(Employee.class, "salary > averageSalary");
q.declareVariables("double averageSalary");
q.addSubquery(averageSalaryQuery, "double averageSalary", null, "this.lastName");
List results = (List)q.execute();

So with the Declarative API we make use of parameters, and the last argument to addSubquery is the value of the parameter lastNameParam.

Candidate of the subquery being part of the outer query

There are occasions where we want the candidate of the subquery to be part of the outer query, so JDOQL subqueries has the notion of a candidate expression. This is an expression relative to the candidate of the outer query. An example

SELECT FROM org.datanucleus.Employee WHERE this.weeklyhours > 
    (SELECT AVG(e.weeklyhours) FROM this.department.employees e)

so the candidate of the subquery is this.department.employees. If using a candidate expression we must provide an alias.

You can do the same with the Declarative API. Like this

Query averageHoursQuery = pm.newQuery(Employee.class);
averageHoursQuery.setResult("avg(this.weeklyhours)");

Query q = pm.newQuery(Employee.class);
q.setFilter("this.weeklyhours > averageWeeklyhours");
q.addSubquery(averageHoursQuery, "double averageWeeklyhours", "this.department.employees", null);
                

so now our subquery has a candidate related to the outer query candidate.

In strict JDOQL you can only have the subquery in the "filter" (WHERE) clause. DataNucleus additionally allows it in the "result" (SELECT) clause.


Result clause

By default (when not specifying the result) the objects returned will be of the candidate class type, where they match the query filter. The result clause can contain (any of) the following

  • DISTINCT - optional keyword at the start of the results to make them distinct
  • this - the candidate instance
  • A field name
  • A variable
  • A parameter (though why you would want a parameter returning is hard to see since you input the value in the first place)
  • An aggregate (count(), avg(), sum(), min(), max())
  • An expression involving a field (e.g "field1 + 1")
  • A navigational expression (navigating from one field to another ... e.g "field1.field4")

so you could specify something like

count(field1), field2

There are situations when you want to return a single number for a column, representing an aggregate of the values of all records. There are 5 standard JDO aggregate functions available. These are

  • avg(val) - returns the average of "val". "val" can be a field, numeric field expression or "distinct field". Returns double.
  • sum(val) - returns the sum of "val". "val" can be a field, numeric field expression, or "distinct field". Returns the same type as the type being summed
  • count(val) - returns the count of records of "val". "val" can be a field, or can be "this", or "distinct field". Returns long
  • min(val) - returns the minimum of "val". "val" can be a field. Returns the same type as the type used in "min"
  • max(val) - returns the maximum of "val". "val" can be a field. Returns the same type as the type used in "max"

So to utilise these you could specify a result like

max(price), min(price)

This will return a single row of results with 2 values, the maximum price and the minimum price.

Note that what you specify in the result defines what form of result you get back when executing the query.

  • {ResultClass} - this is returned if you have only a single row in the results and you specified a result class.
  • Object - this is returned if you have only a single row in the results and a single column. This is achived when you specified either UNIQUE, or just an aggregate (e.g "max(field2)")
  • Object[] - this is returned if you have only a single row in the results, but more than 1 column (e.g "max(field1), avg(field2)")
  • List<{ResultClass}> - this is returned if you specified a result class.
  • List<Object> - this is returned if you have only a single column in the result, and you don't have only aggregates in the result (e.g "field2")
  • List<Object[]> - this is returned if you have more than 1 column in the result, and you don't have only aggregates in the result (e.g "field2, avg(field3)")

Result Class

By default a JDOQL query will return a result matching the result clause. You can override this if you wish by specifying a result class. If your query has only a single row in the results then you will get an object of your result class back, otherwise you get a List of result class objects. The Result Class has to meet certain requirements. These are

  • Can be one of Integer, Long, Short, Float, Double, Character, Byte, Boolean, String, java.math.BigInteger, java.math.BigDecimal, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, or Object[]
  • Can be a user defined class, that has either a constructor taking arguments of the same type as those returned by the query (in the same order), or has a public put(Object, Object) method, or public setXXX() methods, or public fields.

In terms of how the Result Class looks, you have two options

  • Constructor taking arguments of the same types and the same order as the result clause. An instance of the result class is created using this constructor. For example
    public class Price
    {
        protected double amount = 0.0;
        protected String currency = null;
    
        public Price(double amount, String currency)
        {
            this.amount = amount;
            this.currency = currency;
        }
    
        ...
    }
  • Default constructor, and setters for the different result columns, using the alias name for each column as the property name of the setter. For example
    public class Price
    {
        protected double amount = 0.0;
        protected String currency = null;
    
        public Price()
        {
        }
    
        public void setAmount(double amt) {this.amount = amt;}
        public void setCurrency(String curr) {this.currency = curr;}
    
        ...
    }

Grouping of Results

By default your results will have no specified "grouping". You can specify a grouping with optional having expression. When grouping is specified, each result expression must either be an expression contained in the grouping, or an aggregate evaluated once per group.


Ordering of Results

By default your results will be returned in the order determined by the datastore, so don't rely on any particular order. You can, of course, specify the order yourself. You do this using field/property names and ASC/DESC keywords. For example

field1 ASC, field2 DESC

which will sort primarily by field1 in ascending order, then secondarily by field2 in descending order.

JDO3.1 introduces a directive for where NULL values of the ordered field/property go in the order, so the full syntax supported is

fieldName [ASC|DESC] [NULLS FIRST|NULLS LAST]

Note that this is only supported for a few RDBMS (H2, HSQLDB, PostgreSQL, DB2, Oracle, Derby, Firebird, SQLServer v11+).


Range of Results

By default your query will return all results matching the specified filter. You can select just a particular range of results by specifying the RANGE part of the query (or by using setRange when using the declarative API). For example

RANGE 10,20

which will return just the results numbers 10-19 inclusive. Obviously bear in mind that if specifying the range then you should really specify an ordering otherwise the range positions will be not defined.


JDOQL In-Memory queries

The typical use of a JDOQL query is to translate it into the native query language of the datastore and return objects matched by the query. Sometimes you want to query over a set of objects that you have to hand, or for some datastores it is simply impossible to support the full JDOQL syntax in the datastore native query language. In these situation we need to evaluate the query in-memory. In the latter case of the datastore not supported the full JDOQL syntax we evaluate as much as we can in the datastore and then instantiate those objects and evaluate further in-memory. Here we document the current capabilities of in-memory evaluation in DataNucleus.

To enable evaluation in memory you specify the query extension datanucleus.query.evaluateInMemory to true as follows

query.addExtension("datanucleus.query.evaluateInMemory","true");

This is also useful where you have a Collection of (persisted) objects and want to run a query over the Collection. Simply turn on in-memory evaluation, and supply the candidate collection to the query, and no communication with the datastore will be needed.

Specify candidates to query over

With JDO you can define a set of candidate objects that should be queried, rather than just going to the datastore to retrieve those objects. When you specify this you will automatically be switched to evaluate the query in-memory. You set the candidates like this

Query query = pm.newQuery(...);
query.setCandidates(myCandidates);
List<Product> results = (List<Product>)query.execute();

Update/Delete queries

JDOQL offers some possibilities for updating/deleting data in the datastore via query. Note that only the first of these is standard JDOQL, whereas the others are DataNucleus extensions.


Deletion by Query

If you want to delete instances of a candidate using a query, you simply define the query candidate/filter in the normal way, and then instead of calling query.execute() you call query.deletePersistentAll(). Like this

Query query = pm.newQuery("SELECT FROM mydomain.A WHERE this.value < 50");
Long number = (Long)query.deletePersistentAll();

The value returned is the number of instances that were deleted. Note that this will perform any cascade deletes that are defined for these instances. In addition, all instances in memory will reflect this deletion.


Bulk Delete

DataNucleus provides an extension to allow bulk deletion. This differs from the "Deletion by Query" above in that it simply goes straight to the datastore and performs a bulk delete, leaving it to the datastore referential integrity to handle relationships. To enable "bulk delete" you need the persistence property datanucleus.query.jdoql.allowAll set to true. You then perform "bulk delete" like this

Query query = pm.newQuery("DELETE FROM mydomain.A WHERE this.value < 50");
Long number = (Long)query.execute();

Again, the number returned is the number of records deleted.


Bulk Update

DataNucleus provides an extension to allow bulk update. This allows you to do bulk updates direct to the datastore without having to load objects into memory etc. To enable "bulk update" you need the persistence property datanucleus.query.jdoql.allowAll set to true. You then perform "bulk update" like this

Query query = pm.newQuery("UPDATE mydomain.A SET this.value=this.value-5.0 WHERE this.value > 100");
Long number = (Long)query.execute();

Again, the number returned is the number of records updated.