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.

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