Why does Hibernate perform INSERT instead of UPDATE when existing value is null?

Question

The Problem

I observed a constraint violation when merging one of my Hibernate entities. There are two tables A and B, where one holds metadata and the other is a mapping of String values.

In table B there is a combined primary key on (id, key).

Now, when I merge an existing instance of A (that also has several entries for B), Hibernate will perform both UPDATE and INSERT statements for B.

The UPDATE statements are no problem, but the INSERT statement cannot be performed, because it causes a constraint violation, since (id, key) is a unique combination.

I figured out that this problem only occures, when the existing value in B is null.

I'm on Oracle 11, JPA 2.1 and EJB 3.2. Not entirely sure, how to determine the Hibernate version, though.

An Example

Let us assume these are entries of table B:

23, fooKey, foo
23, barKey, bar
23, errorKey, null

Now when I merge A (id = 23), UPDATE statements will be performed for fooKey and barKey. For errorKey, however, Hibernate will issue an INSERT statement instead and cause a constraint violation.

So my questions are:

1. Why does Hibernate perform an INSERT statement, when the existing value is null?
2. How can I fix this problem?

My Database

Table A ( id, version, lastUpdate )

Table B ( id, key, value )

My Entity

@Entity
public class A {
  @Id
  public long id;
  public long version = 0;
  public Date lastUpdate = new Date();

  @ElementCollection(fetch = FetchType.EAGER)
  @CollectionTable(name = "B", joinColumns = @JoinColumn(name = "ID"))
  @MapKeyColumn(name = "KEY")
  @Column(name = "VALUE", length = 2000)
  public Map<String, String> myMapping = new HashMap<String, String>();

  // Setters and getters...
}

Answer 1

Answer for 1: From Hibernate perspective the NULL valued element in the collection is not existing. Therefore Hibernate performs an INSERT statement when you "update" the element to a not-null value.

Answer for 2: You should avoid null values in collections (or wrap them) when using Hibernate, and should avoid empty spaces when using Oracle (see below).

I reproduced your problem with Oracle. First I store an "" (empty string) valued element then update this to any other string. I used log4jdbc for checking the actual statements issued by Hibernate.

Here is the demo (with Oracle it fails, with H2 it works):

http://peter.risko.hu/java_incubator/jpa_hibernate_collectionBehaviour_null_empty_string_h2_oracle_log4jdbc.zip

The initial persist:

A a = new A();
a.setId(1);
a.setLastUpdate(new Date());
a.setVersion(1);

Map<String, String> myMap = new HashMap<>();
myMap.put("b", "");
a.setMyMapping(myMap);

em.getTransaction().begin();
em.persist(a);
em.getTransaction().commit();
em.clear();

This is what happens during the initial persist:

insert into A (lastUpdate, version, id) values (to_timestamp('07/13/2017 12:28:37.112', 'mm/dd/yyyy hh24:mi:ss.ff3'), 1, 1)
insert into B (ID, KEY, VALUE) values (1, 'b', '')

Oracle is so "clever" (stupid?) that it inserts NULL instead of the empty string. So in the db there is (1, 'b', NULL) record in the B table:

select * from b;
ID  KEY VALUE
1   b   (null)

Now comes the lookup:

A found = em.find(A.class, 1l);
System.out.println("found no. 1: " + found);

Note that the output clearly shows that Hibarnate hasn't picked up the NULL valued element:

found no. 1: id: 1, version: 1, mapping: {}

Now comes the merge with the "updated" value -- let's say the updated value is "anything". Note that it's actually an addition:

found.myMapping.put("b", "anything");
em.getTransaction().begin();
em.merge(found);
em.getTransaction().commit();
em.clear();

Since this is an addition, this is what Hibernate will try to do:

insert into B (ID, KEY, VALUE) values (1, 'b', 'anything')

And the insert fails with ORA-00001: unique constraint (XXX.SYS_C0045198) violated: there is indeed a record in B with this primary key (1, 'b').

Another nice writing about this:

http://koenserneels.blogspot.hu/2012/09/hibernates-map-behaviour.html