Smart DML Queries With Hibernate Collections

This post details about the insert, update and delete queries executed while working with list and set collections of basic objects (i.e. string, integer, long etc). When using JPA 2 Standard an entity containing an ElementCollection looks like

@Entity
public class Employee {
  @Id
  @Column(name="EMP_ID")
  private long id;
  ...
  @ElementCollection
  @CollectionTable(
        name="PHONE",
        joinColumns=@JoinColumn(name="OWNER_ID")
  )
  @Column(name="PHONE_NUMBER")
  @OrderColumn(name="PHONE_ORDER")
  private List<String> phones;
  ...
}

The operations when the DML queries are executed for a List collection type are :

Deletes – executed when the size of the collection shrinks and when null values are added
Inserts – executed when the size of the collection grows
Updates – executed when the value or the order changes

The order in which these are executed is – Deletes -> Updates -> Inserts.

For e.g. If the list has values {“1”, “2”, “3”, “4”}

example 1 – modify the list by executing the below code
entity.getPhoneNumbers().add(“5”);
entity.getPhoneNumbers().add(“6”);
entity.getPhoneNumbers().add(“7”);
entity.getPhoneNumbers().add(“8”);

entityManager.merge(entity);

4 insert queries would be executed

example 2 – modify the list as follows
entity.getCreditCardNumbers().clear();
entity.getPhoneNumbers().add(“5”);
entity.getPhoneNumbers().add(“6”);
entity.getPhoneNumbers().add(“7”);
entity.getPhoneNumbers().add(“8”);

only 1 update query would be executed since there were already 4 elements in the list {“1”, “2”, “3”, “4”}.

For an attached entity if the list reference is changed, hibernate all the entries are deleted and re-inserted irrespective of whether any value changes are made to the list or not.

The methods getDeletes(), needsInserting() and needsUpdating() in org.hibernate.collection.PersistentList class can provide more details of the internal workings.

The number of queries fired vary slightly when Set is used. The set collection type is not update-able (i.e update queries are never executed). So in scenarios when the size of the set remains the same but the values change a series of delete and insert queries are executed.

This behavior of the Set indicates us to have a small optimization – Use a List (since its update-able) instead of a Set when your database can manage to avoid duplicate values.

Advertisement