JDO : JPQL Queries

JDO provides a flexible API for use of query languages. DataNucleus makes use of this to allow use
of the query language defined in the JPA1 specification (JPQL) with JDO persistence.
JPQL is a pseudo-OO language based around SQL, and so not using Java syntax, unlike JDOQL.
To provide a simple example, this is what you would do

The "keywords" in the query are shown in UPPER CASE are case-insensitive.

Entity Name

In the example shown you note that we did not specify the full class name. We used Person p
and thereafter could refer to p as the alias. The Person is called the
entity name and in JPA MetaData this can be defined against each class in its definition.
With JDO we don't have this MetaData attribute so we simply define the entity name as
the name of the class omitting the package name. So org.datanucleus.test.samples.Person
will have an entity name of Person.

In strict JPA the entity name cannot be a MappedSuperclass entity name. That is, if
you have an abstract superclass that is persistable, you cannot query for instances of that
superclass and its subclasses. We consider this a significant shortcoming of the querying
capability, and allow the entity name to also be of a MappedSuperclass. You are unlikely
to find this supported in other JPA implementations, but then maybe that's why you chose
DataNucleus?

Unique Results

When you know that there will be only a single result, you can set the query as unique.
This simplifies the process of getting the result

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.

So in our example, we are returning 2 String fields, and we define our
Result ClassName as follows

So here we have a result class using the constructor arguments. We could equally have provided
a class with public fields instead, or provided setXXX methods or a put method.
They all work in the same way.

Fetched Fields

By default a query will fetch fields according to their defined EAGER/LAZY setting, so fields
like primitives, wrappers, Dates, and 1-1/N-1 relations will be fetched, whereas 1-N/M-N fields
will not be fetched. JPQL allows you to include FETCH JOIN as a hint to include
1-N/M-N fields where possible. For RDBMS datastores any multi-valued field will not be fetched
even if you specify FETCH JOIN, due to the complications in doing so. All non-RDBMS datastores
do however respect this FETCH JOIN setting, since a collection/map is stored in a single "column"
in the object and so is readily retrievable.

Note that you can also make use of Fetch Groups to have fuller
control over what is retrieved from each query.

Fields/Properties

In JPQL you refer to fields/properties in the query by referring to the field/bean name.
For example, if you are querying a candidate entity 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 an entity
you can prefix the field by its alias

p.price < 150.0

You can also chain field references if you have an entity Product (alias = p) with a field of
(persistable) type Inventory, which has a field name, so you could do

Literals

JPQL supports the following literals: IntegerLiteral, FloatingPointLiteral, BooleanLiteral,
CharacterLiteral, StringLiteral, and NullLiteral. When String literals are specified using
single-string format they should be surrounded by single-quotes '.

Input Parameters

In JPQL queries it is convenient to pass in parameters so we dont have to define the same query
for different values. Let's take two examples

So in the first case we have parameters that are prefixed by : (colon) to identify them as
a parameter and we use that name in the parameter map passed to execute().
In the second case we have parameters that are prefixed by ? (question mark) and are
numbered starting at 1. We then pass the parameters in to execute in that order.

JPQL Functions

JPQL provides an SQL-like query language. Just as with SQL, JPQL also supports a range of
functions to enhance the querying possibilities. 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 functions for evaluation "in-memory" using this
DataNucleus plugin point

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

Aggregate Functions

There are a series of aggregate functions for aggregating the values of a field
for all rows of the results.

Function Name

Description

Standard

In-Memory

COUNT(field)

Returns the aggregate count of the field (Long)

MIN(field)

Returns the minimum value of the field (type of the field)

MAX(field)

Returns the maximum value of the field (type of the field)

AVG(field)

Returns the average value of the field (Double)

SUM(field)

Returns the sum of the field value(s) (Long, Double, BigInteger, BigDecimal)

String Functions

There are a series of functions to be applied to String fields.

Function Name

Description

Standard

In-Memory

CONCAT(str_field, str_field2 [, str_fieldX])

Returns the concatenation of the string fields

SUBSTRING(str_field, num1 [, num2])

Returns the substring of the string field starting at position num1, and optionally
with the length of num2

TRIM([trim_spec] [trim_char] [FROM] str_field)

Returns trimmed form of the string field

LOWER(str_field)

Returns the lower case form of the string field

UPPER(str_field)

Returns the upper case form of the string field

LENGTH(str_field)

Returns the size of the string field (number of characters)

LOCATE(str_field1, str_field2 [, num])

Returns position of str_field2 in str_field1 optionally
starting at num

Temporal Functions

There are a series of functions for use with temporal values

Function Name

Description

Standard

In-Memory

CURRENT_DATE

Returns the current date (day month year) of the datastore server

CURRENT_TIME

Returns the current time (hour minute second) of the datastore server

CURRENT_TIMESTAMP

Returns the current timestamp of the datastore server

YEAR(dateField)

Returns the year of the specified date

MONTH(dateField)

Returns the month of the specified date

DAY(dateField)

Returns the day of the month of the specified date

HOUR(dateField)

Returns the hour of the specified date

MINUTE(dateField)

Returns the minute of the specified date

SECOND(dateField)

Returns the second of the specified date

Collection Functions

There are a series of functions for use with collection values

Function Name

Description

Standard

In-Memory

INDEX(field)

Returns index number of the field element when that is the element of an indexed
List field.

SIZE(collection_field)

Returns the size of the collection field. Empty collection will return 0

Arithmetic Functions

There are a series of functions for arithmetic use

Function Name

Description

Standard

In-Memory

ABS(numeric_field)

Returns the absolute value of the numeric field

SQRT(numeric_field)

Returns the square root of the numeric field

MOD(num_field1, num_field2)

Returns the modulus of the two numeric fields (num_field1 % num_field2

Other Functions

You have a further function available

Function Name

Description

Standard

In-Memory

FUNCTION(name, [arg1 [,arg2 ...]])

Executes the specified SQL function "name" with the defined arguments

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.

Although it is not (yet) standard JPQL, DataNucleus also supports specifying 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).

Subqueries

With JPQL 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. JPQL also allows the use of subqueries.
Here's an example

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

ALL/ANY Expressions

One use of subqueries with JPQL is where you want to compare with some or all of a
particular expression. To give an example

Range of Results

With JPQL you can select the range of results to be returned. For example if you have a web page and you are paginating
the results of some search, you may want to get the results from a query in blocks of 20 say, with results
0 to 19 on the first page, then 20 to 39, etc. You can facilitate this as follows

Note that if the setter property name doesn't match the query result component name, you should
use AS {alias} in the query so they are the same.

JPQL In-Memory queries

The typical use of a JPQL query is to translate it into the native query language of the
datastore and return objects matched by the query. For many datastores it is simply impossible
to support the full JPQL syntax in the datastore native query language and so it is
necessary to evaluate the query in-memory. This means that 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.

Subqueries using ALL, ANY, SOME, EXISTS are not currently supported

MEMBER OF syntax is not currently supported.

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

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

JPQL DELETE Queries

The JPA specification defines a mode of JPQL for deleting objects from the datastore.

DELETE Syntax

The syntax for deleting records is very similar to selecting them

DELETE FROM [<candidate-class>]
[WHERE <filter>]

The "keywords" in the query are shown in UPPER CASE are case-insensitive.