如何编写JOOQ查询以获取包含结果的单个Java Record

你的核心需求是通过一次JOOQ查询同时获取符合条件的Person总数量和分页后的Person列表，并封装到自定义的MyResult Record中。原代码的问题在于使用count().over()时，主查询的limit会限制窗口函数的统计范围，导致得到的是当前分页的数量而非总条数。下面提供两种可行的实现方式：

方案一：子查询统计总条数 + Multiset获取分页数据 #

这种方式逻辑直观，通过子查询单独统计总数量，再用multiset查询分页后的Person列表：

MyResult result = DSL.getContext(conn)
    .select(
        // 子查询获取符合条件的总人数，映射为Info对象
        DSL.row(DSL.selectCount().from(PERSON).where(PERSON.NAME.startsWith("A"))).mapping(Info::new),
        // 用multiset查询分页后的Person列表并转换为自定义Record
        DSL.multiset(
            DSL.select(PERSON.PERSON_ID, PERSON.NAME)
                .from(PERSON)
                .where(PERSON.NAME.startsWith("A"))
                .orderBy(PERSON.NAME.asc())
                .offset(0)
                .limit(5)
        ).convertFrom(r -> r.map(Records.mapping(Person::new)))
    )
    .fetchOne(Records.mapping(MyResult::new));

说明 #

• 第一个查询字段通过selectCount()子查询直接统计所有符合name startsWith "A"条件的Person总数，不受分页影响。
• 第二个字段用multiset执行分页查询，将结果转换为List<Person>。
• 整个语句是一次数据库查询（JOOQ会将multiset转换为SQL标准的MULTISET构造或兼容的子查询语法）。

方案二：窗口函数预统计总条数 + 聚合分页数据 #

如果不想重复编写where条件，可以用窗口函数在子查询中先计算总条数，再分页并聚合结果：

MyResult result = DSL.getContext(conn)
    .select(
        // 提取子查询中的总条数（所有行的total_count值一致，取任意一个即可）
        DSL.row(DSL.field("total_count", Integer.class)).mapping(Info::new),
        // 将当前页的Person聚合为列表
        DSL.multisetAgg(PERSON.PERSON_ID, PERSON.NAME)
            .convertFrom(r -> r.map(Records.mapping(Person::new)))
    )
    .from(
        // 子查询：给每条数据带上总条数，同时执行分页
        DSL.select(
            PERSON.PERSON_ID,
            PERSON.NAME,
            DSL.count().over().as("total_count") // 窗口函数统计符合条件的总条数，不受limit影响
        )
        .from(PERSON)
        .where(PERSON.NAME.startsWith("A"))
        .orderBy(PERSON.NAME.asc())
        .offset(0)
        .limit(5)
    )
    .fetchOne(Records.mapping(MyResult::new));

说明 #

• 子查询中用count().over()窗口函数统计所有符合条件的记录总数（窗口范围是整个结果集，不受后续limit限制），并将总条数作为字段返回。
• 主查询中通过multisetAgg将分页后的所有Person字段聚合为列表，同时提取总条数封装为Info。

原代码问题解析 #

你原本的代码中，count().over()是在加了limit的主查询中执行的，窗口函数的统计范围被限制为当前分页的5条数据，因此得到的count是5而非符合条件的总人数，这就是不符合需求的原因。

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