Java 如何在辅助表中的非主键列上连接表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1494368/
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
How do I join tables on non-primary key columns in secondary tables?
提问by Jherico
I have a situation where I need to join tables on an object in an ORM class hierarchy where the join column is NOT the primary key of the base class. Here is an example of the table design:
我有一种情况，我需要在 ORM 类层次结构中的对象上连接表，其中连接列不是基类的主键。这是表设计的示例：
CREATE TABLE APP.FOO
(
FOO_ID INTEGER NOT NULL,
TYPE_ID INTEGER NOT NULL,
PRIMARY KEY( FOO_ID )
)
CREATE TABLE APP.BAR
(
FOO_ID INTEGER NOT NULL,
BAR_ID INTEGER NOT NULL,
PRIMARY KEY( BAR_ID ),
CONSTRAINT bar_fk FOREIGN KEY( FOO_ID ) REFERENCES APP.FOO( FOO_ID )
)
CREATE TABLE APP.BAR_NAMES
(
BAR_ID INTEGER NOT NULL,
BAR_NAME VARCHAR(128) NOT NULL,
PRIMARY KEY( BAR_ID, BAR_NAME),
CONSTRAINT bar_names_fk FOREIGN KEY( BAR_ID ) REFERENCES APP.BAR( BAR_ID )
)
And here are the mappings (getters and setters eliminated for brevity
这是映射（为简洁起见，消除了 getter 和 setter
@Entity
@Table(name = "FOO")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "TYPE_ID", discriminatorType = javax.persistence.DiscriminatorType.INTEGER)
public abstract class Foo {
@Id
@Column(name = "FOO_ID")
private Long fooId;
}
@Entity
@DiscriminatorValue("1")
@SecondaryTable(name = "BAR", pkJoinColumns = { @PrimaryKeyJoinColumn(name = "FOO_ID", referencedColumnName = "FOO_ID") })
public class Bar extends Foo{
@Column(table = "BAR", name = "BAR_ID")
Long barId;
}
How can I add the mapping for BAR_NAMESgiven that its join column is not FOO_ID, but BAR_ID?
BAR_NAMES鉴于它的连接列不是FOO_ID，但是如何添加映射BAR_ID？
I Have tried the following:
我尝试了以下方法：
@CollectionOfElements(fetch = FetchType.LAZY)
@Column(name = "BAR_NAME")
@JoinTable(name = "BAR_NAMES", joinColumns = @JoinColumn(table = "BAR", name = "BAR_ID", referencedColumnName="BAR_ID"))
List<String> names = new ArrayList<String>();
This fails because the SQL for retrieving the Bar object tries to get a BAR_ID value from the FOO table. I have also tried replacing the JoinTable annotation with
这将失败，因为用于检索 Bar 对象的 SQL 尝试从 FOO 表中获取 BAR_ID 值。我也试过用
@JoinTable(name = "BAR_NAMES", joinColumns = @JoinColumn(name = "BAR_ID"))
This produces no SQL error, but also retrieves no data because the query against BAR_NAMES is using the FOO_ID as the join value instead of the BAR_ID.
这不会产生 SQL 错误，但也不会检索任何数据，因为针对 BAR_NAMES 的查询使用 FOO_ID 作为连接值而不是 BAR_ID。
For testing purposes, I've populated the DB with the following commands
出于测试目的，我使用以下命令填充了数据库
insert into FOO (FOO_ID, TYPE_ID) values (10, 1);
insert into BAR (FOO_ID, BAR_ID) values (10, 20);
insert into BAR_NAMES (BAR_ID, BAR_NAME) values (20, 'HELLO');
Many solutions which appear to work will return an empty collection when getting Foo object for ID 10 (as opposed to a collection containing 1 name)
许多似乎有效的解决方案在获取 ID 10 的 Foo 对象时将返回一个空集合（而不是包含 1 个名称的集合）
采纳答案by Jherico
I was able to find a solution to this. If you map the Bar class like so
我能够找到解决方案。如果你像这样映射 Bar 类
@Entity
@DiscriminatorValue("1")
@SecondaryTable(name = "BAR", pkJoinColumns = { @PrimaryKeyJoinColumn(name = "FOO_ID", referencedColumnName = "FOO_ID") })
public class Bar extends Foo {
@OneToOne
@JoinColumn(table = "BAR", name = "BAR_ID")
MiniBar miniBar;
}
and add the following class
并添加以下类
@Entity
@SqlResultSetMapping(name = "compositekey", entities = @EntityResult(entityClass = MiniBar.class, fields = { @FieldResult(name = "miniBar", column = "BAR_ID"), }))
@NamedNativeQuery(name = "compositekey", query = "select BAR_ID from BAR", resultSetMapping = "compositekey")
@Table(name = "BAR")
public class MiniBar {
@Id
@Column(name = "BAR_ID")
Long barId;
}
You can then add any sort of mapping you want to the MiniBarclass as if barId were the primary key, and then further make it available in the outer Barclass.
然后，您可以将您想要的任何类型的映射添加到MiniBar类中，就好像 barId 是主键一样，然后进一步使其在外部Bar类中可用。
回答by matt b
Not sure how to do this with JPA/Annotations, but with Hibernate XML mapping files it would be something like:
不确定如何使用 JPA/Annotations 执行此操作，但使用 Hibernate XML 映射文件将类似于：
<class name="Bar" table="BAR">
<id name="id" type="int">
<column name="BAR_ID"/>
<generator class="native"/>
</id>
<set name="barNames" table="BAR_NAMES">
<!-- Key in BAR_NAMES table to map to this class's key -->
<key column="BAR_ID"/>
<!-- The value in the BAR_NAMES table we want to populate this set with -->
<element type="string" column="BAR_NAME"/>
</set>
</class>
回答by ChssPly76
You will not be able to do what you want. @CollectionOfElements (and @OneToMany, for that matter) are alwaysmapped via owner's entity primary key.
你将无法做你想做的事。@CollectionOfElements（和@OneToMany，就此而言）总是通过所有者的实体主键映射。
The way you map Foo / Bar inheritance is rather strange as well - they clearly are not in the same table; seems like using JoinedSubclasswould be a better approach. Keep in mind that still won't help you map bar_namesto bar_idbecause primary key value is shared among the hierarchy (even though column name may be different for subclass).
你映射 Foo / Bar 继承的方式也很奇怪——它们显然不在同一个表中；似乎使用JoinedSubclass会是一个更好的方法。请记住，这仍然不会帮助您映射bar_names到，bar_id因为主键值在层次结构之间共享（即使子类的列名可能不同）。
A possible alternative is to use @OneToOnemapping between Foo and Bar instead of inheritance. That's the only way you will be able to map bar_namesto bar_idand the most appropriate mapping for you table structure (though, perhaps, not for your domain model).
一种可能的替代方法是在 Foo 和 Bar 之间使用@OneToOne映射而不是继承。这是您能够映射bar_names到bar_id表结构的唯一方法和最合适的映射（尽管可能不适用于您的域模型）。
