Let's create a JPQL query to highlight its usage

Query query = em.createQuery("SELECT p FROM Product p WHERE p.param2 < :threshold ORDER BY p.param1 ascending");
query.setParameter("threshold", my_threshold);
List results = query.getResultList();

In this Query, we implicitly select JPQL by using the method EntityManager.createQuery() , and the query is specified to return all objects of type Product (or subclasses) which have the field param2 less than some threshold value ordering the results by the value of field param1 . We've specified the query like this because we want to pass the threshold value in as a parameter (so maybe running it once with one value, and once with a different value). We then set the parameter value of our threshold parameter. The Query is then executed to return a List of results. The example is to highlight the typical methods specified for a (JPQL) Query.

Let's create an SQL query to highlight its usage

Query query = em.createNativeQuery("SELECT * FROM Product p WHERE p.param2 < ?1");
query.setParameter(1, my_threshold);
List results = query.getResultList();

So we implicitly select SQL by using the method EntityManager.createNativeQuery() , and the query is specified like in the JPQL case to return all instances of type Product (using the table name in this SQL query) where the column param2 is less than some threshold value.

In JPA to specify the range of a query you have two methods available. So you could do

Query query = em.createQuery("SELECT p FROM Product p WHERE p.param2 < :threshold ORDER BY p.param1 ascending");
query.setFirstResult(1);
query.setMaxResults(3);

so we will get results 1, 2, and 3 returned only. The first result starts at 0 by default.

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>

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 results = query.getResultList();

The above example was for the JPQL query language. We can do a similar thing using SQL, so we define the following in our MetaData for our Product class

    <entity class="Product">
        ...
        <named-native-query name="PriceBelowValue"><![CDATA[
        SELECT NAME FROM PRODUCT WHERE PRICE < ?1
        ]]></named-native-query>
    </entity>

So here we have an SQL query that will return the names of all Products that have a price less than a specified value. This leaves us the flexibility to specify the value at runtime. So here we run our named query, asking for the names of all Products with price below 20 euros.

    Query query = em.createNamedNativeQuery("PriceBelowValue");
    query.setParameter(1, new Double(20.0));
    List results = query.getResultList();

All of the examples above have been specifed within the <entity> element of the MetaData. You can also define these named queries in annotations in the class itself, but clearly that is polluting your model.

JPA's query API allows implementations to support extensions ("hints") and provides a simple interface for enabling the use of such extensions on queries.

q.setHint("extension_name", value);

DataNucleus provides various extensions for different types of queries.

JPA's query API supports named and numbered parameters and provides method for setting the value of particular parameters. To set a named parameter, for example, you could do

Query query = em.createQuery("SELECT p FROM Product p WHERE p.param2 < :threshold ORDER BY p.param1 ascending");
q.setParameter("threshold", value);

To set a numbered parameter you could do

Query query = em.createQuery("SELECT p FROM Product p WHERE p.param2 < ?1 ORDER BY p.param1 ascending");
q.setParameter(1, value);

Numbered parameters are numbered from 1.

To execute a JPA query you would typically call getResultList . This will return a List of results. This should not be called when the query is an "UPDATE"/"DELETE".

Query query = em.createQuery("SELECT p FROM Product p WHERE p.param2 < :threshold ORDER BY p.param1 ascending");
q.setParameter("threshold", value);
List results = q.getResultList();

To execute a JPA query where you are expecting a single value to be returned you would call getSingleResult . This will return the single Object. If the query returns more than one result then you will get an Exception. This should not be called when the query is an "UPDATE"/"DELETE".

Query query = em.createQuery("SELECT p FROM Product p WHERE p.param2 = :value");
q.setParameter("value", val1);
Product prod = q.getSingleResult();

To execute a JPA UPDATE/DELETE query you would call executeUpdate . This will return the number of objects changed by the call. This should not be called when the query is a "SELECT".

Query query = em.createQuery("DELETE FROM Product p");
int number = q.executeUpdate();

By default, when a query is executed it will be evaluated against the contents of the datastore at the point of execution. If there are any outstanding changes waiting to be flushed then these will not feature in the results. To make sure all outstanding changes are respected

query.setFlushMode(FlushModeType.AUTO);