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.

Can you please post the actual SQL being executed? What happens if you turn off lazy fetching? — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented Jan 14, 2015 at 4:27
@chrylis please take a look on edit. I can try turning off lazy fetching but later, though I need it to be set as lazy for other queries which should not fetch eagerly players. — maximus
– maximus, Commented Jan 14, 2015 at 4:47
Hibernate... making intelligent people execute stupid queries since 2001. Possibly time to ditch the Hibernate bloatware and switch to something closer to the metal :) — lance-java
– lance-java, Commented Jan 20, 2015 at 14:21
I just wanted to say something to Lance Java. I am going to ditch hibernate and use mybatis. good lord is hibernate making me real stupid. — Francis Zabala
– Francis Zabala, Commented Oct 23, 2015 at 5:05

outdev · Accepted Answer · 2015-01-20 21:47:31Z

44

+50

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.

answered Jan 20, 2015 at 21:47

outdev

5,5223 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gyan Over a year ago

@SubSelect is also a good option , when working with lazy fetches. stackoverflow.com/questions/5975752/…

epol Over a year ago

Perfect answer ! If entity has @OneToOne fields they must be fetched in the query. Even if the "@OneToOne" field is marked with FetchType.LAZY it is considered as EAGER when calling "@Query" : sounds like a bug / arbitrary design decision - is there a specific reason for this behaviour?

Vlad Mihalcea · Accepted Answer · 2018-01-05 17:26:59Z

7

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:

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

edited Jan 5, 2018 at 17:26

answered Jan 16, 2015 at 7:38

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

8 Comments

maximus Over a year ago

I did as you suggested, removed all FetchMode.JOIN and added LAZY to them, but there I have noticed two changes only: 1. team is loaded but different select is not done, 2. all players loaded without separate select for them. Which is good but for each player still loading it's stats and skills. I have checked several times, there are no EAGER loading for them, and no JOIN fetchMode.

Vlad Mihalcea Over a year ago

You need to explicitly set LAZY for OneToOne and ManyToOne too. Then use join fetch in your query.

maximus Over a year ago

I must do join fetch even if I don't need those entities to be loaded?

maximus Over a year ago

Sorry but seems like it still doesn't work. I made test only with that query. Before it was not loading due to may be previous fetches gone succesfully. Not sure. But even after changing as you mentioned in answer the code, it still loads team with select request went to db.

Joakim Over a year ago

@VladMihalcea has answered the question by suggestion lazy fetching, but I think many readers were expecting an answer showing how to tell Hibernate to fetch children using SQL left joins, which looks to be what the OP was trying to achieve.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related