java - 为什么这会导致非延迟获取和N + 1选择语句？

我期望仅获得一个sql查询，但遇到N + 1选择陷阱。我真的不明白为什么。这是详细的问题:

我有一个实体“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;
  }

}

和一个 class 球员:

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

现在，在我的程序中，我使用以下HQL查询来获取所有playerRef实体:

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

这将导致N + 1个Select语句:

1)

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

N次)

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=?

这是非常出乎意料的，因为我认为该集合是延迟加载的，并且每个playerRef的播放器集应该是一个休眠代理。

有谁知道我真的只能加载playerRef实体而又不加载关联的玩家？对于我的用例，我需要所有playerRefs，但不需要关联的players。

最佳答案

这里的问题来自以下事实:您的基础数据库结构实际上是many-to-many。有一个配对表cr_player_ref_player，这意味着，

一个Player可以有许多PlayerRef

一个PlayerRef可以有许多Player

参见Hibernate – Many-to-Many example

因为不是这样:

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

应该有:

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

Hibernate的混淆来自于此:@OneToMany(fetch = FetchType.LAZY, mappedBy = "playerRef")，主要是映射将在"playerRef"下找到...在这里，与预期的many-to-one相比，它要复杂得多，并且部分表达了many-to-many。这就是为什么我们可以看到这些奇怪的N + 1 ...