Why does Hibernate execute multiple SELECT queries instead of one when using @Fetch(FetchMode.JOIN)

Question

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.

Solution 1

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.

Solution 2

The secondary queries come from:

@ManyToOne(fetch=FetchType.EAGER)
@Fetch(FetchMode.JOIN)
@JoinColumn(name="team_id2")
public Team getTeam2() {
    return team2;
}

So, you need to:

1. Make all associations LAZY. By default, all @ManyToOne and @OneToOne associations are EAGER, so it's better to have them LAZY and only override the fetch plan on a query basis.

2. Remove the @Fetch(FetchMode.JOIN), which is essentially an EAGER fetch directive. In your case, not just the team2 property is fetched, but its players and skills as well.