JPA : JPQL SELECT Queries

The JPA specification defines JPQL (a pseudo-OO query language, with SQL-like syntax), for selecting objects from the datastore. To provide a simple example, this is what you would do

Query q = em.createQuery("SELECT p FROM Person p WHERE p.lastName = 'Jones'");
List results = (List)q.getResultList();

This finds all "Person" objects with surname of "Jones". You specify all details in the query.



SELECT Syntax

In JPQL queries you define the query in a single string, defining the result, the candidate class(es), the filter, any grouping, and the ordering. This string has to follow the following pattern

SELECT [<result>]
    [FROM <candidate-class(es)>]
    [WHERE <filter>]
    [GROUP BY <grouping>]
    [HAVING <having>]
    [ORDER BY <ordering>]

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

<entity class="org.datanucleus.company.Person" name="Person">
    ...
</entity>

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?



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

Named Parameters :
Query q = em.createQuery("SELECT p FROM Person p WHERE p.lastName = :surname AND o.firstName = :forename");
q.setParameter("surname", theSurname);
q.setParameter("forename", theForename");

Numbered Parameters :
Query q = em.createQuery("SELECT p FROM Person p WHERE p.lastName = ?1 AND p.firstName = ?2");
q.setParameter(1, theSurname);
q.setParameter(2, theForename);

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



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

Query q = em.createQuery("SELECT p FROM Person p WHERE p.age &gt; 20");
q.setFirstResult(0);
q.setMaxResults(20);

So with this query we get results 0 to 19 inclusive.



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

Query q = em.createQuery("SELECT p.firstName, p.lastName FROM Person p WHERE p.age &gt; 20");
List<Object[]> results = q.getResultList();

this returns the first and last name for each Person meeting that filter. Obviously we may have some container class that we would like the results returned in, so if we change the query to this

Query<PersonName> q = em.createQuery(
    "SELECT p.firstName, p.lastName FROM Person p WHERE p.age &gt; 20", PersonName.class);
List<PersonName> results = q.getResultList();

so each result is a PersonName, holding the first and last name. This result class needs to match one of the following structures

  • 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 PersonName
    {
        protected String firstName = null;
        protected String lastName = null;
    
        public PersonName(String first, String last)
        {
            this.firstName = first;
            this.lastName = last;
        }
    
        ...
    }
  • 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 PersonName
    {
        protected String firstName = null;
        protected String lastName = null;
    
        public PersonName()
        {
        }
    
        public void setFirstName(String first) {this.firstName = first;}
        public void setLastName(String last) {this.lastName = last;}
    
        ...
    }

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


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.

    <entity class="Product">
        ...
        <named-query name="SoldOut"><![CDATA[
        SELECT p FROM Product p WHERE p.status == "Sold Out"
        ]]></named-query>
    </entity>

or using annotations

@Entity
@NamedQuery(name="SoldOut", query="SELECT p FROM Product p WHERE p.status == 'Sold Out'")
public class Product {...}

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

Query query = em.createNamedQuery("SoldOut");
List<Product> results = query.getResultList();


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.



JPQL UPDATE Queries

The JPA specification defines a mode of JPQL for updating objects in the datastore.

UPDATE Syntax

The syntax for updating records is very similar to selecting them

UPDATE [<candidate-class>] SET item1=value1, item2=value2
    [WHERE <filter>]

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



JPQL BNF

The BNF defining the JPQL query language is shown below.

QL_statement ::= select_statement | update_statement | delete_statement
select_statement ::= select_clause from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]

update_statement ::= update_clause [where_clause]

delete_statement ::= delete_clause [where_clause]

from_clause ::= FROM identification_variable_declaration
    {, {identification_variable_declaration | collection_member_declaration}}*
identification_variable_declaration ::= range_variable_declaration { join | fetch_join }*
range_variable_declaration ::= entity_name [AS] identification_variable

join ::= join_spec join_association_path_expression [AS] identification_variable
fetch_join ::= join_spec FETCH join_association_path_expression
join_spec::= [ LEFT [OUTER] | INNER ] JOIN
join_association_path_expression ::= join_collection_valued_path_expression | join_single_valued_path_expression
join_collection_valued_path_expression::=
    identification_variable.{single_valued_embeddable_object_field.}*collection_valued_field
join_single_valued_path_expression::=
    identification_variable.{single_valued_embeddable_object_field.}*single_valued_object_field
collection_member_declaration ::=
    IN (collection_valued_path_expression) [AS] identification_variable
qualified_identification_variable ::= KEY(identification_variable) | VALUE(identification_variable) |
    ENTRY(identification_variable)
single_valued_path_expression ::= qualified_identification_variable |
    state_field_path_expression | single_valued_object_path_expression
general_identification_variable ::= identification_variable | KEY(identification_variable) | 
    VALUE(identification_variable)

state_field_path_expression ::= general_identification_variable.{single_valued_object_field.}*state_field
single_valued_object_path_expression ::=
    general_identification_variable.{single_valued_object_field.}* single_valued_object_field
collection_valued_path_expression ::=
    general_identification_variable.{single_valued_object_field.}*collection_valued_field

update_clause ::= UPDATE entity_name [[AS] identification_variable] SET update_item {, update_item}*
update_item ::= [identification_variable.]{state_field | single_valued_object_field} = new_value
new_value ::= scalar_expression | simple_entity_expression | NULL

delete_clause ::= DELETE FROM entity_name [[AS] identification_variable]

select_clause ::= SELECT [DISTINCT] select_item {, select_item}*
select_item ::= select_expression [[AS] result_variable]
select_expression ::= single_valued_path_expression | scalar_expression | aggregate_expression | 
    identification_variable | OBJECT(identification_variable) | constructor_expression
constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}* )
constructor_item ::= single_valued_path_expression | scalar_expression | aggregate_expression |
    identification_variable

aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) |
    COUNT ([DISTINCT] identification_variable | state_field_path_expression |
    single_valued_object_path_expression)

where_clause ::= WHERE conditional_expression
groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
groupby_item ::= single_valued_path_expression | identification_variable
having_clause ::= HAVING conditional_expression
orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::= state_field_path_expression | result_variable [ ASC | DESC ]

subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]
subquery_from_clause ::= FROM subselect_identification_variable_declaration
    {, subselect_identification_variable_declaration | collection_member_declaration}*

subselect_identification_variable_declaration ::= identification_variable_declaration |
    derived_path_expression [AS] identification_variable {join}*|
    derived_collection_member_declaration
derived_path_expression ::=
    superquery_identification_variable.{single_valued_object_field.}*collection_valued_field |
    superquery_identification_variable.{single_valued_object_field.}*single_valued_object_field
derived_collection_member_declaration ::=
    IN superquery_identification_variable.{single_valued_object_field.}*collection_valued_field
simple_select_clause ::= SELECT [DISTINCT] simple_select_expression
simple_select_expression::= single_valued_path_expression | scalar_expression | aggregate_expression |
    identification_variable
scalar_expression ::= simple_arithmetic_expression | string_primary | enum_primary |
    datetime_primary | boolean_primary | case_expression | entity_type_expression
conditional_expression ::= conditional_term | conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND conditional_factor
conditional_factor ::= [ NOT ] conditional_primary
conditional_primary ::= simple_cond_expression | (conditional_expression)
simple_cond_expression ::= comparison_expression | between_expression |
    in_expression | like_expression | null_comparison_expression | 
    empty_collection_comparison_expression | collection_member_expression | exists_expression
between_expression ::= 
    arithmetic_expression [NOT] BETWEEN arithmetic_expression AND arithmetic_expression |
    string_expression [NOT] BETWEEN string_expression AND string_expression |
    datetime_expression [NOT] BETWEEN datetime_expression AND datetime_expression
in_expression ::= {state_field_path_expression | type_discriminator} [NOT] IN
    { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }
in_item ::= literal | single_valued_input_parameter
like_expression ::= string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
null_comparison_expression ::= {single_valued_path_expression | input_parameter} IS [NOT] NULL

empty_collection_comparison_expression ::= collection_valued_path_expression IS [NOT] EMPTY
collection_member_expression ::= entity_or_value_expression [NOT] MEMBER [OF] collection_valued_path_expression
entity_or_value_expression ::= single_valued_object_path_expression | state_field_path_expression |
    simple_entity_or_value_expression
simple_entity_or_value_expression ::= identification_variable | input_parameter | literal
exists_expression::= [NOT] EXISTS (subquery)
all_or_any_expression ::= { ALL | ANY | SOME} (subquery)
comparison_expression ::=
    string_expression comparison_operator {string_expression | all_or_any_expression} |
    boolean_expression { =|<>} {boolean_expression | all_or_any_expression} |
    enum_expression { =|<>} {enum_expression | all_or_any_expression} |
    datetime_expression comparison_operator
    {datetime_expression | all_or_any_expression} |
    entity_expression { = | <>} {entity_expression | all_or_any_expression} |
    arithmetic_expression comparison_operator
    {arithmetic_expression | all_or_any_expression} |
    entity_type_expression { =|<>} entity_type_expression}
comparison_operator ::= = | > | >= | < | <= | <>
arithmetic_expression ::= simple_arithmetic_expression | (subquery)
simple_arithmetic_expression ::= arithmetic_term | simple_arithmetic_expression { + | - } arithmetic_term
arithmetic_term ::= arithmetic_factor | arithmetic_term { * | / } arithmetic_factor
arithmetic_factor ::= [{ + | - }] arithmetic_primary
arithmetic_primary ::= state_field_path_expression | numeric_literal |
    (simple_arithmetic_expression) | input_parameter | functions_returning_numerics |
     aggregate_expression | case_expression
string_expression ::= string_primary | (subquery)
string_primary ::= state_field_path_expression | string_literal |
    input_parameter | functions_returning_strings | aggregate_expression | case_expression
datetime_expression ::= datetime_primary | (subquery)

datetime_primary ::= state_field_path_expression | input_parameter | functions_returning_datetime |
    aggregate_expression | case_expression | date_time_timestamp_literal
boolean_expression ::= boolean_primary | (subquery)
boolean_primary ::= state_field_path_expression | boolean_literal | input_parameter |
    case_expression
enum_expression ::= enum_primary | (subquery)
enum_primary ::= state_field_path_expression | enum_literal | input_parameter | case_expression
entity_expression ::= single_valued_object_path_expression | simple_entity_expression
simple_entity_expression ::= identification_variable | input_parameter
entity_type_expression ::= type_discriminator | entity_type_literal | input_parameter
type_discriminator ::= TYPE(identification_variable | single_valued_object_path_expression |
    input_parameter)
functions_returning_numerics::= LENGTH(string_primary) |
    LOCATE(string_primary, string_primary[, simple_arithmetic_expression]) |
    ABS(simple_arithmetic_expression) |
    SQRT(simple_arithmetic_expression) |
    MOD(simple_arithmetic_expression, simple_arithmetic_expression) |
    SIZE(collection_valued_path_expression) |
    INDEX(identification_variable)
functions_returning_datetime ::= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP

functions_returning_strings ::=
    CONCAT(string_primary, string_primary {, string_primary}*) |
    SUBSTRING(string_primary, simple_arithmetic_expression [, simple_arithmetic_expression]) |
    TRIM([[trim_specification] [trim_character] FROM] string_primary) |
    LOWER(string_primary) |
    UPPER(string_primary)
trim_specification ::= LEADING | TRAILING | BOTH
case_expression ::= general_case_expression | simple_case_expression | coalesce_expression |
    nullif_expression
general_case_expression::= CASE when_clause {when_clause}* ELSE scalar_expression END
when_clause::= WHEN conditional_expression THEN scalar_expression
simple_case_expression::= 
    CASE case_operand simple_when_clause {simple_when_clause}*
    ELSE scalar_expression
    END
case_operand::= state_field_path_expression | type_discriminator
simple_when_clause::= WHEN scalar_expression THEN scalar_expression
coalesce_expression::= COALESCE(scalar_expression {, scalar_expression}+)
nullif_expression::= NULLIF(scalar_expression, scalar_expression)