Pencils Down

We have a case where most of our tables have composite ids.  This means a key with multiple parts.  While fairly common in the database world, Hibernate appears to prefer that it’s users NOT have complex keys.  The mapping definitions don’t really allow for this to occur (e.g. you can’t specify a generator class in a composite key)

We had thought about generating a single column key (something like a hash of the composite elements), but that just seemed wrong.  I always thought a database should model reality, not some arbitrary manifestation to get a tool to work.

In this example we have two tables that look like this:

Table A
——-
A_NAME VARCHAR(200)
A_ID INT
attributes ….

Table B
——-
B_NAME VARCHAR(200)
B_ID INT
attributes ….

They both have composite ids.  A helper table is generated that provides the many-to-many mappings for the two:

Table AB
——–
A_NAME VARCHAR(200)
A_ID INT
B_NAME VARCHAR(200)
B_ID INT

Using standard Hibernate reverse engineering we end up with the following mappings:

Table A Mapping
—————
…
<composite-id name=”id” class=”path.to.entity.classes.AId”>
 <key-property name=”aId” type=”integer”>
  <column name=”A_ID” precision=”22″ scale=”0″ />
 </key-property>
 <key-property name=”aName” type=”string”>
  <column name=”A_NAME” length=”12″ />
 </key-property>
</composite-id>
…
<set name=”bs” table=”table_ab” cascade=”all” inverse=”true”>
 <key>
  <column name=”A_NAME” not-null=”true” />
  <column name=”A_ID” not-null=”true” />
 </key>
 <many-to-many class=”path.to.entity.classes.B”>
  <column name=”B_NAME” />
  <column name=”B_ID” />
 </many-to-many>
</set>  
…

Table B Mapping
—————
…
<composite-id name=”id” class=”path.to.entity.classes.BId”>
 <key-property name=”bId” type=”integer”>
  <column name=”B_ID” precision=”22″ scale=”0″ />
 </key-property>
 <key-property name=”bName” type=”string”>
  <column name=”B_NAME” length=”12″ />
 </key-property>
</composite-id>
…
<!– CRITICAL - Only one side can have inverse=true, the other must have inverse=false –>
<set name=”as” table=”table_ab” cascade=”all” inverse=”false”>
 <key>
  <column name=”B_NAME” not-null=”true” />
  <column name=”B_ID” not-null=”true” />
 </key>
 <many-to-many class=”path.to.entity.classes.A”>
  <column name=”A_NAME” />
  <column name=”A_ID” />
 </many-to-many>
</set>  
…

Table AB Mapping
—————-
…
<many-to-one name=”a” class=”path.to.entity.classes.A” update=”false” insert=”false” fetch=”select”>
 <column name=”A_NAME” not-null=”true” />
 <column name=”A_ID” not-null=”true” />
</many-to-one>
<many-to-one name=”b” class=”path.to.entity.classes.B” update=”false” insert=”false” fetch=”select”>
 <column name=”B_NAME” not-null=”true” />
 <column name=”B_ID” not-null=”true” />
</many-to-one>
…

Be sure to note use of the inverse attribute in the A and B mappings: only one side of a many-to-many can be the ‘owner’ of the relationship.  The owner is denoted with inverse=true setting.  The other side of a many-to-many MUST be inverse=false.  It is completely arbitrary which is the owner, but Hibernate will not work unless there is only one.

So, once the mapping is cleared up we produce fairly normal Java code to create a many-to-many relation:

A a = new A();
a.setXYZZY(”various attributes”);
AId aid = new AId();
aid.setPKPart1(”string”);
aid.setPKPart2(4);           
a.setId(aid);

session.save(a)

B b = new B();
b.setXYZZY(”various attributes”);
BId bid = new BId();
bid.setPKPart1(”string”);
bid.setPKPart2(12);           
b.setId(bid);

session.save(b)

a.getBs().add(b);
session.saveOrUpdate(a);

b.getAs().add(a);
session.saveOrUpdate(b)

session.flush();

Just to be sure, note we are setting the composite id fields manually.  In Oracle this can be done by accessing a Sequence.  Other databases have built-in row ids.

Uncategorized Hibernate, Java

I think we have a fairly standard database that looks like a parts BOMB.  Someone had the neato idea to use cascading delete in the project.  Hibernate’s cascading delete will follow any required foreign key and delete the children it finds there in a cyclical manner.  Good idea, huh?

No.  This means parent entity Parent with a primary key of ParentPrimaryKey MUST cascade it’s primary key to all children.  So, entity Child has a primary key of ChildPrimaryKey PLUS ParentPrimaryKey.  This continues all the way down your entity tree.  This example assumes simple row id’s for primary key.  If there was some other overriding attribute which makes every primary key a composite, like SystemThatThisEntityLivesOn, then every level has one of those plus all the parent’s.

Then we realize, we can’t actually delete some of the lower level entities because they are a lot of work to create.  So, we configure Hibernate to stop at those entities.

Now if we step back and look at the entity diagram for our database it is not uncommon for an entity at a lower level to have close to 20 component parts of a primary key that have cascaded down.

But, we only delete via cascade a very small subset of the entity tree.

Now, throw into the mix some developers who don’t understand the above features of a relational database or Hibernate in general and we now can’t use the LowLevelEntityId composite object that Hibernate generates as that is ‘unclean’.  We are to flatten all of these id’s wherever used.

Uncategorized Database, Delete, Hibernate