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

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

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 be bulk-fetched if it is defined to be EAGER
or is placed in the current EntityGraph. By bulk-fetched we mean that there will be a single SQL
issued per collection field (hence avoiding the N+1 problem). Note that you can disable this by either not putting
multi-valued fields in the FetchPlan, or by setting the query extension "datanucleus.multivaluedFetch" to "none"
(default is "bulk-fetch" using the single SQL per field).
All non-RDBMS datastores do 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 Entity Graphs 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 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 literals of the following types : Number, boolean, character, String, NULL and temporal.
When String literals are specified using single-string format they should be surrounded by single-quotes '.
Please note that temporal literals are specified using JDBC escape syntax in String form, namely

So in the first case we have parameters that are prefixed by : (colon) to identify them as
a parameter and we use that name when calling Query.setParameter().
In the second case we have parameters that are prefixed by ? (question mark) and are
numbered starting at 1. We then use the numbered position when calling Query.setParameter().

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(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, Firebird, SQLServer v11+).

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.

Query Execution

There are two ways to execute a JPQL query. When you know it will return 0 or 1 results you call

Object result = query.getSingleResult();

If however you know that the query will return multiple results, or you just don't know then you would call

List results = query.getResultList();

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");

Named Query

With the JPA API you can either define a query at runtime, or define it in the MetaData/annotations
for a class and refer to it at runtime using a symbolic name. This second option means that the
method of invoking the query at runtime is much simplified. To demonstrate the process, lets say we
have a class called Product (something to sell in a store). We define the JPA Meta-Data for
the class in the normal way, but we also have some query that we know we will require, so we
define the following in the Meta-Data.

Note that DataNucleus also supports specifying this using annotations in non-Entity classes.
This is beyond the JPA spec, but is very useful in real applications

So we have a JPQL query called "SoldOut" defined for the class Product that returns all
Products (and subclasses) that have a status of "Sold Out". Out of interest, what we would
then do in our application to execute this query woule be