HibernateException: Unexpected row count

12 May 2009    

For little more than a month I’ve been coaching a Junior Java Developer on a project for the Flemish Government. A couple of days ago, he came to me with a problem with his persistance layer.  The application uses an Oracle 10g database and uses Hibernate for Object Relational Mapping. Transactions and DI are configured with Spring. The problem itself might seem trivial, but finding the solution has cost me over a day and a half ..

The relevant part of the datamodel:

schoolchildparent

1 school may have several children. 1 child may have several parents. (preferrably)

Relevant DDL:

create table SCHOOL (
   ID number(19,0) not null enable,
   NAME varchar2(200),
   constraint SCHOOL_PK primary key (ID)
);

create table CHILD (
   ID number(19,0) not null enable,
   SCHOOL_ID number(19,0) not null enable,
   NAME varchar2(200),
   constraint CHILD_PK primary key (ID),
   constraint CHILD_SCHOOL_FK foreign key (SCHOOL_ID) references SCHOOL(ID) enable
);

CREATE SEQUENCE CHILD_SEQ
   INCREMENT BY 1 START WITH 1 MAXVALUE 9.999999999999999e+26
   MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

create or replace trigger TR_CHILD
before insert on CHILD for each row
begin
   select CHILD_SEQ.NEXTVAL
   INTO :NEW.id
   from DUAL;
end;

create table PARENT (
   ID number(19,0) not null enable,
   CHILD_ID number(19,0) not null enable,
   NAME varchar2(200),
   constraint PARENT_PK primary key (ID),
   constraint PARENT_CHILD_FK foreign key (CHILD_ID) references CHILD(ID) enable
);

CREATE SEQUENCE PARENT_SEQ
   INCREMENT BY 1 START WITH 1 MAXVALUE 9.999999999999999e+26
   MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

create or replace trigger TR_PARENT
before insert on PARENT
for each row
begin
   select PARENT_SEQ.NEXTVAL
   INTO :NEW.id
   from DUAL;
end;

Nothing strange going on here: creating tables with constraints, sequences and triggers for autogenerating an id, when inserting a new record.

I’m not going into creating Pojo’s and DAO’s as that is not part of the problem or the solution.

Hibernate mappings looked like this. Classes are mapped bi-directional:

    <class name="be.vlaanderen.School" table="SCHOOL">
        <id name="id" column="ID">
            <generator class="sequence">
                <param name="sequence">SCHOOL_SEQ</param>
            </generator>
        </id>
        <property name="name" column="NAME" />

        <set name="children" cascade="all-delete-orphan">
            <key column="SCHOOL_ID"/>
            <one-to-many class="be.vlaanderen.Child"/>
        </set>
    </class>

    <class name="be.vlaanderen.Child" table="CHILD">
        <id name="id" column="ID">
            <generator class="sequence">
                <param name="sequence">CHILD_SEQ</param>
            </generator>
        </id>
        <many-to-one name="school" column="SCHOOL_ID" />
        <property name="name" column="NAME" />

        <set name="parents" cascade="all-delete-orphan">
            <key column="PARENT_ID"/>
            <one-to-many class="be.vlaanderen.Parent"/>
        </set>
    </class>

    <class name="be.vlaanderen.Parent" table="PARENT">
        <id name="id" column="ID">
            <generator class="sequence">
                <param name="sequence">PARENT_SEQ</param>
            </generator>
        </id>
        <many-to-one name="child" column="CHILD_ID" />
        <property name="name" column="NAME" />
    </class>

At first sight, nothing strange or incorrent. However when his DAO called the

getHibernateTemplate().saveOrUpdate(school);

he always received the following error:

HibernateException: Unexpected row count: 0 expected: 1

This error means that somewhere along the way something happened behind Hibernate’s back that changed the id of the entities we are trying to persist, because of the cascade mode. In code however, no id’s were manually changed at all. It appeared that Hibernate was fighting with itself on how to update the id of the child-records. After a while, we changed the Set in School to:

        <set name="children" cascade="all-delete-orphan" inverse="true">
            <key column="SCHOOL_ID"/>
            <one-to-many class="be.vlaanderen.Child"/>
        </set>

This was a (partial) succes. The child records were persisted and correctly linked to the school-record. When we applied the same change to the parent set, we didn’t have the same success: the parent-records had no reference anymore to the child-records. Changing inverse from false (default) to true, changed the responsibilty of the relationship. Thus: changes in Child weren’t persisted in the database, so if something changed the id of child, we didn’t notice it in the database. When persisting parent, we had no refernce to child, because the id did change. (I dislike the name inverse, something like “owner” would be easier to understand)

In the end, the problem wasn’t with Hibernate .. but with the database. We created a Trigger in Oracle “BEFORE INSERT”. Due this trigger, Hibernate and Oracle were fighting on who should set the foreign keys. Nothing was wrong with the initial mapping and after dropping the triggers, the application worked as expected. The HibernateException was correct and understandable (for once): something changed the id. Whilst most developers would start searching in the code (as I did), it’s important to remember that triggers and sequences in a DBMS are also responsible for changing and linking id’s.

Twitter Facebook
# hibernate