如何在辅助表中的非主键列上连接表？ [英] How do I join tables on non-primary key columns in secondary tables?

问题描述

我有一种情况，我需要在ORM类层次结构中的对象上连接表，其中连接列不是基类的主键。这是一个表设计的例子：

pre $ code 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）
）

这里是映射（getter和setter为了简洁而被删除）

  @Entity 
 @Table（name =FOO）
 @Inheritance（strategy = InheritanceType.SINGLE_TABLE）
 @DiscriminatorColumn（name =TYPE_ID，discriminatorType = javax.persistence.Discriminator Type.INTEGER）
 public abstract class Foo {
 @Id 
 @Column（name =FOO_ID）
 private Long fooId; 
 
 $ b @Entity 
 @ DiscriminatorValue（1）
 @SecondaryTable（name =BAR，pkJoinColumns = {@PrimaryKeyJoinColumn（name =FOO_ID ，referenceColumnName =FOO_ID）}）
 public class Bar extends Foo {
 @Column（table =BAR，name =BAR_ID）
长barId; 
 
 
 
 $ b 
如何添加 BAR_NAMES  code>，因为它的连接列不是 FOO_ID ，但是 BAR_ID ？
 
 
 
我试过以下内容： 
 
 
  @CollectionOfElements（fetch = FetchType.LAZY）
 @Column（name =BAR_NAME）
 @JoinTable（name =BAR_NAMES，joinColumns = @JoinColumn（table =BAR，name =BAR_ID，referencedColumnName =BAR_ID））
列表< String> names = new ArrayList< String>（）; 
  
这会失败，因为用于检索Bar对象的SQL试图从FOO表中获取BAR_ID值。我也尝试用 
 
 
  @JoinTable（name =BAR_NAMES，joinColumns = @JoinColumn（name =）替换JoinTable注释。它不会产生SQL错误，但也不会检索数据，因为对BAR_NAMES的查询是使用FOO_ID作为连接值而不是BAR_ID。
 
 
出于测试目的，我用以下命令填充了数据库
 插入FOO（FOO_ID，TYPE_ID）值（10,1）; 
插入BAR（FOO_ID，BAR_ID）值（10,20）; 
插入BAR_NAMES（BAR_ID，BAR_NAME）的值（20，'HELLO'）; 
  
许多解决方案在获得ID为10的Foo对象时会返回一个空集合（相反到包含1个名称的集合）
解决方案
我能找到解决方案。如果你像这样映射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; 
} 
  
并添加以下类别
 
 
  @Entity 
 @SqlResultSetMapping（name =compositekey，entities = @EntityResult（entityClass = MiniBar.class，fields = {@FieldResult（name =miniBar ，column =BAR_ID），}））
 @NamedNativeQuery（name =compositekey，query =从BAR中选择BAR_ID，resultSetMapping =compositekey）
 @Table（name =BAR ）
 public class MiniBar {
 @Id 
 @Column（name =BAR_ID）
长barId; 
 
 $ / code> 
然后你可以添加任何你想要的映射到 MiniBar 类，就好像barId是主键一样，然后进一步在外部 Bar 类中使用它。
 
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:
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
@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_NAMES given that its join column is not FOO_ID, but 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
@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.

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)
解决方案
I was able to find a solution to this.  If you map the Bar class like so
@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 MiniBar class as if barId were the primary key, and then further make it available in the outer Bar class.

                        
这篇关于如何在辅助表中的非主键列上连接表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！