JPA @JoinTable - 三个ID列

问题描述

我的情况与此问题图中所列的情况十分相似：，但有不同的问题。

我有三个表：函数，组和位置。目前，我使用 @JoinTable在 Location 和 Group 之间建立了一个连接表。 code>。它是双方 @ManyToMany ，并且工作得很好。

我试图添加约束 Location 应与多个 Group 具有相同的功能。所以我在我的SQL模式中为我的连接表添加了一个用于 Function 的列，并在 Location 和 Function 列，如下所示：

  create table function_table（
 id varchar（50），
主键（id）
）; 
 
 create table group_table（
 id varchar（50），
 function_id varchar（50）not null，
 primary key（id）
）; 
 
 alter table group_table添加约束FK_TO_FUNCTION外键（function_id）引用function_table; 
 
创建表location_table（
 id varchar（50），
主键（id）
）; 
 
 create table group_location_join（
 location_id varchar（50）not null，
 group_id varchar（50）not null，
 function_id varchar（50）not null，
主键（location_id，group_id，function_id），
 unique（location_id，function_id）
）; 
 
 alter table group_location_join添加约束FK_TO_LOCATION外键（location_id）引用location_table; 
 alter table group_location_join添加约束FK_TO_GROUP外键（group_id）引用group_table; 
 alter table group_location_join添加约束FK_TO_FUNCTION外键（function_id）引用function_table; 
  

然后我尝试在我的模型实体中设置以下内容：

  @Entity 
 @Table（name =function_table）
 public class Function {
 @Id 
 @列（名称=ID，长度= 50）
私人字符串ID; 
 
 
 $ Entity 
 @Table（name =group_table）
 public class Group {
 @Id 
 @Column（name =id，长度= 50）
私人字符串ID; 
 
 @ManyToOne 
 @JoinColumn（name =function_id，referencedColumnName =id，nullable = false）
 private函数function; 
 
 @ManyToMany 
 @JoinTable（name =group_location_join，
 joinColumns = {@JoinColumn（name =group_id，referencedColumnName =id），
 @JoinColumn（name =function_id，referencedColumnName =function_id）}，
 inverseJoinColumns = @JoinColumn（name =location_id，referencedColumnName =id））
 private Set< Location>位置; 
 
 $ b $实体
 @Table（name =location_table）
 public class Location {
 @Id 
 @Column（name =id，长度= 50）
私人字符串ID; 
 
 @ManyToMany 
 @JoinTable（name =group_location_join，
 joinColumns = @JoinColumn（name =location_id，referencedColumnName =id）
 inverseJoinColumns = {@JoinColumn（name =group_id，referencedColumnName =id），
 @JoinColumn（name =function_id，referencedColumnName =function_id）}）
 private Set< Group>组; 
 
 $ / code> 

（很明显，这些实体还有更多，但我将它们分解为只有与这个问题有关的部分。）

这是行不通的。当我编写一个简单的测试来创建与 Group 时， Location >函数，我尝试刷新会话以提交事务，Hibernate给了我这个：

  java.lang.ClassCastException：my.package.Group不能转换为java.io.Serializable 
  

我认为发生的事情是，Hibernate越来越困惑，举起手来，并说：我只是序列化它，发送到数据库，并希望它知道发生了什么。

当我添加实现Serializable 并添加一个 serialVersionUID 到 Group ，然后我得到这个：

  org.hibernate.exception.SQLGrammarException：用户缺少权限或对象找不到：FUNCTION_ID 
  

我现在还不确定该怎么做，或者也许我已经在错误的道路上走得太远了。也许我没有正确地思考SQL，并且有一种更简单的方法来确保这种不涉及所有这些荒谬的限制。

编辑：在我的系统中，涉及表的DAO没有保存功能。这意味着只要我的约束被设置在数据库中，我的应用程序就不会在意;它不能插入违反约束条件的东西，因为它根本不能插入东西。

编辑2：我从来没有解决过如我在上面的第一个编辑部分中所述，只需在我的数据库模式中添加第三列而不触及Java代码。但是我从此尝试过使用 @Embedded 复合键创建一个显式连接表对象，并且它似乎可行。

解决方案

您正尝试创建复合主键。在Hibernate中，你可以使用 @Embeddable 注释来完成它。在下面的示例中，您可以找到为两个实体使用组合键的方法。

我相信你可以继续使用这个例子，并创建你自己的主键。

I have a situation that is quite similar to the one outlined in this question's diagram: JPA. JoinTable and two JoinColumns, although with different issues.

I have three tables: Function, Group, and Location. Currently, I have a join table set up between Location and Group using @JoinTable. It is @ManyToMany on both sides, and works perfectly fine.

I am attempting to add the constraint that no Location should be associated with more than one Group that has the same Function. So I added a column for Function to my join table in my SQL schema and a uniqueness constraint across the Location and Function columns, like so:

create table function_table (
  id varchar(50),
  primary key(id)
);

create table group_table (
  id varchar(50),
  function_id varchar(50) not null,
  primary key(id)
);

alter table group_table add constraint FK_TO_FUNCTION foreign key (function_id) references function_table;

create table location_table (
  id varchar(50),
  primary key(id)
);

create table group_location_join (
  location_id varchar(50) not null,
  group_id varchar(50) not null,
  function_id varchar(50) not null,
  primary key(location_id, group_id, function_id),
  unique(location_id, function_id)
);

alter table group_location_join add constraint FK_TO_LOCATION foreign key (location_id) references location_table;
alter table group_location_join add constraint FK_TO_GROUP foreign key (group_id) references group_table;
alter table group_location_join add constraint FK_TO_FUNCTION foreign key (function_id) references function_table;

I then attempted to set up the following in my model entities:

@Entity
@Table(name = "function_table")
public class Function {
  @Id
  @Column(name = "id", length = 50)
  private String id;
}

@Entity
@Table(name = "group_table")
public class Group {
  @Id
  @Column(name = "id", length = 50)
  private String id;

  @ManyToOne
  @JoinColumn(name = "function_id", referencedColumnName = "id", nullable = false)
  private Function function;

  @ManyToMany
  @JoinTable(name = "group_location_join",
    joinColumns = {@JoinColumn(name = "group_id", referencedColumnName = "id"),
                   @JoinColumn(name = "function_id", referencedColumnName = "function_id")},
    inverseJoinColumns = @JoinColumn(name="location_id", referencedColumnName = "id"))
  private Set<Location> locations;
}

@Entity
@Table(name = "location_table")
public class Location {
  @Id
  @Column(name = "id", length = 50)
  private String id;

  @ManyToMany
  @JoinTable(name = "group_location_join",
    joinColumns = @JoinColumn(name="location_id", referencedColumnName = "id")
    inverseJoinColumns = {@JoinColumn(name = "group_id", referencedColumnName = "id"),
                   @JoinColumn(name = "function_id", referencedColumnName = "function_id")})
  private Set<Group> groups;
}

(Obviously, there is more to these entities, but I stripped them down to only the parts relevant to this question.)

This does not work. When I write a simple test to create a Location associated with a Group that is associated with a Function, the minute I try to flush the session to commit the transaction, Hibernate gives me this:

java.lang.ClassCastException: my.package.Group cannot be cast to java.io.Serializable

I think what's happening is that Hibernate is getting confused, throwing up its hands, and saying "I'll just serialize it, send it to the database, and hope it knows what's going on."

When I add implements Serializable and add a serialVersionUID to Group, I then get this:

org.hibernate.exception.SQLGrammarException: user lacks privilege or object not found: FUNCTION_ID

I'm not really sure how to proceed at this point, or if perhaps I have already proceeded too far down the wrong path. Maybe I'm not thinking about the SQL correctly, and there is a much easier way to ensure this constraint that doesn't involve all this ridiculousness.

Edit: In my system, the DAOs for the tables involved have no save capabilities. Which means that as long as my constraint is set up in the database, my application doesn't care; it can't insert things that violate the constraint because it can't insert things at all.

Edit 2: I never originally solved the stated problem, and instead simply added a third column in my database schema without touching the Java code, as stated in my first Edit section above. But I have since experimented with creating an explicit join table object with an @Embedded compound key, and it seems to work.

解决方案

You are trying to create a composite primary key. In Hibernate you can do it using the @Embeddable annotation. In the example below you can find the way to use a composite key for two entities.

I believe you can move forward with this example and create your own version of primary key.

Mapping ManyToMany with composite Primary key and Annotation:

这篇关于JPA @JoinTable - 三个ID列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！