为什么 Hibernate 在使用 @Fetch(FetchMode.JOIN) 时执行多个 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.

编辑 1:这是 postgresql 日志(其中的一部分)，纯 sql 查询:http://pastebin.com/Fbsvmep6

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

为简单起见，此问题中更改了表的原始名称，Game 是 GamelistLeague，Team 是 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 项选择.
hibernate中N+1"问题有两种解决方法:
1.加入获取"所有 OneToOne 孩子.
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 变得懒惰(因为默认情况下它是急切的)只是部分解决方案，因为 hibernate 只会在您访问孩子的一些 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.

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