没有好的 PK 的 Oracle 旧表：如何休眠？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1519078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle legacy table without good PK: How to Hibernate?
提问by wallenborn
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:
我在 Oracle 数据库中有旧表，我想从带有 Hibernate 的 Java 应用程序访问这些表。问题是：表没有好的主键。例如，一个表看起来像这样：
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_atcolumn 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.
最初的设计者决定使用该hired_at列作为状态字段，认为数据库永远不需要区分公司外的 John Smith，但可以通过其hired_at 日期识别同名员工。显然，这会创建一个部分可为空且可修改的主键。由于数据库不允许将其作为主键，因此他改为使用唯一索引。
Now, if i try to write a Hibernate mapping for this, i could come up with something like this:
现在，如果我尝试为此编写 Hibernate 映射，我可以想出这样的东西：
<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:
这适用于读取数据，但是当我创建仅在其租用日期不同的新条目时，我遇到了 org.hibernate.NonUniqueObjectExceptions。或者，我可能会考虑 Oracle 的 ROWID 功能：
<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.
这也适用于读取数据。但很明显，你不能持久化新对象，因为 Hibernate 现在抛出一个 org.hibernate.exception.SQLGrammarException: 在尝试存储实体时无法获得下一个序列值。
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?
我在俯瞰什么？有没有办法让 Hibernate 与 Oracle 的 ROWID 合作，也许使用不同的生成器？或者有没有办法让第一个想法奏效，也许使用巧妙的 DAO 驱逐和重新加载实体，并隐藏应用程序的复杂性？或者我应该寻找 Hibernate 的替代品，也许是 Ibatis？
采纳答案by APC
You don't want to use ROWIDas a primary key, because it is not guaranteed to be stable over the lifetime of a row.
您不想用ROWID作主键，因为它不能保证在行的生命周期内稳定。
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 %ROWTYPEkeyword. 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.
您在遗留方面有点含糊不清，因此很难确定其含义是什么。添加一列会破坏任何未明确列出目标列的插入语句。它也可能会破坏任何SELECT *查询（除非他们选择使用%ROWTYPE关键字声明的变量。但您不必更改任何其他应用程序，因此它使用新的主键而不是现有的列 - 除非您真的想要。
回答by duffymo
I would add a surrogate key with a backing sequence. It wouldn't change any of your legacy semantics and Hibernate would be satisfied.
我会添加一个带有支持序列的代理键。它不会改变您的任何遗留语义，并且 Hibernate 会满意。
Just curious - if the index has first and last names and hired date, why does your composite key exclude hired date? I would have expected to see all the fields in the index in the composite key as well.
只是好奇 - 如果索引有名字和姓氏以及雇用日期，为什么您的复合键不包括雇用日期？我本来希望在复合键中看到索引中的所有字段。
