UPDATE SET Queries in JPA/JPQL

Existing entity objects can be updated, as explained in chapter 2, by:

• Retrieving the entity objects into an EntityManager.
• Updating the relevant entity object fields within an active transaction.
• Applying changes to the database by calling the commit method.

JPQL UPDATE queries provide an alternative way of updating entity objects. Unlike SELECT queries, which are used to retrieve data from the database, UPDATE queries do not retrieve data from the database, but when executed, update the content of specified entity objects in the database.

Updating entity objects in the database using an UPDATE query may be slightly more efficient than retrieving entity objects and then updating them, but it should be used cautiously because bypassing the EntityManager may break its synchronization with the database. For example, the EntityManager may not be aware that a cached entity object in its persistence context has been modified by an UPDATE query. Therefore, it is a good practice to use a separate EntityManager for UPDATE queries.

As with any operation that modifies the database, UPDATE queries can only be executed within an active transaction and the changes are visible to other users (who use other EntityManager instances) only after commit.

Update All Queries

The simpler form of UPDATE queries acts on all the instances of a specified entity class in the database (including instances of subclasses).

For example, the following three equivalent queries increase the population size of all the countries by 10%:

UPDATE Country SET population = population * 11 / 10
UPDATE Country c SET c.population = c.population * 11 / 10
UPDATE Country AS c SET c.population = c.population * 11 / 10

The UPDATE clause defines exactly one range variable (with or without an explicit variable name) for iteration. Multiple variables and JOIN are not supported. The SET clause defines one or more field update expressions (using the range variable name - if defined).

Multiple field update expressions, separated by commas, are also allowed. For example:

UPDATE Country SET population = 0, area = 0

UPDATE queries are executed using the executeUpdateexecuteUpdate()Query's methodExecute an update or delete statement.See JavaDoc Reference Page... method:

  Query query = 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 population = 0, area = 0");
  int updateCount = em.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 modified by the query.

Selective Update

UPDATE queries cannot include the GROUP BY, HAVING and ORDER BY clauses, but the WHERE clause, which is essential for updating selected entity objects, is supported.

For example, the following query updates the population size of countries whose population size is smaller than a specified limit:

UPDATE Country
SET population = population * 11 / 10
WHERE population < :p

The query can be executed as follows:

  Query query = 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 population = population * 11 / 10 " +
      "WHERE population < :p");
  int updateCount = query.setParametersetParameter(name, value)Query's methodBind an argument to a named parameter.See JavaDoc Reference Page...(p, 100000).executeUpdateexecuteUpdate()Query's methodExecute an update or delete statement.See JavaDoc Reference Page...();