Running JPA Queries

The Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... interface defines two methods for running SELECT queries:

• Query.getSingleResultgetSingleResult()Query's methodExecute a SELECT query that returns a single untyped result.See JavaDoc Reference Page... - for use when exactly one result object is expected.
• Query.getResultListgetResultList()Query's methodExecute a SELECT query and return the query results as an untyped List.See JavaDoc Reference Page... - for general use in any other case.

Similarly, the TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... interface defines the following methods:

• TypedQuery.getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page... - for use when exactly one result object is expected.
• TypedQuery.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results as a typed List.See JavaDoc Reference Page... - for general use in any other case.

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

• Query.executeUpdate executeUpdate()Query's methodExecute an update or delete statement.See JavaDoc Reference Page...- for running only 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 getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results as a typed List.See JavaDoc Reference Page... method, as we expect to receive multiple objects in return:

  TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Country> query =
      em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("SELECT c FROM Country c", Country.class);
  List<Country> results = query.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

Both Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... and TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... define a getResultList method, but the version of Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... returns a result list of a raw type (non generic) instead of a parameterized (generic) type:

  Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... query = em.createQuerycreateQuery(qlString)EntityManager's methodCreate an instance of Query for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("SELECT c FROM Country c");
  List results = query.getResultListgetResultList()Query's methodExecute a SELECT query and return the query results
 as an untyped List.See JavaDoc Reference Page...();

An attempt to cast the above results to a parameterized type (List<Country>) will cause a compilation warning. If, however, the new TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... 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 getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results as a typed List.See JavaDoc Reference Page... 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, getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page..., 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.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Long> query = em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
      "SELECT COUNT(c) FROM Country c", Long.class);
  long countryCount = query.getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page...();

Notice that when a query returns a single object it might be tempting to use Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... over TypedQuery,javax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... even when the result type is known, because the casting of a single object is easy and the code is simple:

  Query query = em.createQuerycreateQuery(qlString)EntityManager's methodCreate an instance of Query for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("SELECT COUNT(c) FROM Country c");
  long countryCount = (Long)query.getSingleResultgetSingleResult()Query's methodExecute a SELECT query that returns a single untyped result.See JavaDoc Reference Page...();

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.createQuerycreateQuery(qlString)EntityManager's methodCreate an instance of Query for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
      "SELECT c FROM Country c WHERE c.name = 'Canada'");
  Country c = (Country)query.getSingleResultgetSingleResult()Query's methodExecute a SELECT query that returns a single untyped result.See JavaDoc Reference Page...();

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.NonUniqueResultExceptionJPA exceptionThrown by the persistence provider when Query.getSingleResult() or TypedQuery.getSingleResult() is executed on a query and there is more than one result from the query.See JavaDoc Reference Page... is thrown. On the other hand, if there are no results at all a NoResultExceptionjavax.persistence.NoResultExceptionJPA exceptionThrown by the persistence provider when Query.getSingleResult() or TypedQuery.getSingleResult()is executed on a query and there is no result to return.See JavaDoc Reference Page... is thrown. Therefore, using getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page... 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 executeUpdateexecuteUpdate()Query's methodExecute an update or delete statement.See JavaDoc Reference Page... method.

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

  int count = em.createQuerycreateQuery(qlString)EntityManager's methodCreate an instance of Query for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("DELETE FROM Country").executeUpdateexecuteUpdate()Query's methodExecute an update or delete statement.See JavaDoc Reference Page...();

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

  int count = em.createQuerycreateQuery(qlString)EntityManager's methodCreate an instance of Query for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("UPDATE Country SET area = 0").executeUpdateexecuteUpdate()Query's methodExecute an update or delete statement.See JavaDoc Reference Page...();

A TransactionRequiredExceptionjavax.persistence.TransactionRequiredExceptionJPA exceptionThrown by the persistence provider when a transaction is required but is not active.See JavaDoc Reference Page... 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.