one-to-many mapping // how to map an association of legacy tables (Hibernate)

Question

Hi all,

I've got two legacy database tables, layouted in simplified manner like this:

MASTER                        SLAVE
ident                         ident
sName                         sName
sNumber                       sNumber
sDesc                         sValue
-----                         ------
Java Class 'ScenarioMaster'   Java Class 'ScenarioSlave'

Each row has a surrogate index via the ident column, but there is no relationship between MASTER.key and SLAVE.key. However, for the MASTER table, uniqueness of one sName/sNumber pair is true. This would therefore be a possible and meaningful composite key (I do know that those are evil).

In fact, there is a 1:n relationship meaning each MASTER row references n rows in the SLAVE table. Given the column description from above, possible population could be

MASTER                        SLAVE
100                           42
'labl'                        'labl'
1                             1
'some label'                  0.1
                              43
                             'labl'
                              1
                              0.2

Now, using hibernate, how could I reflect this relationship in my java classes? In ScenarioMaster I would declare a Set or List with public getters/setters like

private List<ScenarioSlave> slaves = new ArrayList<ScenarioSlave>();

The hibernate-mappings for ScenarioMaster could contain

<bag name="slaves" cascade="all">
 <key>
  <column name="sName" not-null="true"/>
  <column name="sNumber" not-null="true"/>
 </key>
 <one-to-many class="ScenarioSlave"/>
</bag>

This, however, creates a strage update statement when updating an already persitant ScenarioMaster entity using session.saveOrUpdate(scenarioMaster).

// create master scn and slave slv   
scn.addSlave(slv);  
session.saveOrUpdate(scn);

    Hibernate: 
        update
            SLAVE
        set
            sNumber=?,
            sName=?,
            sValue=?
        where
            sName=? 
            and sNumber=?
    Hibernate: 
        update
            SLAVE
        set
            sName=null,
            sNumber=null 
        where
            sName=? 
            and sNumber=?

What am I doing wrong? Where is that second update coming from? I guess it has something to do with sName/sNumber not being a key for ScenarioSlave. I can't quite figure out, why.

Note that the sName/sNumber parameters do point to valid values and that the ScenarioMaster instance I want to persist via saveOrUpdate actually has a non-null ScenarioSlave instance in the slaves List.

EDIT: Composite key is deferred to a separate class using this mapping

<composite-id name="keyId" class="ScenarioKeyId">
 <key-property name="name" access="field" column="sName"
  type="string" length="20"/>
 <key-property name="number" access="field" column="sNumber"
  type="long"/>
</composite-id>

I don't really want to create a table NAMENUMBER_KEY which maps 'labl', 1 to something like *'key_labl1'* which can then be used as id for hibernate. I suppose, this is what hibernate does (without making use of an actual table).

Answer 1

Hibernate does not work well with composite keys, in my experience. It's the one main problem with Hibernate as far as I've seen. I've worked around it in the past by avoiding the use of "purely" composite keys; that is, I've given composite key tables a unique ID column which Hibernate can then work off of.

Answer 2

I tried a bit around (gotta love Apache Derby) pretending, both tables did not have a composite ID. Then I found out, that the SET ... = null statement most likely is being issued to invalidate members of the slave list.

create table MASTER(
   ident numeric(10) not null, -- key column
   name char(20) not null,
   number numeric(10) not null,
   descr char(64) not null
);

create table SLAVE(
   ident numeric(10) not null, -- key column
   name char(20) not null,
   number numeric(10)   ,      -- foreign key f. MASTER.ident
   value float not null
);

Here is my test code. I set the generator for the master class key as assigned.

  Master master = new Master();
  master.setIdent(Integer.valueOf(0));
  master.setName("name");
  master.setNumber(42);
  master.setDescr("name/42 descr");
  Slave slv = new Slave("name", 42, 0.001);
  master.addSlave(slv);
  theSession.beginTransaction();
  theSession.saveOrUpdate(master);
  theSession.getTransaction().commit();

Then, allowing NULL values in the SLAVE.number row, I get

Hibernate: 
    update
        SLAVE 
    set
        number=null 
    where
        number=?
Hibernate: 
    update
        SLAVE 
    set
        number=? 
    where
        ident=?

Now I only have to figure out how to actually delete stale rows in SLAVE rather then setting the foreign key column to NULL...