如何在辅助表中的非主键列上联接表?

我遇到一种情况,我需要在ORM类层次结构中的对象上联接表,其中join列不是基类的主键。这是表格设计的示例:

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.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;

}

BAR_NAMES鉴于其连接列不是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"))

List<String> names = new ArrayList<String>();

这将失败,因为用于检索Bar对象的SQL试图从FOO表中获取BAR_ID值。我也尝试过将JoinTable注释替换为

@JoinTable(name = "BAR_NAMES", joinColumns = @JoinColumn(name = "BAR_ID"))

这不会产生SQL错误,也不会检索任何数据,因为针对BAR_NAMES的查询使用FOO_ID作为联接值而不是BAR_ID。

为了进行测试,我使用以下命令填充了数据库

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');

当获得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 = "select BAR_ID from BAR", resultSetMapping = "compositekey")

@Table(name = "BAR")

public class MiniBar {

@Id

@Column(name = "BAR_ID")

Long barId;

}

然后,您可以将想要的任何类型的映射添加到MiniBar类中,就像barId是主键一样,然后进一步使其在外部Bar类中可用。

以上是 如何在辅助表中的非主键列上联接表? 的全部内容, 来源链接: utcz.com/qa/398504.html

回到顶部