Develop Java Persistence Query Language statements that update a set of entities using UPDATE/SET and DELETE FROM.

[Note]

Bulk update and delete operations apply to entities of a single entity class (together with its subclasses, if any). Only one entity abstract schema type may be specified in the FROM or UPDATE clause.

update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable] SET update_item {, update_item}*
					

delete_clause ::= DELETE FROM abstract_schema_name [[AS] identification_variable]
					

A delete operation only applies to entities of the specified class and its subclasses. It DOES NOT cascade to related entities.

Bulk update maps directly to a database update operation, bypassing optimistic locking checks. Portable applications must manually update the value of the version column, if desired, and/or manually validate the value of the version column.

The persistence context is not synchronized with the result of the bulk update or delete.

NOTE: Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a separate transaction or at the beginning of a transaction (before entities have been accessed whose state might be affected by such operations).

Examples:

DELETE
FROM Customer c
WHERE c.status = 'inactive'
					

DELETE
FROM Customer c
WHERE c.status = 'inactive' AND c.orders IS EMPTY
					

UPDATE customer c
SET c.status = 'outstanding'
WHERE c.balance < 10000
	AND 1000 > (SELECT COUNT(o) FROM customer cust JOIN cust.order o)
					

Delete by query uses the same JPQL syntax as normal queries, with one exception: begin your query string with the DELETE keyword instead of the SELECT keyword. To then execute the delete, you call the following Query method:

public int executeUpdate();
					

This method returns the number of objects deleted. The following example deletes all subscriptions whose expiration date has passed:

Query q = em.createQuery("DELETE s FROM Subscription s WHERE s.subscriptionDate < :today");
q.setParameter("today", new Date());
int deleted = q.executeUpdate();
					

Similar to bulk deletes, it is sometimes necessary to perform updates against a large number of queries in a single operation, without having to bring all the instances down to the client. Rather than bring these objects into memory and modifying them individually, JPA allows you to perform a single bulk update based on JPQL criteria.

Update by query uses the same JPQL syntax as normal queries, except that the query string begins with the UPDATE keyword instead of SELECT. To execute the update, you call the following Query method:

public int executeUpdate();
					

This method returns the number of objects updated. The following example updates all subscriptions whose expiration date has passed to have the "paid" field set to true:

Query q = em.createQuery("UPDATE Subscription s SET s.paid = :paid WHERE s.subscriptionDate < :today");
q.setParameter("today", new Date());
q.setParameter("paid", true);
int updated = q.executeUpdate();
					

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     Free Mock Exam Engine