为什么在使用@Fetch(FetchMode.JOIN)时，Hibernate为什么执行多个SELECT查询而不是一个查询 [英] Why does Hibernate execute multiple SELECT queries instead of one when using @Fetch(FetchMode.JOIN)

问题描述

我有以下查询，希望在单个选择请求中运行该查询:

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:

游戏实体:

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

团队实体:

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

玩家实体:

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.

这是postgresql日志(部分日志)，纯sql查询: http://pastebin.com/Fbsvmep6

EDIT 1: here is postgresql log (some part of it), pure sql queries: http://pastebin.com/Fbsvmep6

为简单起见，在此问题中更改了表的原始名称， 游戏是GamelistLeague，团队是TeamInfo，并且存在BatterStats和PitcherStats而不是一个PlayerStat

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.

推荐答案

您正在遇到一个众所周知的问题，也就是"N + 1选择". 简而言之，当您选择父实体时，会发生"N + 1选择"问题，并且休眠将使用OneToOne对与父相关的子项进行附加选择.因此，如果数据库中有"N"个父子记录，则hibernate将使所有父项具有一个选择，然后使每个子项处于单独的选择中，从而使N + 1个选择合计.
在休眠状态下，有两种解决"N + 1"问题的方法:
1.加入获取" 所有个一对一的孩子.
2.启用二级缓存，并在OneToOne子级上使用@Cache批注.

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.

您的问题是您没有加入提取"所有的OneToOne子代. 您必须加入"所有对象，包括可传递的子代(从子代本身或在集合中引用的实体).

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

使OneToOne懒惰(因为默认情况下它很渴望)只是部分解决方案，因为只有当您访问子代上的某个getter时，休眠才会为子代选择一个选项，但是从长远来看，它仍将使所有N个选择成为可能.

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.

这篇关于为什么在使用@Fetch(FetchMode.JOIN)时，Hibernate为什么执行多个SELECT查询而不是一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！