Why does this result in non-lazy fetching and N+1 select statements?

Question

I expect to get only one sql query, but I run into the N+1 select trap. I don't really understand why. Here is is the problem in detail:

I have an entity "PlayerRef":

@Entity
@Table(name = "player_ref")
public class PlayerRef {

  //constructor etc...

  @OptimisticLock(excluded = true)
  @OneToMany(fetch = FetchType.LAZY, mappedBy = "playerRef")
  public Set<Player> getPlayers() {
    return players;
  }

}

And a class Player:

@Entity
@Table(name = "player")
public class Player {

  //constructor etc...

  @OptimisticLock(excluded = true)
  @ManyToOne(optional = true, fetch = FetchType.LAZY)
    @JoinTable(name="cr_player_ref_player", 
      joinColumns = {
        @JoinColumn(name="player_id", unique = true)           
      }
      ,inverseJoinColumns = {
        @JoinColumn(name="player_ref_id")
      }     
    )
  public PlayerRef getPlayerRef() {
    return this.playerRef;
  }
}

Now, in my program I use the following HQL query to get all playerRef entities:

Query playerRefQ = session.createQuery("select playerRef from PlayerRef playerRef ")
  .setReadOnly(true); 
playerRefQ.setParameter("sport", sport);
@SuppressWarnings("unchecked")
List<PlayerRef> allPlayerRefs = playerRefQ.list();

This results in the N+1 Select statements:

1)

select
    playerref0_.id as id1_21_,
    playerref0_.... 
from
    player_ref playerref0_

N times)

select
    players0_.player_ref_id as player_r1_21_0_,
    players0_.player_id as player_i2_34_0_,
    player1_.id as id1_19_1_,
    player1_....,
    player1_1_.player_ref_id as player_r1_34_1_,
    ... 
from
    cr_player_ref_player players0_ 
inner join
    player player1_ 
        on players0_.player_id=player1_.id 
left outer join
    cr_player_ref_player player1_1_ 
        on player1_.id=player1_1_.player_id 
where
    players0_.player_ref_id=?

This is very unexpected, since I thought the collection is lazy loaded and the set of players of each playerRef should be a hibernate-proxy.

Anyone knows how I can really only load the playerRef entities without also loading the associated players? For my use case I need all playerRefs but not the associated players.

Radim Köhler Radim Köhler · Accepted Answer · 2014-07-27T15:26:47

The problem here comes from the fact, that your underlying DB structure is in fact many-to-many. There is a pairing table cr_player_ref_player, and that means, that

one Player can have many PlayerRef
one PlayerRef can have many Player

See Hibernate – Many-to-Many example

because instead of this:

public PlayerRef getPlayerRef() {
    return this.playerRef;
}

there should be:

public Set<PlayerRef> getPlayerRefs() {
    return playerRef;
}

Confusion for Hibernate comes from this: @OneToMany(fetch = FetchType.LAZY, mappedBy = "playerRef"), mainly that the mapping will be found under the "playerRef"... where instead of expected many-to-one is much more complex and partially expressed many-to-many. And that's why we can see these wierd N+1...

Why does this result in non-lazy fetching and N+1 select statements?

1 Answers