SpringBoot 3中JPQL转SQL异常：select new与right join列选择错误

问题概述 #

将SpringBoot从2.7版本迁移至3.1版本后，包含select new和right join的JPQL查询生成的SQL出现异常。这类查询用于从EntityE和EntityC两个实体创建MyProjection投影，要求即使EntityC不存在，投影中也必须包含EntityE的ID。但在3.1版本中，生成的SQL错误地选择了EntityC关联EntityE的外键而非EntityE自身的ID，导致当EntityC为空时，投影中的EntityE ID也变为null。

示例代码 #

EntityC实体类 #

@Entity
public class EntityC implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String reference;

  @ManyToOne(cascade = CascadeType.REFRESH, fetch = FetchType.LAZY)
  @JoinColumn(name = "entity_e_id")
  private EntityE entityE;
}

EntityE实体类 #

@Entity
public class EntityE implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
}

MyProjection投影类 #

public class MyProjection implements Serializable {

  private static final long serialVersionUID = -3431863476946276142L;

  private Long entityEId;
  private Long entityCId;

  private String entityEName;
  private String entityCReference;

  public MyProjection(
      Long entityEId,
      Long entityCId,
      String entityEName,
      String entityCReference) {
    this.entityEId = entityEId;
    this.entityCId = entityCId;
    this.entityEName = entityEName;
    this.entityCReference = entityCReference;
  }
  
  public MyProjection(
      Long entityEId,
      Long entityCId) {
    this.entityEId = entityCId;
    this.entityEId = entityCId;
  }

  
  public MyProjection() {}
}

JPQL查询语句 #

select new MyProjection(e.id, c.id, e.name, c.reference)
from EntityC c right join c.entityE e;

生成SQL对比 #

SpringBoot 2.7.x生成的SQL #

select e1_.id as col_0_0_, c0_.id as col_1_0_, e1_.name as col_2_0_, c0_.reference as col_3_0_
from entity_c c0_ 
right outer join entity_e e1_ on c0_.entity_e_id=e1_.id;

SpringBoot 3.1.x生成的SQL #

select c0_.entity_e_id as col_0_0_, c0_.id as col_1_0_, e1_.name as col_2_0_, c0_.reference as col_3_0_
from entity_c c0_ 
right join entity_e e1_ on c0_.entity_e_id=e1_.id;

已尝试的操作 #

• 切换至SpringBoot 3.2.0-SNAPSHOT版本，问题未解决
• 尝试降级Hibernate至5.x版本，降级失败

问题分析与解决方案 #

这是Hibernate 6.x（SpringBoot 3.x默认依赖版本）的查询优化Bug：在处理右连接场景下的select new投影时，Hibernate错误地将e.id替换为左表（EntityC）的外键字段c.entity_e_id，忽略了右连接中左表为空时右表字段仍需正常取值的逻辑。

临时修复方案 #

1. 强制指定EntityE ID的获取方式：修改JPQL查询，使用FUNCTION('IDENTITY', e)显式获取EntityE的ID，避免Hibernate的错误替换：

   select new MyProjection(FUNCTION('IDENTITY', e), c.id, e.name, c.reference)
   from EntityC c right join c.entityE e;
   

2. 调整查询关联方向：将右连接改为从EntityE出发的左连接，这样Hibernate会正确选取EntityE的ID。需要先在EntityE中添加关联关系：

   // 在EntityE类中添加
   @OneToMany(mappedBy = "entityE")
   private List<EntityC> entityCs;
   

   对应的JPQL查询修改为：

   select new MyProjection(e.id, c.id, e.name, c.reference)
   from EntityE e left join e.entityCs c;
   

内容的提问来源于stack exchange，提问作者Olivier