Running JPA Queries

The Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. interface defines two methods for running SELECT queries:

Similarly, the TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. interface defines the following methods:

In addition, the Query interface defines a method for running DELETE and UPDATE queries:

Ordinary Query Execution (with getResultList)

The following query retrieves all the Country objects in the database. The query should be ran using the getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List. method, as we expect to receive multiple objects in return:

  TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries.<Country> query =
      em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.("SELECT c FROM Country c", Country.class);
  List<Country> results = query.getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
 as a typed List.();

Both Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. and TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. define a getResultList method, but the version of Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. returns a result list of a raw type (non generic) instead of a parameterized (generic) type:

  Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent
 instances, values, and aggregate data
 from the data store. query = em.createQueryEntityManager.createQuery(qlString) - JPA MethodCreate an instance of Query for executing a
 Java Persistence query language statement.("SELECT c FROM Country c");
  List results = query.getResultListQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
 as an untyped List.();

An attempt to cast the above results to a parameterized type (List<Country>) will cause a compilation warning. If, however, the new TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. interface is used - casting is unnecessary and the warning is avoided.

The query result collection functions as any other ordinary Java collection. For example, a result collection of a parameterized type can be iterated easily using an enhanced for loop:

  for (Country c : results) {
      System.out.println(c.getName());
  }

Note that to only print the country names, a query using projection and retrieving country names directly instead of retrieving the entire Country instances would be more efficient.

Single Result Query Execution (with getSingleResult)

The getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List. method (which was discussed above) can also be used to run queries that return a single result object. In this case, the result object has to be extracted from the result collection after query execution (e.g. by results.get(0)). To eliminate this routine operation JPA provides an additional method, getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result., as a more convenient method when exactly one result object is expected.

The following aggregate query always returns a single result object, which is a Long object reflecting the number of Country objects in the database:

  TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries.<Long> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.(
      "SELECT COUNT(c) FROM Country c", Long.class);
  long countryCount = query.getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result.();

Notice that when a query returns a single object it might be tempting to use Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. over TypedQuery,javax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. even when the result type is known, because the casting of a single object is easy and the code is simple:

  Query query = em.createQueryEntityManager.createQuery(qlString) - JPA MethodCreate an instance of Query for executing a
 Java Persistence query language statement.("SELECT COUNT(c) FROM Country c");
  long countryCount = (Long)query.getSingleResultQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single untyped result.();

An aggregate COUNT query always returns one result, by definition. In other cases our expectation for a single object result might fail, depending on the database content. For example, the following query is expected to return a single Country object:

  Query query = em.createQueryEntityManager.createQuery(qlString) - JPA MethodCreate an instance of Query for executing a
 Java Persistence query language statement.(
      "SELECT c FROM Country c WHERE c.name = 'Canada'");
  Country c = (Country)query.getSingleResultQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single untyped result.();

However, the correctness of this assumption depends on the content of the database. If the database contains multiple Country objects with the name 'Canada' (e.g. due to a bug), a NonUniqueResultExceptionjavax.persistence.NonUniqueResultException - JPA ExceptionThrown by the persistence provider when {@link Query#getSingleResult Query.getSingleResult()} or {@link TypedQuery#getSingleResult TypedQuery.getSingleResult()} is executed on a query and there is more than one result from the query. is thrown. On the other hand, if there are no results at all a NoResultExceptionjavax.persistence.NoResultException - JPA ExceptionThrown by the persistence provider when {@link Query#getSingleResult Query.getSingleResult()} or {@link TypedQuery#getSingleResult TypedQuery.getSingleResult()}is executed on a query and there is no result to return. is thrown. Therefore, using getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result. requires some caution and if there is any possibility that these exceptions might be thrown, they have to be caught and handled.

DELETE and UPDATE Query Execution (with executeUpdate)

DELETE and UPDATE queries are executed using the executeUpdateQuery.executeUpdate() - JPA MethodExecute an update or delete statement. method.

For example, the following query deletes all the Country instances:

  int count = em.createQueryEntityManager.createQuery(qlString) - JPA MethodCreate an instance of Query for executing a
 Java Persistence query language statement.("DELETE FROM Country").executeUpdateQuery.executeUpdate() - JPA MethodExecute an update or delete statement.();

The following query resets the area field in all the Country instances to zero:

  int count = em.createQueryEntityManager.createQuery(qlString) - JPA MethodCreate an instance of Query for executing a
 Java Persistence query language statement.("UPDATE Country SET area = 0").executeUpdateQuery.executeUpdate() - JPA MethodExecute an update or delete statement.();

A TransactionRequiredExceptionjavax.persistence.TransactionRequiredException - JPA ExceptionThrown by the persistence provider when a transaction is required but is not active. is thrown if no transaction is active.

On success - the executeUpdate method returns the number of objects that have been updated or deleted by the query.

The Query Structure section explains DELETE and UPDATE queries in more detail.