Oracle遗留表没有很好的PK：如何休眠？ [英] Oracle legacy table without good PK: How to Hibernate?

问题描述

我有一个Oracle数据库中的遗留表，我想用Hibernate从Java应用程序访问。问题是：表没有很好的主键。例如，一张表如下所示：

  create table employee（
 first_name varchar（64）not null， 
 last_name varchar（64）not null，
 hired_at date，
 department varchar（64）
）; 
在员工上创建唯一索引emp_uidx（firstname，lastname，hired_at）; 
  

原始设计师决定使用 hired_at 列作为状态字段，认为数据库永远不需要区分公司之外的John Smiths，但可以通过他们的雇佣日期来识别具有相同名称的员工。显然，这会创建一个部分为空或可修改的主键。因为数据库不允许这个作为主键，所以他使用了一个唯一的索引。

现在，如果我尝试为此写一个Hibernate映射，我可以来这样的东西：

 < hibernate-mapping> 
< class name =com.snakeoil.personneltable =employee> 
< composite-id class =com.snakeoil.personnel.EmpIdname =id> 
< key-property name =firstNamecolumn =first_nametype =string/> 
< key-property name =lastNamecolumn =last_nametype =string/> 
< / composite-id> 
< property name =hiredAtcolumn =hired_attype =date/> 
< property name =departmenttype =string> 
< / class> 
< / hibernate-mapping> 
  

这适用于读取数据，但是当我创建仅在他们的雇佣日期不同的新条目时，我运行到org.hibernate.NonUniqueObjectExceptions。或者，我可能会考虑Oracle的ROWID功能：

 < id column =ROWIDname =idtype =string > 
< generator class =native/> 
< / id> 
  

这也适用于读取数据。但显然，你不能坚持新的对象，因为Hibernate现在抛出一个org.hibernate.exception.SQLGrammarException：尝试存储实体时，无法获得下一个序列值。

显而易见的方法是通过代理键。然而，这将需要改变数据库模式，这使我们回到了遗留的事情。

我忽略了什么？有没有办法让Hibernate与Oracle的ROWID合作，也许有不同的生成器？还是有办法让第一个想法工作，也许有巧妙的DAO，驱逐和重新加载实体，并隐藏应用程序的复杂性？或者我应该寻找一种替代Hibernate的方法，Ibatis也许？解析方案 您不想使用 ROWID 作为主键，因为它不能保证在一行的生命周期内保持稳定。

添加合成密钥是最好的选择。使用触发器以序列值填充列。

您在遗产方面有点模糊，所以很难确定其含义。添加一列会破坏任何没有明确列出目标列的插入语句。它也可能会破坏任何 SELECT * 查询（除非它们选择使用％ROWTYPE 关键字声明的变量。不必更改任何其他应用程序，因此它使用新的主键而不是现有的列 - 除非您真的想要。

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?

解决方案

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.

这篇关于Oracle遗留表没有很好的PK：如何休眠？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！