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?

From Clause

The FROM clause allows a user to add some explicit joins to related entities, and assign aliases to the joined entities.
These are then usable in the filter/ordering/result etc. If you don't add any joins DataNucleus will add joins where they are
implicit from the filter expression for example. The FROM clause is of the following structure

So you are explicitly stating that the join across join_spec is performed as "LEFT OUTER" or "INNER" (rather than just leaving it to DataNucleus
to decide which to use). Note that the join_spec can be a relation field, or alternately if you have a Map of non-Entity keys/values then also
the Map field. If you provide the join_alias then you can use it thereafter in other clauses of the query.

Some examples of FROM clauses.

Join across 2 relations, allowing referral to Address (a) and Owner (o)
SELECT p FROM Person p JOIN p.address a JOIN a.owner o WHERE o.name = 'Fred'
Join to a Map relation field and access to the key/value of the Map.
SELECT VALUE(om) FROM Company c INNER JOIN c.officeMap om ON KEY(om) = 'London'

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.

CASE expressions

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

So in this case the second result value will be a String, either "Youth", "Adult" or "Old" depending on the age of the person.
The BNF structure of the JPQL CASE expression is

CASE WHEN conditional_expression THEN scalar_expression {WHEN conditional_expression THEN scalar_expression}* ELSE scalar_expression END

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(collection_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

Map Functions

There are a series of functions for use with maps

Function Name

Description

Standard

In-Memory

KEY(map_field)

Returns the key of the map

VALUE(map_field)

Returns the value of the map

SIZE(map_field)

Returns the size of the map field. Empty map 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)

ACOS(num_field)

Returns the arc-cosine of a numeric field

ASIN(num_field)

Returns the arc-sine of a numeric field

ATAN(num_field)

Returns the arc-tangent of a numeric field

COS(num_field)

Returns the cosine of a numeric field

SIN(num_field)

Returns the sine of a numeric field

TAN(num_field)

Returns the tangent of a numeric field

DEGREES(num_field)

Returns the degrees of a numeric field

RADIANS(num_field)

Returns the radians of a numeric field

CEIL(num_field)

Returns the ceiling of a numeric field

FLOOR(num_field)

Returns the floor of a numeric field

LOG(num_field)

Returns the natural logarithm of a numeric field

EXP(num_field)

Returns the exponent of a numeric field

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

Collection Fields

Where you have a collection field, often you want to navigate it to query based on some filter for the element. To achieve this, you can clearly
JOIN to the element in the FROM clause. Alternatively you can use the MEMBER OF keyword. Let's take an example, you have
a field which is a Collection of Strings, and want to return the owner object that has an element that is "Freddie".

Beyond this, you can also make use of the Collection functions and use the size of the collection for example.

Map Fields

Where you have a map field, often you want to navigate it to query based on some filter for the key or value.
Let's take an example, you want to return the value for a particular key in the map of an owner.

Beyond this, you can also make use of the Map functions and use the size of the map for example.

Note that in the JPA spec they allow a user to interchangeably use "p.addresses" to refer to the value of the Map.
DataNucleus doesn't support that since that primary expression is a Map field, and the Map can equally be represented as a join table,
key stored in value, or value stored in key. Hence you should always use VALUE(...) if you mean to refer to the Map value - besides
it is a damn sight clearer the intent by doing that.

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

Specify candidates to query over

With JPA you always query objects of the candidate type in the datastore. DataNucleus extends this and allows you to provide
a Collection of candidate objects that will be queried (rather than going to the datastore), and it will perform the querying
"in-memory". You set the candidates like this

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

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.

Query Result

Whilst the majority of the time you will want to return instances of a candidate class, JPQL also
allows you to return customised results. Consider the following example

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.