Since JPA version 2.0 the EntityManager offers the method getCriteriaBuilder() to dynamically build select queries without the need of string concatenation using the Java Persistence Query Languge (JPQL). With version 2.1 this CriteriaBuilder offers the two new methods createCriteriaDelete() and createCriteriaUpdate() that let us formulate delete and update queries using the criteria API.
For illustration purposes lets use a simple inheritance use case with the two entities Person and Geek:
@Entity
@Table(name = "T_PERSON")
@Inheritance(strategy = InheritanceType.JOINED)
public class Person {
@Id
@GeneratedValue
private Long id;
@Column(name = "FIRST_NAME")
private String firstName;
@Column(name = "LAST_NAME")
private String lastName;
...
}
@Entity
@Table(name = "T_GEEK")
@Access(AccessType.PROPERTY)
public class Geek extends Person {
private String favouriteProgrammingLanguage;
...
}To delete all geeks from our database that favour Java as their programming language, we can utilize the following code using EntityManager’s new createCriteriaDelete() method:
EntityTransaction transaction = null;
try {
transaction = entityManager.getTransaction();
transaction.begin();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaDelete<Geek> delete = builder.createCriteriaDelete(Geek.class);
Root<Geek> geekRoot = delete.from(Geek.class);
delete.where(builder.equal(geekRoot.get("favouriteProgrammingLanguage"), "Java"));
int numberOfRowsUpdated = entityManager.createQuery(delete).executeUpdate();
LOGGER.info("Deleted " + numberOfRowsUpdated + " rows.");
transaction.commit();
} catch (Exception e) {
if (transaction != null && transaction.isActive()) {
transaction.rollback();
}
}Like with pure SQL we can use the method from() to specify the table the delete query should be issued against and where() to declare our predicates. This way the criteria API allows the definition of bulk deletion operations in a dynamic way without using too much string concatenations.
But how does the SQL look like that is created? First of all the ORM provider has to pay attention that we are deleting from an inheritance hierarchy with the strategy JOINED, meaning that we have two tables T_PERSON and T_GEEK where the second tables stores a reference to the parent table. Hibernate in version 4.3.8.Final creates the following SQL statements:
insert into HT_T_GEEK select geek0_.id as id from T_GEEK geek0_ inner join T_PERSON geek0_1_ on geek0_.id=geek0_1_.id where geek0_.FAV_PROG_LANG=?; delete from T_GEEK where ( id ) IN ( select id from HT_T_GEEK ); delete from T_PERSON where ( id ) IN ( select id from HT_T_GEEK ) delete from HT_T_GEEK;
As we can see, Hibernate fills a temporary table with the ids of the geeks/persons that match our search criteria. Then it deletes all rows from the geek table and then all rows from the person table. Finally the temporary table gets purged.
The sequence of delete statements is clear, as the table T_GEEK has a foreign key constraint on the id column of the T_PERSON table. Hence the rows in the child table have to be deleted before the rows in the parent table. The reason why Hibernate creates a temporary table is explained in this article. To summarize it, the underlying problem is that the query restricts the rows to be deleted on a column that only exists in the child table. But the rows in the child table have to be deleted before the corresponding rows in the parent table. Having deleted the rows in the child table, i.e. all geeks with FAV_PROG_LANG='Java', makes it impossible to delete afterwards all corresponding persons as the geek rows have already been deleted. The solution to this problem is the temporary table that first collects all row ids that should be deleted. Once all ids are known, this information can be used to delete the rows first from the geek table and then from the person table.
The generated SQL statements above are of course independent from the usage of the criteria API. Using the JPQL approach leads to the same generated SQL:
EntityTransaction transaction = null;
try {
transaction = entityManager.getTransaction();
transaction.begin();
int update = entityManager.createQuery("delete from Geek g where g.favouriteProgrammingLanguage = :lang").setParameter("lang", "Java").executeUpdate();
LOGGER.info("Deleted " + update + " rows.");
transaction.commit();
} catch (Exception e) {
if (transaction != null && transaction.isActive()) {
transaction.rollback();
}
}When we change the inheritance strategy from JOINED to SINGLE_TABLE, the generated SQL statements also changes to a single one (here the discriminator column is DTYPE):
delete from T_PERSON where DTYPE='Geek' and FAV_PROG_LANG=?
Conclusion
The new additions to the criteria API for deletion and update let you construct your SQL statements without the need of any string concatenation. But be aware that bulk deletions from an inheritance hierarchy can force the underlying ORM to use temporary tables in order to assemble the list of rows that have to be removed in advance.
| Reference: | JPA 2.1 criteria delete/update and temporary tables in Hibernate from our JCG partner Martin Mois at the Martin’s Developer World blog. |
