的多表关联查询、DTO 投影、聚合统计与视图封装

在前几篇中，我们已经逐步完成了：

  1. tio-boot + jOOQ 基础整合
  2. 事务管理
  3. 连接池整合
  4. Codegen 类型安全
  5. Record / POJO CRUD
  6. 批量操作、分页、动态 SQL
  7. PostgreSQL UPSERT 与 returning

到这里，已经具备完整的 单表数据访问能力。

但在真实业务系统中，大量查询其实是：

  • 多表 join
  • DTO 投影
  • 聚合统计
  • 报表查询
  • 复杂视图封装

如果只依赖 ORM 或字符串 SQL，代码往往会变得：

  • SQL 难维护
  • DTO 映射混乱
  • join 字段易写错

而 jOOQ 的优势就在这里：

既保留 SQL 的表达能力，又让查询结构保持强类型与可维护性。

本文将系统讲清：

  • 多表关联查询
  • DTO 投影
  • 聚合统计
  • group by
  • 子查询
  • 视图封装
  • 在 tio-boot 项目中的最佳实践

一、准备示例表结构

为了讲解多表查询，我们引入一张新表：

CREATE TABLE system_role (
  id SERIAL PRIMARY KEY,
  role_name VARCHAR(64)
);

CREATE TABLE system_admin_role (
  admin_id INT,
  role_id INT
);

关系：

system_admin
      |
      | (admin_id)
      |
system_admin_role
      |
      | (role_id)
      |
system_role

表示：

一个管理员可以拥有多个角色。

二、Codegen 后生成的对象

生成类通常包括：

Tables.SYSTEM_ADMIN
Tables.SYSTEM_ROLE
Tables.SYSTEM_ADMIN_ROLE

每张表都有：

  • Table
  • Record
  • POJO

例如：

SystemAdmin
SystemAdminRecord
SystemAdminPojo

在查询时，我们几乎只会使用：

SYSTEM_ADMIN
SYSTEM_ROLE
SYSTEM_ADMIN_ROLE

三、最基本的多表 JOIN 查询

先看最简单的关联查询：

查询管理员及其角色

SQL：

select
  a.id,
  a.login_name,
  r.role_name
from system_admin a
left join system_admin_role ar on a.id = ar.admin_id
left join system_role r on ar.role_id = r.id

jOOQ 写法：

public List<Record3<Integer,String,String>> listAdminRoles() {

  return useDsl()
    .select(
      SYSTEM_ADMIN.ID,
      SYSTEM_ADMIN.LOGIN_NAME,
      SYSTEM_ROLE.ROLE_NAME
    )
    .from(SYSTEM_ADMIN)
    .leftJoin(SYSTEM_ADMIN_ROLE)
      .on(SYSTEM_ADMIN.ID.eq(SYSTEM_ADMIN_ROLE.ADMIN_ID))
    .leftJoin(SYSTEM_ROLE)
      .on(SYSTEM_ADMIN_ROLE.ROLE_ID.eq(SYSTEM_ROLE.ID))
    .fetch();
}

返回类型：

Record3<Integer,String,String>

表示：

(id, loginName, roleName)

四、DTO 投影（推荐方式）

在真实项目中，我们通常不会直接返回 Record3。

更推荐：

映射到 DTO

先定义 DTO：

package demo.jooq.dto;

public class AdminRoleDTO {

  private Integer adminId;
  private String loginName;
  private String roleName;

  public Integer getAdminId() {
    return adminId;
  }

  public void setAdminId(Integer adminId) {
    this.adminId = adminId;
  }

  public String getLoginName() {
    return loginName;
  }

  public void setLoginName(String loginName) {
    this.loginName = loginName;
  }

  public String getRoleName() {
    return roleName;
  }

  public void setRoleName(String roleName) {
    this.roleName = roleName;
  }
}

然后查询：

public List<AdminRoleDTO> listAdminRoles() {

  return useDsl()
    .select(
      SYSTEM_ADMIN.ID.as("adminId"),
      SYSTEM_ADMIN.LOGIN_NAME,
      SYSTEM_ROLE.ROLE_NAME
    )
    .from(SYSTEM_ADMIN)
    .leftJoin(SYSTEM_ADMIN_ROLE)
      .on(SYSTEM_ADMIN.ID.eq(SYSTEM_ADMIN_ROLE.ADMIN_ID))
    .leftJoin(SYSTEM_ROLE)
      .on(SYSTEM_ADMIN_ROLE.ROLE_ID.eq(SYSTEM_ROLE.ID))
    .fetchInto(AdminRoleDTO.class);
}

这样 jOOQ 会自动：

字段 -> DTO 属性

进行映射。

五、DTO 构造函数映射（更类型安全）

如果 DTO 有构造函数：

public class AdminRoleDTO {

  private Integer adminId;
  private String loginName;
  private String roleName;

  public AdminRoleDTO(Integer adminId,String loginName,String roleName){
    this.adminId=adminId;
    this.loginName=loginName;
    this.roleName=roleName;
  }
}

可以使用 jOOQ 的 mapping：

public List<AdminRoleDTO> listAdminRoles() {

  return useDsl()
    .select(
      SYSTEM_ADMIN.ID,
      SYSTEM_ADMIN.LOGIN_NAME,
      SYSTEM_ROLE.ROLE_NAME
    )
    .from(SYSTEM_ADMIN)
    .leftJoin(SYSTEM_ADMIN_ROLE)
      .on(SYSTEM_ADMIN.ID.eq(SYSTEM_ADMIN_ROLE.ADMIN_ID))
    .leftJoin(SYSTEM_ROLE)
      .on(SYSTEM_ADMIN_ROLE.ROLE_ID.eq(SYSTEM_ROLE.ID))
    .fetch(Records.mapping(AdminRoleDTO::new));
}

优点：

  • 完全类型安全
  • 编译期检查
  • 无反射

这是 jOOQ 推荐方式。

六、查询管理员及角色列表（多值聚合）

有时需要：

admin
  -> roles[]

SQL 可以写：

select
  a.id,
  a.login_name,
  array_agg(r.role_name)
from system_admin a
left join system_admin_role ar on a.id = ar.admin_id
left join system_role r on ar.role_id = r.id
group by a.id,a.login_name

jOOQ：

public List<Record3<Integer,String,String[]>> listAdminRoleArray(){

  return useDsl()
    .select(
      SYSTEM_ADMIN.ID,
      SYSTEM_ADMIN.LOGIN_NAME,
      DSL.arrayAgg(SYSTEM_ROLE.ROLE_NAME)
    )
    .from(SYSTEM_ADMIN)
    .leftJoin(SYSTEM_ADMIN_ROLE)
      .on(SYSTEM_ADMIN.ID.eq(SYSTEM_ADMIN_ROLE.ADMIN_ID))
    .leftJoin(SYSTEM_ROLE)
      .on(SYSTEM_ADMIN_ROLE.ROLE_ID.eq(SYSTEM_ROLE.ID))
    .groupBy(SYSTEM_ADMIN.ID,SYSTEM_ADMIN.LOGIN_NAME)
    .fetch();
}

七、聚合统计

统计管理员数量：

public int countAdmin(){

  return useDsl()
    .selectCount()
    .from(SYSTEM_ADMIN)
    .fetchOne(0,int.class);
}

按角色统计管理员数量

SQL：

select
 role_name,
 count(*)
from system_role r
left join system_admin_role ar
  on r.id=ar.role_id
group by role_name

jOOQ：

public List<Record2<String,Integer>> countByRole(){

  return useDsl()
    .select(
      SYSTEM_ROLE.ROLE_NAME,
      DSL.count().cast(Integer.class)
    )
    .from(SYSTEM_ROLE)
    .leftJoin(SYSTEM_ADMIN_ROLE)
      .on(SYSTEM_ROLE.ID.eq(SYSTEM_ADMIN_ROLE.ROLE_ID))
    .groupBy(SYSTEM_ROLE.ROLE_NAME)
    .fetch();
}

八、子查询

查询：

拥有角色数量最多的管理员

SQL：

select *
from system_admin
where id in(
 select admin_id
 from system_admin_role
 group by admin_id
 order by count(*) desc
 limit 1
)

jOOQ：

public SystemAdminRecord findTopRoleAdmin(){

  var subQuery =
    useDsl()
      .select(SYSTEM_ADMIN_ROLE.ADMIN_ID)
      .from(SYSTEM_ADMIN_ROLE)
      .groupBy(SYSTEM_ADMIN_ROLE.ADMIN_ID)
      .orderBy(DSL.count().desc())
      .limit(1);

  return useDsl()
    .selectFrom(SYSTEM_ADMIN)
    .where(SYSTEM_ADMIN.ID.in(subQuery))
    .fetchOne();
}

九、exists 子查询

判断某个用户是否拥有某角色：

public boolean adminHasRole(Integer adminId,Integer roleId){

  return useDsl().fetchExists(

    useDsl()
      .selectOne()
      .from(SYSTEM_ADMIN_ROLE)
      .where(
        SYSTEM_ADMIN_ROLE.ADMIN_ID.eq(adminId)
          .and(SYSTEM_ADMIN_ROLE.ROLE_ID.eq(roleId))
      )
  );
}

十、视图封装（推荐大型项目）

复杂 SQL 可以封装为数据库视图：

create view v_admin_role as
select
  a.id,
  a.login_name,
  r.role_name
from system_admin a
left join system_admin_role ar
  on a.id=ar.admin_id
left join system_role r
  on ar.role_id=r.id;

然后重新 Codegen。

就会生成：

Tables.V_ADMIN_ROLE

查询：

public List<VAdminRolePojo> listAdminRoleView(){

  return useDsl()
    .selectFrom(V_ADMIN_ROLE)
    .fetchInto(VAdminRolePojo.class);
}

优点：

  • SQL 简化
  • DAO 更干净
  • 数据库优化更容易

十一、DTO 投影 + 分页

结合第 6 篇分页：

public PageResult<AdminRoleDTO> pageAdminRole(int pageNo,int pageSize){

  int offset=(pageNo-1)*pageSize;

  List<AdminRoleDTO> list=

    useDsl()
      .select(
        SYSTEM_ADMIN.ID.as("adminId"),
        SYSTEM_ADMIN.LOGIN_NAME,
        SYSTEM_ROLE.ROLE_NAME
      )
      .from(SYSTEM_ADMIN)
      .leftJoin(SYSTEM_ADMIN_ROLE)
      .on(SYSTEM_ADMIN.ID.eq(SYSTEM_ADMIN_ROLE.ADMIN_ID))
      .leftJoin(SYSTEM_ROLE)
      .on(SYSTEM_ADMIN_ROLE.ROLE_ID.eq(SYSTEM_ROLE.ID))
      .limit(pageSize)
      .offset(offset)
      .fetchInto(AdminRoleDTO.class);

  int total=
    useDsl()
      .selectCount()
      .from(SYSTEM_ADMIN)
      .fetchOne(0,int.class);

  return new PageResult<>(pageNo,pageSize,total,list);
}

十二、最佳实践总结

DAO 层

推荐：

  • 多表查询 → DTO
  • 单表 CRUD → Record / POJO
  • 统计查询 → Record / DTO

Service 层

职责：

  • 事务边界
  • 业务逻辑
  • 组合 DAO

Controller 层

职责：

  • HTTP 输入输出
  • DTO / VO 转换

十三、性能建议

多表查询注意：

1 join 顺序

尽量：

小表 -> 大表

2 必须有索引

例如：

system_admin_role.admin_id
system_admin_role.role_id

3 分页必须排序

order by id

4 避免 N+1 查询

不要：

for admin
  query roles

要：

join 查询

十四、本篇总结

本篇讲解了：

  • 多表 join
  • DTO 投影
  • mapping 投影
  • 聚合统计
  • 子查询
  • exists 查询
  • 视图封装
  • DTO + 分页
  • 多表性能优化

核心思想可以总结为一句话：

jOOQ 不是 ORM，而是强类型 SQL DSL。

它允许我们：

  • 保留 SQL 表达力
  • 避免字符串 SQL
  • 保持 Java 类型安全
  • 写出结构清晰的查询代码

当 tio-boot + jOOQ + PostgreSQL 组合在一起时，可以形成一套非常现代的数据库访问体系：

SQL能力 + 类型安全 + 高性能