OneToMany映射在findById/findAll期间生成错误的hibernate SQL

Question

我无法使以下OneToMany映射正常工作，即使它们应该经过验证(通过hibernate.ddl auto=validate(。我可以在应用程序中插入所有实体，但在执行findAll或findById时，hibernate为我生成的查询是错误的，并导致异常。这很可能是由于我的OneToMany映射有问题，或者缺乏ManyToOne映射，但我不知道如何使其工作。

目前，我的postgres12数据库中存在以下表格：

CREATE TABLE battlegroups (
id uuid,
gameworld_id uuid,
name varchar(255),
PRIMARY KEY(id)
);
CREATE TABLE battlegroup_players (
id uuid,
battlegroup_id uuid,
player_id integer,
name varchar(255),
tribe varchar(255),
PRIMARY KEY (id)
);
CREATE TABLE battlegroup_player_villages(
battlegroup_id uuid,
player_id integer,
village_id integer,
x integer,
y integer,
village_name varchar(255),
tribe varchar(255),
PRIMARY KEY(battlegroup_id, player_id, village_id, x, y)
);

这些被映射到Kotlin中的以下实体：

@Entity
@Table(name = "battlegroups")
class BattlegroupEntity(
@Id
val id: UUID,
@Column(name = "gameworld_id")
val gameworldId: UUID,
val name: String? = "",
@OneToMany(mappedBy = "battlegroupId", cascade = [CascadeType.ALL],fetch = FetchType.EAGER)
private val players: MutableList<BattlegroupPlayerEntity>) 
@Entity
@Table(name = "battlegroup_players")
class BattlegroupPlayerEntity(@Id
val id: UUID,
@Column(name = "battlegroup_id")
val battlegroupId: UUID,
@Column(name = "player_id")
val playerId: Int,
val name: String,
@Enumerated(EnumType.STRING)
val tribe: Tribe,
@OneToMany(mappedBy= "id.playerId" , cascade = [CascadeType.ALL], fetch = FetchType.EAGER)
val battlegroupPlayerVillages: MutableList<BattlegroupPlayerVillageEntity>) 
@Entity
@Table(name = "battlegroup_player_villages")
class BattlegroupPlayerVillageEntity(
@EmbeddedId
val id: BattlegroupPlayerVillageId,
@Column(name ="village_name")
val villageName: String,
@Enumerated(EnumType.STRING)
val tribe: Tribe) 
@Embeddable
data class BattlegroupPlayerVillageId(
@Column(name = "battlegroup_id")
val battlegroupId: UUID,
@Column(name = "player_id")
val playerId: Int,
@Column(name = "village_id")
val villageId: Int,
val x: Int,
val y: Int
): Serializable

这是SQL hibernate在战斗组上执行findAll/findById时生成的：

select
battlegrou0_.id as id1_2_0_,
battlegrou0_.gameworld_id as gameworl2_2_0_,
battlegrou0_.name as name3_2_0_,
players1_.battlegroup_id as battlegr2_1_1_,
players1_.id as id1_1_1_,
players1_.id as id1_1_2_,
players1_.battlegroup_id as battlegr2_1_2_,
players1_.name as name3_1_2_,
players1_.player_id as player_i4_1_2_,
players1_.tribe as tribe5_1_2_,
battlegrou2_.player_id as player_i2_0_3_,
battlegrou2_.battlegroup_id as battlegr1_0_3_,
battlegrou2_.village_id as village_3_0_3_,
battlegrou2_.x as x4_0_3_,
battlegrou2_.y as y5_0_3_,
battlegrou2_.battlegroup_id as battlegr1_0_4_,
battlegrou2_.player_id as player_i2_0_4_,
battlegrou2_.village_id as village_3_0_4_,
battlegrou2_.x as x4_0_4_,
battlegrou2_.y as y5_0_4_,
battlegrou2_.tribe as tribe6_0_4_,
battlegrou2_.village_name as village_7_0_4_ 
from
battlegroups battlegrou0_ 
left outer join
battlegroup_players players1_ 
on battlegrou0_.id=players1_.battlegroup_id 
left outer join
battlegroup_player_villages battlegrou2_ 
on players1_.id=battlegrou2_.player_id -- ERROR: comparing integer to uuid
where
battlegrou0_.id=?

这导致了一个异常：

PSQL异常：错误：运算符不存在：integer=uuid

这是完全合理的，因为它将战斗组玩家id(uuid(与战斗组玩家_村庄玩家id(整数(进行比较。相反，它应该比较/加入战群玩家的player_id和战群玩家村庄的player_id。

如果我更改sql以反映这一点，并手动执行上面的查询，并替换错误行：

on players1_.player_id=battlegrou2_.player_id 

我得到了我想要的结果。我如何更改OneToMany映射，使其能够做到这一点？在我的BattlegroupPlayerVillageEntity类中没有BattlegroupPlayerEntity对象的情况下，有可能做到这一点吗？

如果你能让左边的外部联接变成常规的内部联接，就可以获得额外的积分。

编辑：

我尝试了当前的答案，因为我的代码无法编译，所以不得不稍微调整我的嵌入id，应该是一样的：

@Embeddable
data class BattlegroupPlayerVillageId(
@Column(name = "battlegroup_id")
val battlegroupId: UUID,
@Column(name = "village_id")
val villageId: Int,
val x: Int,
val y: Int
): Serializable {
@ManyToOne
@JoinColumn(name = "player_id")
var player: BattlegroupPlayerEntity? = null
}

出于某种原因，使用它仍然会导致int和uuid之间的比较。

Schema-validation: wrong column type encountered in column [player_id] in table [battlegroup_player_villages]; found [int4 (Types#INTEGER)], but expecting [uuid (Types#OTHER)]

有趣的是，如果我试图在其中放入referencedColumnName = "player_id"，我会得到一个stackerflow错误。

Answer 1

我做了一些挖掘，发现了映射和类的一些问题，我会尽可能多地解释。

警告！！！TL；DR

我将使用Java编写代码，我希望转换为kotlin不会有问题。

类也存在一些问题(提示：Serializable(，因此类必须实现Serializable。

使用lombok减少样板

这是更改后的BattleGroupPlayer实体：

@Entity
@Getter
@NoArgsConstructor
@Table(name = "battle_group")
public class BattleGroup implements Serializable {
private static final long serialVersionUID = 6396336405158170608L;
@Id
private UUID id;
private String name;
@OneToMany(mappedBy = "battleGroupId", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private List<BattleGroupPlayer> players = new ArrayList();
public BattleGroup(UUID id, String name) {
this.id = id;
this.name = name;
}
public void addPlayer(BattleGroupPlayer player) {
players.add(player);
}
}

以及BattleGroupVillage和BattleGroupvilageId实体

@AllArgsConstructor
@Entity
@Getter
@NoArgsConstructor
@Table(name = "battle_group_village")
public class BattleGroupVillage implements Serializable {
private static final long serialVersionUID = -4928557296423893476L;
@EmbeddedId
private BattleGroupVillageId id;
private String name;
}

@Embeddable
@EqualsAndHashCode
@Getter
@NoArgsConstructor
public class BattleGroupVillageId implements Serializable {
private static final long serialVersionUID = -6375405007868923427L;
@Column(name = "battle_group_id")
private UUID battleGroupId;
@Column(name = "player_id")
private Integer playerId;
@Column(name = "village_id")
private Integer villageId;
public BattleGroupVillageId(UUID battleGroupId, Integer playerId, Integer villageId) {
this.battleGroupId = battleGroupId;
this.villageId = villageId;
this.playerId = playerId;
}
}

现在，Serializable需要在每个类中实现，因为我们使用了@EmbeddedId，它也要求容器类是Serializable的，因此每个父类都必须实现Serializable，否则会出错。

现在，我们可以使用@JoinColumn注释来解决这个问题，如下所示：

@OneToMany(cascade = CasacadeType.ALL, fetch =EAGER)
@JoinColumn(name = "player_id", referencedColumnName = "player_id")
private List<BattleGroupVillage> villages = new ArrayList<>();

名称->子表中的字段和referenceColumnName->父表中的字段。

这将加入两个实体中的列player_id列。

SELECT 
battlegrou0_.id AS id1_0_0_,
battlegrou0_.name AS name2_0_0_,
players1_.battle_group_id AS battle_g2_1_1_,
players1_.id AS id1_1_1_,
players1_.id AS id1_1_2_,
players1_.battle_group_id AS battle_g2_1_2_,
players1_.player_id AS player_i3_1_2_,
villages2_.player_id AS player_i4_2_3_,
villages2_.battle_group_id AS battle_g1_2_3_,
villages2_.village_id AS village_2_2_3_,
villages2_.battle_group_id AS battle_g1_2_4_,
villages2_.player_id AS player_i4_2_4_,
villages2_.village_id AS village_2_2_4_,
villages2_.name AS name3_2_4_
FROM
battle_group battlegrou0_
LEFT OUTER JOIN
battle_group_player players1_ ON battlegrou0_.id = players1_.battle_group_id
LEFT OUTER JOIN
battle_group_village villages2_ ON players1_.player_id = villages2_.player_id
WHERE
battlegrou0_.id = 1;

但如果你检查BattleGroup#getPlayers()方法，这将给2个玩家，下面是要验证的测试用例。

UUID battleGroupId = UUID.randomUUID();
doInTransaction( em -> {
BattleGroupPlayer player = new BattleGroupPlayer(UUID.randomUUID(), battleGroupId, 1);
BattleGroupVillageId villageId1 = new BattleGroupVillageId(
battleGroupId,
1,
1
);
BattleGroupVillageId villageId2 = new BattleGroupVillageId(
battleGroupId,
1,
2
);
BattleGroupVillage village1 = new BattleGroupVillage(villageId1, "Village 1");
BattleGroupVillage village2 = new BattleGroupVillage(villageId2, "Village 2");
player.addVillage(village1);
player.addVillage(village2);
BattleGroup battleGroup = new BattleGroup(battleGroupId, "Takeshi Castle");
battleGroup.addPlayer(player);
em.persist(battleGroup);
});
doInTransaction( em -> {
BattleGroup battleGroup = em.find(BattleGroup.class, battleGroupId);
assertNotNull(battleGroup);
assertEquals(2, battleGroup.getPlayers().size());
BattleGroupPlayer player = battleGroup.getPlayers().get(0);
assertEquals(2, player.getVillages().size());
});

如果你的用例是从BattleGroup中获得单个播放器，那么你必须使用FETCH.LAZY，这对性能也有好处。

为什么LAZY有效？

因为当您真正访问它们时，LAZY加载将发出单独的select语句。EAGER将加载整个图，无论你在哪里。这意味着，它将尝试加载用该类型映射的所有关系，因此它将执行外部联接(这可能会导致玩家有两行，因为你的条件是唯一的，因为villageId，在查询之前你无法知道(。

如果你有超过1个这样的字段，即也想加入战列组ID，你需要这个

@JoinColumns({
@JoinColumn(name = "player_id", referencedColumnName = "player_id"),
@JoinColumn(name = "battle_group_id", referencedColumnName = "battle_group_id")
}
)

注意：在测试用例的内存数据库中使用h2