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

Query q = pm.newQuery("javax.jdo.query.JPQL", "SELECT p FROM Person p WHERE p.lastName = 'Jones'");
List results = (List)q.execute();

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



JPQL Syntax

In traditional (declarative) JDOQL (JDO 1.0) it was necessary to specify the component parts (filter, candidate class, ordering, etc) of the query using the mutator methods on the Query. In JDO 2 you can now specify it all in a single string. This string has to follow a particular pattern, but provides the convenience that many people have been asking for. The pattern to use is as follows

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. With JDO we dont 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 .

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

Query query = pm.newQuery("javax.jdo.query.JPQL", 
    "SELECT p FROM Person p WHERE p.lastName = 'Obama' AND o.firstName = 'Barak'");
query.setUnique(true);
Person pers = (Person) query.execute();


Result Class

If you are defining the result of the JPQL query and want to obtain each row of the results as an object of a particular type, then you can set the result class.

Query query = pm.newQuery("javax.jdo.query.JPQL", 
                "SELECT p.firstName, p.lastName FROM Person p");
query.setResultClass(Name.class);
List<Name> names = (List<Name>) query.execute();

The Result Class has to meet certain requirements. These are

  • Can be one of Integer, Long, Short, Float, Double, Character, Byte, Boolean, String, java.math.BigInteger, java.math.BigDecimal, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, or Object[]
  • 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 Class Name as follows

public class Name
{
    protected String firstName = null;
    protected String lastName = null;

    public Name(String first, String last)
    {
        this.firstName = first;
        this.lastName = last;
    }

    ...
}

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.



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");
Map params = new HashMap();
params.put("surname", theSurname);
params.put("forename", theForename");
List<Person> results = (List<Person>)q.executeWithMap(params);


Numbered Parameters :
Query q = em.createQuery(
    "SELECT p FROM Person p WHERE p.lastName = ?1 AND p.firstName = ?2");
List<Person> results = (List<Person>)q.execute(theSurname, 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 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.