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
- Removing these objects from the
EntityManagerwithin an active transaction, either explicitly by calling the
removemethod or implicitly by a cascading operation.
- Applying changes to the database by calling the
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 (which use other
EntityManager instances) only after
This page covers the following topics:
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
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...();
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.
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...();