DELETE Queries in JPA/JPQL

As explained in Chapter 2, you can delete entities from the database by following these steps:

• Retrieve the entities into an EntityManager.
• Remove these objects from the EntityManager within an active transaction, either explicitly by calling the remove method or implicitly through a cascading operation.
• Apply changes to the database by calling the commit method.

JPQL DELETE queries provide an alternative way to delete entities. Unlike SELECT queries, which retrieve data from the database, DELETE queries remove specified entities from the database.

Using a DELETE query to remove entities can be more efficient than retrieving and then removing them. However, you should use this approach cautiously because bypassing the EntityManager can disrupt its synchronization with the database. For example, the EntityManager might not be aware that a DELETE query has removed an entity that is cached in its persistence context. Therefore, using a separate EntityManager for DELETE queries is a good practice.

As with any operation that modifies the database, DELETE queries must be executed within an active transaction. The changes become visible to other users (who use other EntityManager instances) only after the transaction is committed.

Delete all queries

The simplest DELETE query removes all instances of a specified entity class, including instances of its subclasses, from the database.

For example, the following three equivalent queries delete all 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 you can use the following query to delete all objects in the database:

DELETE FROM Object

DELETE queries are executed using the executeUpdatejakarta.persistence.Query.executeUpdate()Execute an update or delete statement. method:

  int deletedCount =
      em.createQueryjakarta.persistence.EntityManager.createQuery(String)Create an instance of   Query   for executing a Jakarta Persistence query language statement.("DELETE FROM Country").executeUpdatejakarta.persistence.Query.executeUpdate()Execute an update or delete statement.();

A TransactionRequiredExceptionjakarta.persistence.TransactionRequiredExceptionThrown by the persistence provider when a transaction is required but is not active. is thrown if no transaction is active.

If successful, the executeUpdate method returns the number of objects that were deleted.

Selective deletion

The structure of DELETE queries is simpler than that of SELECT queries. DELETE queries cannot include multiple variables, JOIN clauses, or the GROUP BY, HAVING, and ORDER BY clauses.

A WHERE clause is supported and is essential for removing specific entities.

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

DELETE FROM Country c WHERE c.population < :p

The query can be executed as follows:

  Query query = em.createQueryjakarta.persistence.EntityManager.createQuery(String)Create an instance of   Query   for executing a Jakarta Persistence query language statement.(
      "DELETE FROM Country c WHERE c.population < :p");
  int deletedCount = query.setParameterjakarta.persistence.Query.setParameter(String,Object)Bind an argument value to a named parameter.(p, 100000).executeUpdatejakarta.persistence.Query.executeUpdate()Execute an update or delete statement.();