The Update query will modify one or more stored entities. This query is used to modify the content of a data store. It specifies the fields of the entity to be modified, their new values and which entities to be affected.
The Update query normally will include the Update clause, a Set clause and a Where clause. The Update clause is similar in structure to a Select clause, but uses the UPDATE keyword instead. The Set clause follows and starts with the SET keyword and has an assignment looking expression. The Where clause follows the Set clause.
For example, to update the dosage for a specific type of mediation we can use this query:
UPDATE Medication m SET m.dosage = 6 WHERE m.type = 'ACE'
The Update clause specifies the name of the entity and declares an identification variable. The Set clause assigns 6 to the dosage field and the While clause selects only those medications whose type is ACE.
The steps used to create and use a JPQL Update query include:
- Obtaining an instance of an
EntityManager - Using the
createQuerymethod to create an instance of aQuerybased on an Update query 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.
We will modify the MedicationFacade class to illustrate the UPDATE query. Add an updateDosage method to the MedicationFacade class and pass two string arguments representing a type and a dosage. The method will invoke the executeUpdate method and return the number of entities affected.
@Stateless
public class MedicationFacade extends AbstractFacade<Medication> {
@PersistenceContext(unitName="PatientApplication-ejbPU")
private EntityManager entityManager;
...
public int updateDosage(String type, int dosage) {
Query query = entityManager.createQuery("UPDATE Medication m " + "SET m.dosage = " + dosage + " WHERE m.type = '" + type + "'");
int numberUpdated = query.executeUpdate();
return numberUpdated;
}
...
}
Next, modify the PatientServlet by adding code to call updateDosage and display the results. Add this code after the body tag is written.
...
out.println("<body>");
int numberUpdated = medicationFacade.updateDosage("ACE", 6);
out.println("<h3>" + numberUpdated + " Entities updated</h3>");
Execute the PatientServlet and you should get a message similar to the following indicating that 6 entities have been updated.
We used the entityManager variable to create a new query. Notice, the query used string concatenation to create a query with the two method parameters.
The executeUpdate method was used to execute the query. The actual number of entities affected by the query was returned. If necessary, we can use this number to verify the correctness of the operation. The number of entities affected was then returned by the method.