JPQL - EJB 3 in Action - page 442

that appropriately sets the user status. You could run a query to retrieve the collection of
User entities and then iterate through the collection and update the status. An easier way
is to use a bulk UPDATE statement to update the collection of entities matching the condi-
tion, as in this example:
UPDATE User u
SET u.status = 'G'
WHERE u.numTrades >=?1
You've seen some examples of DELETE and UPDATE statements in JPQL in previous sec-
tions, but we avoided any in-depth discussion until now. Let's assume that Action-Bazaar
administrators need functionality to remove instances of entities such as User based on
certain conditions. You start with the following code:
@PersistenceContext em;
. . .
// start transaction
Query query = em.createQuery("DELETE USER u WHERE u.status = :status ");
query.setParameter("status", 'GOLD');
int results = query.executeUpdate();
//end transaction
In this code, the use of UPDATE and DELETE statements is quite similar to using any
other JPQL statements, except for two significant differences. First, you use the ex-
ecuteUpdate method of the Query interface to perform bulk updates and deletes instead
of getResultList or getSingleResult . Second, you must invoke executeUp-
date within an active transaction.
Because bulk updates and deletes involve many pitfalls, we recommend that you isolate
any bulk operations to a discrete transaction, because they're directly translated into data-
base operations and may cause inconsistencies between managed entities and the database.
Vendors are required only to execute the update or delete operations and aren't required to
modify any changes to the managed entities according to the specification. In other words,
the persistence provider won't remove any associated entities when an entity is removed as
a result of a bulk operation.
At this point, we've covered a lot of ground: queries, annotations, and JPQL. There's only
one topic left to discuss in this arena: using regular SQL queries in EJB 3.