DELETE Queries in JPQL

To delete record from database use the same JPQL syntax as normal queries, with one exception: begin your query string with the delete keyword instead of the select keyword.

To execute the delete query call the following Query method:

public int executeUpdate();

This method returns the number of objects deleted. The following example deletes all students whose sid is greater than 5

EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

EntityManager em = emf.createEntityManager();

Query q = em.createQuery("DELETE x FROM Student x WHERE sid > 5");

int deleted = q.executeUpdate();

Update Queries in JPQL

To update record from database use the same JPQL syntax as normal queries, with two exceptions: begin your query string with the update keyword instead of the select keyword and set the new value to be updated using set clause.

To execute the update query call the following Query method:

public int executeUpdate();

This method returns the number of objects update. The following example update all students name whose sid is greater than 5

EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

EntityManager em = emf.createEntityManager();

Query q = em.createQuery("UPDATE Student x SET x.name = 'Srinivas' WHERE sid > 5");

int deleted = q.executeUpdate();

Ordering records from JPQL

JPQL queries may optionally contain an order by clause which specifies one or more fields to order by when returning query results. You may follow the order by field clause with the asc or desc keywords, which indicate that ordering should be ascending or descending, respectively. If the direction is omitted, ordering is ascending by default.

select x from Student x order by x.sid asc, x.name desc

The query above returns Student instances sorted by their sid in ascending order. In cases where the sid of two or more students are the same, those instances will be sorted by name in descending order.