DELETE Queries in JPA/JPQL

As explained in chapter 2, entity objects can be deleted from the database by:

• Retrieving the entity objects into an EntityManager.
• Removing these objects from the EntityManager within an active transaction, either explicitly by calling the remove method or implicitly by a cascading operation.
• Applying changes to the database by calling the commit method.

JPQL DELETE queries provide an alternative way for deleting entity objects. Unlike SELECT queries, which are used to retrieve data from the database, DELETE queries do not retrieve data from the database, but when executed, delete specified entity objects from the database.

Removing entity objects from the database using a DELETE query may be slightly more efficient than retrieving entity objects and then removing 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 removed from the database by a DELETE query. Therefore, it is a good practice to use a separate EntityManager for DELETE queries.

As with any operation that modifies the database, DELETE 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.

Delete All Queries

The simplest form of a DELETE query removes all the instances of a specified entity class (including instances of subclasses) from the database.

For example, the following three equivalent queries delete all the Country instances:

DELETE FROM Country       // no variable
DELETE FROM Country c     // an optional variable
DELETE FROM Country AS c  // AS + an optional variable

ObjectDB supports using the java.lang.Object class in queries (as an extension to JPA), so the following query can be used to delete all the objects in the database:

DELETE FROM Object

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

  int deletedCount = 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...();

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 deleted by the query.

Selective Deletion

The structure of DELETE queries is very simple relative to the structure of SELECT queries. DELETE queries cannot include multiple variables and JOIN, and cannot include the GROUP BY, HAVING and ORDER BY clauses.

A WHERE clause, which is essential for removing selected entity objects, is supported.

For example, the following query deletes the countries with population size that is smaller than a specified limit:

DELETE FROM Country c WHERE c.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...(
      "DELETE FROM Country c WHERE c.population < :p");
  int deletedCount = 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...();