基于Quarkus+QueryDSL+Blaze Persistence实现左连接子查询映射实体

需求说明 #

需将以下SQL转换为Quarkus结合QueryDSL/Blaze Persistence的代码，并完成结果映射：

select * from sys_region as sr 
left join 
(select count(*) as c,parent_code from sys_region group by parent_code ) 
as c on c.parent_code = sr.parent_code

对应实体类SysRegion：

@Getter
@Setter
@CTE
public class SysRegion extends BaseIdEntity {

  @Column(length = 128)
  private String name;

  @Column
  private Short level;

  @Column
  @ColumnDefault("0")
  private BigInteger parentCode;

  @Column
  @ColumnDefault("0")
  private BigInteger areaCode;

  @Column
  private String cityCode;

  @Column
  private Integer zipCode;

  @Column
  @ColumnDefault("0.0")
  private BigDecimal lng;
  
  @Column
  @ColumnDefault("0.0")
  private BigDecimal lat;

  @Column
  private String shortName;

  @Column
  private String mergerName;
}

步骤1：定义结果DTO #

原实体无统计字段，需创建DTO承载完整查询结果：

@Getter
@Setter
public class SysRegionWithChildCount {
    // 继承自BaseIdEntity的字段
    private Long id;
    // SysRegion原有字段
    private String name;
    private Short level;
    private BigInteger parentCode;
    private BigInteger areaCode;
    private String cityCode;
    private Integer zipCode;
    private BigDecimal lng;
    private BigDecimal lat;
    private String shortName;
    private String mergerName;
    // 子节点统计数
    private Long childCount;
}

方式1：Blaze Persistence实现 #

子查询关联写法 #

@Inject
EntityManager em;
@Inject
CriteriaBuilderFactory cbf;

public List<SysRegionWithChildCount> getRegionsWithChildCount() {
    // 构建子查询：统计每个父节点的子节点数量
    CommonQueryBuilder<?> subQuery = cbf.create(em, SysRegion.class)
            .select("COUNT(id)", "childCount")
            .select("parentCode")
            .groupBy("parentCode");

    // 主查询左连接子查询并映射到DTO
    return cbf.create(em, SysRegionWithChildCount.class)
            .from(SysRegion.class, "sr")
            .leftJoinOnSubquery(subQuery, "c")
            .on("c.parentCode = sr.parentCode")
            // 映射SysRegion字段
            .select("sr.id", "id")
            .select("sr.name", "name")
            .select("sr.level", "level")
            .select("sr.parentCode", "parentCode")
            .select("sr.areaCode", "areaCode")
            .select("sr.cityCode", "cityCode")
            .select("sr.zipCode", "zipCode")
            .select("sr.lng", "lng")
            .select("sr.lat", "lat")
            .select("sr.shortName", "shortName")
            .select("sr.mergerName", "mergerName")
            // 映射统计字段
            .select("c.childCount", "childCount")
            .getResultList();
}

CTE写法（利用@CTE注解） #

先定义CTE实体：

@CTE
@Getter
@Setter
public class SysRegionChildCountCTE {
    private BigInteger parentCode;
    private Long childCount;
}

再编写查询逻辑：

public List<SysRegionWithChildCount> getRegionsWithCTE() {
    // 定义CTE：统计父节点子数
    CTEBuilder<SysRegionChildCountCTE> cteBuilder = cbf.createCTE(SysRegionChildCountCTE.class)
            .with("childCount", "COUNT(id)")
            .with("parentCode")
            .from(SysRegion.class)
            .groupBy("parentCode");

    // 主查询关联CTE并映射结果
    return cbf.create(em, SysRegionWithChildCount.class)
            .with(cteBuilder)
            .from(SysRegion.class, "sr")
            .leftJoin(SysRegionChildCountCTE.class, "c")
            .on("c.parentCode = sr.parentCode")
            // 字段映射同前
            .select("sr.id", "id")
            .select("sr.name", "name")
            // ... 其他SysRegion字段映射
            .select("c.childCount", "childCount")
            .getResultList();
}

方式2：QueryDSL实现 #

@Inject
EntityManager em;

public List<SysRegionWithChildCount> getRegionsWithQueryDSL() {
    QSysRegion qSysRegion = QSysRegion.sysRegion;

    // 构建子查询
    JPASubQuery subQuery = new JPASubQuery()
            .from(qSysRegion)
            .groupBy(qSysRegion.parentCode)
            .list(qSysRegion.parentCode, qSysRegion.id.count().as("childCount"));

    // 主查询左连接子查询，映射到DTO
    return new JPAQuery<>(em)
            .from(qSysRegion)
            .leftJoin(subQuery, "c")
            .on(qSysRegion.parentCode.eq(PathBuilderFactory.forClass(SysRegionChildCountCTE.class).get("parentCode")))
            .select(Projections.bean(SysRegionWithChildCount.class,
                    qSysRegion.id,
                    qSysRegion.name,
                    qSysRegion.level,
                    qSysRegion.parentCode,
                    qSysRegion.areaCode,
                    qSysRegion.cityCode,
                    qSysRegion.zipCode,
                    qSysRegion.lng,
                    qSysRegion.lat,
                    qSysRegion.shortName,
                    qSysRegion.mergerName,
                    PathBuilderFactory.forClass(SysRegionChildCountCTE.class).get("childCount").as("childCount")
            ))
            .fetch();
}

依赖配置（pom.xml） #

确保引入Quarkus对应的扩展：

<!-- QueryDSL -->
<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-hibernate-orm-querydsl</artifactId>
</dependency>
<!-- Blaze Persistence -->
<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-blaze-persistence</artifactId>
</dependency>

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