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

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>


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 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 DELETE Queries

The JPA specification defines a mode of JPQL for deleting objects from the datastore. This is supported for all datastores except for RDBMS with the legacy implementation.

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. This is supported for RDBMS, but is not yet implemented for other datastores.

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.