【DB系列】Jooq之聚合查询

上一篇介绍的时候一些Jooq基本的查询使用方式，分组聚合查询，在实际的业务场景中也比较常见，本文将介绍下jooq中，如何使用聚合查询

# I. 项目搭建

本项目借助SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

# 1. 项目依赖

关于如何创建一个SpringBoot的项目工程，不再本文的描述范围内，如有兴趣可以到文末的个人站点获取

在这个示例工程中，我们的选用h2dabase作为数据库（方便有兴趣的小伙伴直接获取工程源码之后，直接测试体验），因此对应的pom核心依赖如下

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 2. 数据准备

本文对应的项目工程，和前面介绍增加删除的一致，所以这里直接使用之前新增的数据

# II. 使用姿势

# 0. 基本准备

测试类，初始化一些必要的依赖，如 dsl

@Repository
public class PoetryGroupQueryRepository {

    private static final PoetryTB poetryTable = PoetryTB.POETRY;

    @Autowired
    private DSLContext dsl;

}

1
2
3
4
5
6
7
8
9

# 1. 简单group查询

对于mysql中，group的聚合查询

注意

一般情况下，select中的列需要是group by的条件，否则会包sql错误

如果希望避免上面的问题，解决办法就是干掉参数ONLY_FULL_GROUP_BY，这样在select中就可以出现其他的列了

public void queryGroupByPoetId() {
    // group聚合函数支持 count, max, min, avg
    // select poet_id, count(1) from poetry group by poet_id
    Result<Record2<Integer, Integer>> ans =
            dsl.select(poetryTable.POET_ID, DSL.count()).from(poetryTable).groupBy(poetryTable.POET_ID).fetch();

    for (Record2<Integer, Integer> sub : ans) {
        System.out.println("queryGroupByPoetId ==> poetId: " + sub.get(0) + " count: " + sub.get(1));
    }
}

1
2
3
4
5
6
7
8
9
10

# 2. where限定+group

public void queryByIdGtAndGroupByPoetId(int id) {
    // select poet_id, count(1) from poetry where id>xx group by poet_id
    Result<Record2<Integer, Integer>> ans =
            dsl.select(poetryTable.POET_ID, DSL.count()).from(poetryTable).where(poetryTable.ID.gt(id))
                    .groupBy(poetryTable.POET_ID).fetch();

    for (Record2<Integer, Integer> sub : ans) {
        System.out.println("queryByIdGtAndGroupByPoetId ==> poetId: " + sub.get(0) + " count: " + sub.get(1));
    }
}

1
2
3
4
5
6
7
8
9
10

# 3. group + having

遵循标准sql语法，where是在分组之前做过滤，having是在分组之后进行过滤，having后可以跟聚合函数，且可以于前面的聚合函数不同

public void queryGroupByPoetIdHavingCntGt(int count) {
    // 请注意 where是在分组之前做过滤，having是在分组之后进行过滤，having后可以跟聚合函数，且可以于前面的聚合函数不同
    // select poet_id, count(1) from poetry group by poet_id having count(1) > xxx
    Result<Record2<Integer, Integer>> ans =
            dsl.select(poetryTable.POET_ID, DSL.count()).from(poetryTable).groupBy(poetryTable.POET_ID)
                    .having(DSL.count().gt(count)).fetch();

    for (Record2<Integer, Integer> sub : ans) {
        System.out.println("queryGroupByPoetIdHavingCntGt ==> poetId: " + sub.get(0) + " count: " + sub.get(1));
    }
}

1
2
3
4
5
6
7
8
9
10
11

# 4. group + having + limit

唯一需要注意的就是多个限定时，请注意先后顺序

public void queryByIdGtGroupByPoetIdAndHavingCntGtAndOrderByPoetIdLimit(int id, int cnt, int limit) {
    // 请注意下面几个同时出现在一个sql时，有严格的先后顺序
    // select poet_id, count(1) from poetry where id>xxx group by poet_id having count(1)>xxx limit xxx
    Result<Record2<Integer, Integer>> ans =
            dsl.select(poetryTable.POET_ID, DSL.count()).from(poetryTable).where(poetryTable.ID.gt(id))
                    .groupBy(poetryTable.POET_ID).having(DSL.count().gt(cnt))
                    .orderBy(poetryTable.POET_ID.asc())
                    .limit(limit).fetch();
    for (Record2<Integer, Integer> sub : ans) {
        System.out.println("queryByIdGtGroupByPoetIdAndHavingCntGtAndOrderByPoetIdLimit ==> poetId: " + sub.get(0) + " count: " + sub.get(1));
    }
}