Hibernate正在做多个选择请求，而不是一个（使用联合提取） [英] Hibernate is doing multiple select requests instead one (using join fetch)
问题描述
我有以下查询，我希望在单个select请求中运行：
@NamedQuery（name = Game.GET_GAME_BY_ID1，
query =SELECT g FROM Game G+
JOIN FETCH g.team1 t1+
JOIN FETCH t1.players p1+
JOIN FETCH p1.playerSkill skill1+
其中g.id =：id）
问题是一切都是由单独的多个查询获取的。
我只希望团队和球队的球员以及每位球员的技能都能在一次请求中获得。相反，我有多个选择查询来获取每个团队，玩家，每个玩家的统计数据和技能。
以下是用于注释的实体：
游戏实体
public class Game implements Serializable {
私人整数ID;
private Integer dayNumber;
私人长日期;
私人整数分数1;
私人整数分数2;
私人团队team1;
私人团队team2;
$ b $ ...
@ManyToOne（fetch = FetchType.EAGER）
@Fetch（FetchMode.JOIN）
@JoinColumn（name = team_id1）
public Team getTeam1（）{
return team1;
}
public void setTeam1（Team team1）{
this.team1 = team1;
}
//单向多对一关联到Team
@ManyToOne（fetch = FetchType.EAGER）
@Fetch（FetchMode.JOIN）
@JoinColumn（name =team_id2）
public Team getTeam2（）{
return team2;
}
public void setTeam2（Team team2）{
this.team2 = team2;
}
}
团队实体：
public class Team implements Serializable {
...
private Set< Player>玩家;
...
@OneToMany（mappedBy =team，targetEntity = Player.class，fetch = FetchType.LAZY，cascade = CascadeType.ALL）
@Fetch（FetchMode.JOIN）
@OrderBy（value =batOrder，pitRotationNumber ASC）
public Set< Player> getPlayers（）{
返回玩家;
}
public void setPlayers（Set& Player> players）{
this.players = players;
}
}
玩家实体：
public class Player实现Serializable {
private PlayerStat playerStats;
私人PlayerSkill playerSkill;
...
@OneToOne（mappedBy =player，cascade = CascadeType.ALL）
@Fetch（FetchMode.JOIN）
public PlayerStat getPlayerStats（）{
返回this.playerStats;
}
public void setPlayerStats（PlayerStat playerStats）{
this.PlayerStats = playerStats;
$ b @OneToOne（mappedBy =player，fetch = FetchType.LAZY，cascade = CascadeType.ALL）
@ Fetch（FetchMode.JOIN）
public PlayerSkill getPlayerSkill（）{
return this.playerSkill;
}
public void setPlayerSkill（PlayerSkill playerSkill）{
this.playerSkill = playerSkill;
}
}
您可以指出所犯的错误吗？
我需要一个选择查询来加载游戏，它是团队，团队的玩家和每个玩家的技能。
编辑1：
这里是postgresql日志（它的一部分），纯sql查询：
http://pastebin.com/Fbsvmep6
为简单起见，表格的原始名称已更改，
游戏是GamelistLeague，Team是TeamInfo，并且有BatterStats和PitcherStats而不是一个PlayerStat
来自日志的第一个查询是上面这个问题（命名查询）中显示的查询，如果我直接在数据库中执行它，则会根据需要返回所有内容。
您正在经历一个众所周知的问题，即N + 1选择。
简而言之，当您选择一个父实体时，会出现N + 1选择问题，并且休眠会为与OneToOne的父级相关的子级额外选择。因此，如果数据库中有N个父子记录，hibernate将获得所有父母的一个选择，然后让每个孩子分开选择，从而产生总共N + 1个选择。
有两种方法对于Hibernate中的N + 1问题：
1.加入Fetch全部 OneToOne子项。
2.启用二级缓存并使用OneToOne儿童的@Cache注释
你的问题是你没有加入获取所有的OneToOne儿童。
您必须联合提取它们，包括可传递的子项（从子项本身或集合中引用的实体）。
使OneToOne懒惰（因为默认情况下它是渴望的）只是部分解决方案，因为只有当你访问子节点上的某个getter时，hibernate才会为子节点选择，从长远来看，它仍然会进行所有N次选择。
I've got the following query which I expect to run in a single select request:
@NamedQuery(name=Game.GET_GAME_BY_ID1,
query = "SELECT g FROM Game g " +
"JOIN FETCH g.team1 t1 " +
"JOIN FETCH t1.players p1 " +
"JOIN FETCH p1.playerSkill skill1 " +
"where g.id=:id")
The problem is that everything is fetched by separate multiple queries. I want only Team and team's players and each player's skills to be fetched in a single request. But instead I've got multiple select queries for fetching each team, player, each player's stats and skills.
Here are entities used with annotations given:
Game Entity:
public class Game implements Serializable {
private Integer id;
private Integer dayNumber;
private Long date;
private Integer score1;
private Integer score2;
private Team team1;
private Team team2;
....
@ManyToOne(fetch=FetchType.EAGER)
@Fetch(FetchMode.JOIN)
@JoinColumn(name="team_id1")
public Team getTeam1() {
return team1;
}
public void setTeam1(Team team1) {
this.team1 = team1;
}
// uni directional many to one association to Team
@ManyToOne(fetch=FetchType.EAGER)
@Fetch(FetchMode.JOIN)
@JoinColumn(name="team_id2")
public Team getTeam2() {
return team2;
}
public void setTeam2(Team team2) {
this.team2 = team2;
}
}
Team Entity:
public class Team implements Serializable {
...
private Set<Player> players;
...
@OneToMany(mappedBy="team", targetEntity=Player.class, fetch=FetchType.LAZY, cascade=CascadeType.ALL)
@Fetch(FetchMode.JOIN)
@OrderBy(value="batOrder, pitRotationNumber ASC")
public Set<Player> getPlayers() {
return players;
}
public void setPlayers(Set<Player> players) {
this.players = players;
}
}
Player Entity:
public class Player implements Serializable {
private PlayerStat playerStats;
private PlayerSkill playerSkill;
...
@OneToOne(mappedBy="player", cascade=CascadeType.ALL)
@Fetch(FetchMode.JOIN)
public PlayerStat getPlayerStats() {
return this.playerStats;
}
public void setPlayerStats(PlayerStat playerStats) {
this.PlayerStats = playerStats;
}
...
@OneToOne(mappedBy="player", fetch=FetchType.LAZY, cascade=CascadeType.ALL)
@Fetch(FetchMode.JOIN)
public PlayerSkill getPlayerSkill() {
return this.playerSkill;
}
public void setPlayerSkill(PlayerSkill playerSkill) {
this.playerSkill = playerSkill;
}
}
Could you point on the mistakes made? I need one select query to load game, it's teams, team's players and each player's skills.
EDIT 1: here is postgresql log (some part of it), pure sql queries: http://pastebin.com/Fbsvmep6
Original names of tables are changed in this question for simplicity, Game is GamelistLeague, Team is TeamInfo, and there are BatterStats and PitcherStats instead of one PlayerStat
The first query from the logs is the one shown in this question above (named query) which, if I execute it directly in database, returns everything as needed.
You are experiencing a well known problem, a.k.a. the "N+1 selects".
In short, the "N+1 selects" problem occurs when you select a parent entity and hibernate will make additional select for a child related to the parent with OneToOne. So if you have "N" parent-child records in the database, hibernate will get all parents with one select and then get each child in separated select, making total N+1 selects.
There are two approaches for "N+1" problem in hibernate:
1. "Join Fetch" all OneToOne children.
2. Enable the second level cache and use @Cache annotation on the OneToOne children.
Your problem is that you didn't "join fetch" all of the OneToOne children. You must "join fetch" them all, including the transitive children (entities referenced from children themselves, or within the collection).
Making OneToOne lazy (because its eager by default) is only partial solution, because hibernate will make a select for a child only when you access some getter on the child, but in long term it will still make all the N selects.
这篇关于Hibernate正在做多个选择请求，而不是一个（使用联合提取）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
