Hibernate自引用实体查询报错，请求排查代码问题

场景与代码 #

现有两张数据表medicaments_group和medicaments_group_join，实体类MedicamentGroup继承自GenericDictionary，通过@ManyToMany实现自关联，定义了childrens（子分组）和childrenOf（父分组）两个属性：

@Entity 
@Table(name = "medicaments_group") 
@Getter @Setter 
public class MedicamentGroup extends GenericDictionary {

    @Id
    private Long id;

    private boolean groupMain;

    @ManyToMany
    @JoinTable(
            name = "medicaments_group_join",
            joinColumns = @JoinColumn(name = "medicament_group_id"),
            inverseJoinColumns = @JoinColumn(name = "medicament_join_id")
    )
    private List<MedicamentGroup> childrens = new ArrayList<>();

    @ManyToMany
    @JoinTable(
            name = "medicaments_group_join",
            joinColumns = @JoinColumn(name = "medicament_join_id"),
            inverseJoinColumns = @JoinColumn(name = "medicament_group_id")
    )
    private List<MedicamentGroup> childrenOf; 
}

为获取包含子实体列表的MedicamentGroup列表，编写了以下Querydsl查询方法：

@Override
public List<MedicamentGroup> getGroupsAndItsChildren() {
    List<Tuple> query = Objects.requireNonNull(getQuerydsl())
            .createQuery()
            .from(medicamentGroup)
            .select(id, shortName, childrens)
            .where(groupMain.isFalse())
            .fetch();

    return query.stream().map(tuple -> {
        MedicamentGroup group = new MedicamentGroup();
        group.setId(tuple.get(id));
        group.setShortName(tuple.get(shortName));
        group.setChildrens(tuple.get(childrens));
        return group;
    }).toList();
}

调用该方法时触发SQL语法错误，报错信息如下：

Hibernate: select medicament0_.id as col_0_0_, medicament0_.short_name as col_1_0_, . as col_2_0_, medicament2_.id as id1_20_, medicament2_.active as active2_20_, medicament2_.name as name3_20_, medicament2_.short_name as short_na4_20_, medicament2_.group_main as group_ma5_20_ from medicaments_group medicament0_ inner join medicaments_group_join childrens1_ on medicament0_.id=childrens1_.medicament_group_id inner join medicaments_group medicament2_ on childrens1_.medicament_join_id=medicament2_.id where medicament0_.group_main=?
2023-04-03 20:12:23.563 WARN 46674 --- [nio-8089-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42601
2023-04-03 20:12:23.563 ERROR 46674 --- [nio-8089-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: syntax error at or near "." Position: 74
2023-04-03 20:12:23.579 ERROR 46674 --- [nio-8089-exec-3] p.k.c.w.f.ErrorResponseFactoryImpl : Error response
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet ...

错误原因 #

问题出在Querydsl的select(id, shortName, childrens)语句中：childrens是@ManyToMany关联的集合属性，Querydsl无法直接将集合作为单独的SELECT列进行查询，导致生成的SQL出现了无效的.占位符（对应报错中的as col_2_0_, .），最终触发语法错误。

解决方法 #

方法1：使用Fetch Join直接加载关联实体 #

通过Fetch Join一次性加载主实体和关联的子实体，避免手动映射Tuple，同时解决N+1查询问题：

@Override
public List<MedicamentGroup> getGroupsAndItsChildren() {
    return Objects.requireNonNull(getQuerydsl())
            .createQuery()
            .from(medicamentGroup)
            .leftJoin(medicamentGroup.childrens, childrens)
            .fetchJoin() // 启用Fetch Join加载子实体
            .where(medicamentGroup.groupMain.isFalse())
            .distinct() // 避免主实体重复（关联查询会产生笛卡尔积）
            .fetch();
}

方法2：使用DTO投影（自定义返回字段场景） #

如果不需要完整的MedicamentGroup实体，可以定义DTO类，通过构造函数投影包含子实体信息：

先定义DTO：

public record GroupWithChildrenDTO(Long id, String shortName, List<MedicamentGroup> children) {}

再修改查询：

@Override
public List<GroupWithChildrenDTO> getGroupsAndItsChildren() {
    return Objects.requireNonNull(getQuerydsl())
            .createQuery()
            .select(Projections.constructor(GroupWithChildrenDTO.class,
                    medicamentGroup.id,
                    medicamentGroup.shortName,
                    JPAExpressions.selectFrom(childrens)
                            .where(childrens.childrenOf.any().id.eq(medicamentGroup.id))))
            .from(medicamentGroup)
            .where(medicamentGroup.groupMain.isFalse())
            .fetch();
}

方法3：调整原查询逻辑（不推荐，存在N+1性能问题） #

如果坚持使用Tuple映射，需单独查询子实体，但这种方式会触发N+1查询，性能较差：

@Override
public List<MedicamentGroup> getGroupsAndItsChildren() {
    // 先查询主分组
    List<MedicamentGroup> mainGroups = Objects.requireNonNull(getQuerydsl())
            .createQuery()
            .from(medicamentGroup)
            .where(medicamentGroup.groupMain.isFalse())
            .fetch();
    
    // 为每个主分组查询子实体
    mainGroups.forEach(group -> {
        List<MedicamentGroup> children = Objects.requireNonNull(getQuerydsl())
                .createQuery()
                .from(childrens)
                .where(childrens.childrenOf.any().id.eq(group.getId()))
                .fetch();
        group.setChildrens(children);
    });
    
    return mainGroups;
}

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