Hibernate trouble: INSERT instead of UPDATE when using Inheritance:SINGLE_TABLE and SecondaryTables

Question

I have implemented inheritance hierarchy using SINGLE_TABLE with SecondaryTables.

This works otherwise, but when my secondary table's field(s) are empty (= null in Oracle), next update to the entity fails since Hibernate thinks it should INSERT to the table when it should UPDATE. Example:

CREATE TABLE TASK 
(
   ID NUMBER(10) NOT NULL 
   , TYPE NUMBER(1) NOT NULL 
   , STATUS NUMBER(1) NOT NULL 
, CONSTRAINT TASK_PK PRIMARY KEY  (ID) ENABLE);

CREATE TABLE SUB_TASK 
(
  ID NUMBER(10) NOT NULL 
, TEXT VARCHAR2(4000 CHAR) 
, CONSTRAINT SUB_TASK_PK PRIMARY KEY 
(ID) ENABLE);

ALTER TABLE SUB_TASK
ADD CONSTRAINT SUB_TASK_FK1 FOREIGN KEY
(ID)REFERENCES TASK(ID)ENABLE;

Task.java:

@Entity
@Table(name="TASK")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="TYPE",discriminatorType=DiscriminatorType.INTEGER)
public abstract class Task implements Serializable, Comparable<Task> {

@Id
@Column(nullable = false)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TASK_GEN")
@SequenceGenerator(name = "TASK_GEN", sequenceName = "SEQ_TASK", allocationSize = 1)
private Long id;

@Column(name = "TYPE", nullable = false, insertable=false, updatable=false)
private int typeCode;

SubTask.java

@Entity
@DiscriminatorValue("7")
@SecondaryTable(name = "SUB_TASK", pkJoinColumns = {@PrimaryKeyJoinColumn(name = "id", referencedColumnName = "id")})
public class SubTask extends Task implements Serializable {

@Column(name="TEXT", length = 4000, table="SUB_TASK")
@Size(max = 4000)
private String text;

public String getText() {
    return text;
}

public void setText(String text) {
    this.text = text;
}

}

Exception when saving an entity which previously had null SubTask.text:

Caused by: org.hibernate.exception.ConstraintViolationException: ORA-00001: yksikäsitteistä rajoitetta (SUB_TASK_PK) loukattu

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:128)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy75.executeUpdate(Unknown Source)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2965)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3028)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3350)
at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:140)

I debugged relevant Hibernate code, AbstractEntityPersister.java:

    if ( !isInverseTable( j ) ) {

        final boolean isRowToUpdate;
        if ( isNullableTable( j ) && oldFields != null && isAllNull( oldFields, j ) ) {
            //don't bother trying to update, we know there is no row there yet
            isRowToUpdate = false;
        }
        else if ( isNullableTable( j ) && isAllNull( fields, j ) ) {
            //if all fields are null, we might need to delete existing row
            isRowToUpdate = true;
            delete( id, oldVersion, j, object, getSQLDeleteStrings()[j], session, null );
        }
        else {
            //there is probably a row there, so try to update
            //if no rows were updated, we will find out
            isRowToUpdate = update( id, fields, oldFields, rowId, includeProperty, j, oldVersion, object, sql, session );
        }

        if ( !isRowToUpdate && !isAllNull( fields, j ) ) {
            // assume that the row was not there since it previously had only null
            // values, so do an INSERT instead
            //TODO: does not respect dynamic-insert
            insert( id, fields, getPropertyInsertability(), j, getSQLInsertStrings()[j], object, session );
        }

    }

and it looks like the first if ("don't bother trying to update") is done and isRowToUpdate = false, and the last if (if ( !isRowToUpdate && !isAllNull( fields, j ) ) is also done, so that insert is executed.

I guess I could get around this by adding a dummy non-null field to table SUB_TASK, but is this my only choice?

Using Hibernate 4.17.final

EDIT: to hopefully clarity what I am doing:

SubTask s = taskRepository.findOne(42l);
s.setText("dasdsa");
taskRepository.save(s); // OK
s.setText(null); 
taskRepository.save(s); // OK
s.setText("update after null value");
taskRepository.save(s); // exception

I use Spring Data for persistence, but it does not look like problem is related to it (and I would assume same thing happens when using vanilla JPA)

Please clarify what you're doing. All you say is "Exception when saving an entity which previously had null SubTask.value". Show us the code you're executing, and the complete exception atck trace. SubTask doesn't even have a value field. So it doesn't make much sense. — JB Nizet, Feb 04 '13 at 09:42
Added short description to the end. Fixed "SubTask.value" => "SubTask.text" — Janne Mattila, Feb 04 '13 at 10:43

score 1 · Answer 1 · answered Mar 07 '13 at 13:08

I had a somewhat similar problem, with a @ManyToOne relationship defined in a secondary table. When the secondary table had a row referring to the entity in the primary table, the relationship column was null and trying to set a value, Hibernate tried to insert a new row instead of updating the existing one, throwing a constraint violation exception.

The solution was to use the annotation @org.hibernate.annotations.Table(appliesTo="secondary_table", optional=false), which makes Hibernate to update the row.

I´m not sure about the behaviour when the row in the secondary table doesn´t exist, but perhaps it solves your problem.

commenting on both answers : when and if hibernate complains that it cannot find such table, make sure that you try a lower-case name of the table in the value of the "appliesTo" attribute (even though you might have declared it with upper-case in corresponding javax.persistence.Table annotation) - it seems that this matters — hello_earth, Jun 25 '21 at 09:44

score 0 · Answer 2 · answered Mar 21 '17 at 11:33

Using @dampudia solution worked for me.

Please make sure to add the following on the primary table and not the secondary table. Also, please note @Table annotation used here is from hibernate and not java.persistence.

@org.hibernate.annotations.Table(appliesTo="secondary_table", optional=false)

Hibernate trouble: INSERT instead of UPDATE when using Inheritance:SINGLE_TABLE and SecondaryTables

2 Answers2

Linked