JPA 2.1 criteria delete/update and temporary tables in Hibernate

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.

Advertisements

Tags: , ,

One response to “JPA 2.1 criteria delete/update and temporary tables in Hibernate”

  1. pirent420 says :

    Hey,

    When I was looking for some articles about CriteriaDelete, somehow I got into here. It’s really good to know what sql is generated by underlaying ORM based on inheritance strategy. xD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: