Oracle legacy table without good PK: How to Hibernate?

Question

I have legacy tables in an Oracle database which i'd like to access from a Java application with Hibernate. The problem is: the tables don't have good primary keys. For instance, a table would look like this:

create table employee (
  first_name    varchar(64) not null,
  last_name     varchar(64) not null,
  hired_at      date,
  department    varchar(64)
);
create unique index emp_uidx on employee (firstname, lastname, hired_at);

The original designer decided to use the hired_at column as a status field, thinking that the database would never need to distinguish between the John Smiths outside the company, but that employees with the same name could be identified by their hired_at date. Obviously, that creates a primary key that is partially nullable and modifyable. Since the database wouldn't allow this as primary key he went with a unique index instead.

Now, if i try to write a Hibernate mapping for this, i could come up with something like this:

<hibernate-mapping>
  <class name="com.snakeoil.personnel" table="employee">
    <composite-id class="com.snakeoil.personnel.EmpId" name="id">
      <key-property name="firstName" column="first_name" type="string"/>
      <key-property name="lastName" column="last_name" type="string"/>
    </composite-id>
    <property name="hiredAt" column="hired_at" type="date"/>
    <property name="department" type="string">
  </class>
</hibernate-mapping>

This works for reading data, but when i create new entries that differ only in their hiredAt date, i run into org.hibernate.NonUniqueObjectExceptions. Alternatively, i might consider Oracle's ROWID feature:

<id column="ROWID" name="id" type="string">
   <generator class="native"/>
</id>

This works fine for reading data, too. But obviously, you can't persist new objects, since Hibernate now throws an org.hibernate.exception.SQLGrammarException: could not get next sequence value when trying to store the entity.

The obvious way out of this is via surrogate keys. That however would require the database schema to be changed, which brings us back to that "legacy" thing.

What am i overlooking? Is there a way to make Hibernate cooperate with Oracle's ROWID, maybe with a different generator? Or is there a way to make the first idea work, maybe with clever DAOs that evict and reload entities a lot, and hide the complexity from the application? Or should i look for an alternative to Hibernate, Ibatis maybe?

Answer 1

You don't want to use ROWID as a primary key, because it is not guaranteed to be stable over the lifetime of a row.

Adding a synthetic key is the best bet. Use a trigger to populate the column with a sequence value.

You are a bit vague on the legacy aspect, so it is difficult to be certain what the implications are. Adding a column would break any insert statement which doesn't explicitly list the target columns. It might also break any SELECT * queries (unless they select into a variable declared using the %ROWTYPE keyword. But you wouldn't have to change any other application so it used the new primary key instead of the existing columns - unless you really want to.