Deleting records from a database is a common activity. We can use JPQL to delete individual records or multiple records. JPQL query deals with entities as opposed to database records. In this recipe we will explore the process of deleting entities.
The basic form of the Delete query consists of:
For example, to delete the entity whose name is "Donald Baker" we would use the query.
DELETE FROM Patient p WHERE p.firstName = 'Donald' AND p.lastName = 'Baker'
The steps used to create and use a JPQL Delete query include:
- Obtaining an instance of an
EntityManager - Using the
createQuerymethod to create an instance of aQuerybased on a Delete JPQL string argument - Using the
executeUpdatemethod to execute and return the result of the queryThe easiest place to find an instance of the
EntityManageris in a facade class. This is where we will place our JPQL-based methods.
To illustrate the process add a delete method to the PatientFacade. The method should have first and last name parameters and should return an integer. The return value will indicate whether any entities have actually been deleted.
@Stateless
public class PatientFacade extends AbstractFacade<Patient> {
@PersistenceContext(unitName = "PatientApplication-ejbPU")
private EntityManager entityManager;
...
public int delete(String firstName, String lastName) {
Query query = entityManager.createQuery("DELETE FROM Patient p WHERE p.firstName = '" + firstName + "' AND p.lastName = '" + lastName + "'");
int numberDeleted = query.executeUpdate();
return numberDeleted;
}
...
}
Next, modify the PatientServlet to use this method after the body tag is written.
...
out.println("<body>");
int numberDeleted = patientFacade.delete("Donald", "Baker");
out.println("<h3>" + numberDeleted + " Entities deleted</h3>");
Execute the PatientServlet and you should see a message indicating the deletion of the entity as illustrated next:
The EntityManager was inserted using the @PersistenceContext annotation. In the delete method, string concatenation was used to integrate the two method parameters into the query string.
To execute the query, we used the executeUpdate method. This method can be used for both a Delete query and an Update query as discussed in the next recipe. The method returned the number of entities affected, in this case, the number of entities deleted. We used this number as the method's return value.
The AbstractFacade class provides a remove method to delete an entity. This method is passed a reference to the entity and uses the Criteria API to delete the entity from the database. The delete method we used provides the foundation for more powerful queries where we can select one or more entities to delete based on an arbitrarily complex Where clause. The Criteria API can be used to achieve similar results.