Spring Boot 2.X 集成 Mybatis PageHelper 分页插件解决一对多分页查询问题

我就是我 2023-06-17 05:00 18阅读 0赞

### 文章目录 ###

*   *   *  1 摘要
         *  2 官方文档
         *  3 核心依赖
         *  4 核心代码
         *   *  4.1 方法应用
             *  4.2 Mapper 文件
             *  4.3 DAO 接口
             *  4.3 所有相关类概览
         *  5 数据库SQL
         *  6 测试结果
         *  7 参考文档推荐
         *  8 Github 源码

### 1 摘要 ###

每个项目中都会用到分页查询，这一技术具有高度的复用性，为了避免每次创建新项目时重新编写一边分页查询，或者因为项目的数据库更换而重新编写分页代码,将分页查询提取出来封装成专门的第三方库就显得十分必要。本文将介绍基于 Spring Boot 2.X 集成 Mybatis PageHelper 分页插件。

### 2 官方文档 ###

[MyBatis Pagination - PageHelper][]

### 3 核心依赖 ###

./pom.xml
    ./demo-dao/pom.xml

<dependency>
                    <groupId>com.github.pagehelper</groupId>
                    <artifactId>pagehelper-spring-boot-starter</artifactId>
                    <version>${mybatis.pagehelper.version}</version>
                </dependency>

其中 `${mybatis.pagehelper.version}` 的版本为 `1.2.12`

### 4 核心代码 ###

#### 4.1 方法应用 ####

./demo-service/src/main/java/com/ljq/demo/springboot/service/impl/ArticleServiceImpl.java

package com.ljq.demo.springboot.service.impl;
    
    import cn.hutool.core.bean.BeanUtil;
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import com.ljq.demo.springboot.common.api.ApiResult;
    import com.ljq.demo.springboot.common.page.QueryUtil;
    import com.ljq.demo.springboot.dao.article.ArticleDao;
    import com.ljq.demo.springboot.entity.ArticleEntity;
    import com.ljq.demo.springboot.service.ArticleService;
    import com.ljq.demo.springboot.vo.article.ArticleListParam;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    /** * 文章表业务层具体实现类 * * @author junqiang.lu * @date 2019-11-25 14:01:38 */
    @Service("articleService")
    @Transactional(rollbackFor = { Exception.class})
    @Slf4j
    public class ArticleServiceImpl implements ArticleService { 
    
    	@Autowired
    	private ArticleDao articleDao;
    
    	/** * 查询列表 * * @param articleListParam * @return * @throws Exception */
    	@Override
    	public ApiResult list(ArticleListParam articleListParam) throws Exception { 
    		long start = System.currentTimeMillis();
    		QueryUtil queryMap = new QueryUtil(BeanUtil.beanToMap(articleListParam, false, true));
    		PageInfo<ArticleEntity> pageInfo = PageHelper.startPage(articleListParam.getCurrPage(), articleListParam.getPageLimit())
    				.setOrderBy(articleListParam.getProperties() + " " + articleListParam.getDirection())
    				.doSelectPageInfo(() -> articleDao.queryListPage(queryMap));
    		long end = System.currentTimeMillis();
    		log.info("查询耗时: {}", (end - start));
    		return ApiResult.success(pageInfo);
    	}
    
    	
    }

关于 `PageHelper` 的一些方法说明:

`startPage`: 设置查询起始点与每页显示条数

`setOrderBy`: 设置排序规则

`doSelectPageInfo`: 需要执行的查询语句，Mybatis Mapper 中定义的接口(支持 lambda 表达式)，返回的结果是分页信息`PageInfo`(包含结果列表以及分页参数)

`doSelectPage`: 也是执行查询语句，与 `doSelectPageInfo` 一样，只是返回结果不同，`doSelectPage` 返回的是 `Page`, 如果将这个结果返回至前端，则只有数据列表，而没有分页信息。`Page` 可转化为 `PageInfo`

**注意** : 方法顺序必须是 `startPage` \-> `setOrderBy` \-> `doSelectPage` ,若将 `setOrderBy` 放在 `doSelectPage` 后边，则排序不生效(亲测结果是这样的)

#### 4.2 Mapper 文件 ####

../demo-dao/src/main/resources/mapper/ArticleDao.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="com.ljq.demo.springboot.dao.article.ArticleDao">
    
    	
        <resultMap type="com.ljq.demo.springboot.entity.ArticleEntity" id="articleMap">
            <result property="id" column="id"/>
            <result property="title" column="title"/>
            <result property="content" column="content"/>
    		<collection property="tagList" column="id" javaType="java.util.List" ofType="com.ljq.demo.springboot.entity.ArticleTagEntity" select="com.ljq.demo.springboot.dao.article.ArticleTagDao.queryTagsByArticleId">
    			<id property="id" column="id" />
    			<result property="tagName" column="tag_name" />
    		</collection>
        </resultMap>
    
       
    	<sql id="article_base_field">
            a.`id`,
            a.`title`,
            a.`content`
    	</sql>
    
    	
    	<sql id="article_list_field">
            <include refid="article_base_field" />
            ,
            at.`id` at_id,
            at.`tag_name` at_tag_name
    	</sql>
    
    	
    	<select id="queryListPage" parameterType="java.util.Map" resultMap="articleMap">
    		SELECT
    		<include refid="article_base_field" />
    		FROM article a
    		LEFT JOIN `article_to_tag` att ON att.article_id = a.id
    		LEFT JOIN `article_tag` at ON at.id = att.tag_id
    		WHERE 1 = 1
    		<if test="title != null and '' != title">
    			AND a.title LIKE CONCAT(CONCAT("%", #{title}), "%")
    		</if>
    		<if test="articleTag != null and '' != articleTag">
    			AND at.tag_name LIKE CONCAT(CONCAT("%", #{articleTag}), "%")
    		</if>
    		GROUP BY a.id
    	</select>
    
    
    
    </mapper>

../demo-dao/src/main/resources/mapper/ArticleTagDao.xml

**注意:** 在一对多的关系中，可以使用 `collection` 来对查询结果进行封装。在 `collection` 标签中有 `select` 属性，用于将一个子查询结果封装到 `resultMap` 中，这个查询可以为本 Mapper 文件中的查询，也可以是其他 Mapper 文件的查询，如果为其他 Mapper 中的查询，则需要指定具体的 Mapper ，具体参考示例中`ArticleDao.xml`的代码

#### 4.3 DAO 接口 ####

../demo-dao/src/main/java/com/ljq/demo/springboot/dao/article/ArticleTagDao.java

package com.ljq.demo.springboot.dao.article;
    
    import com.ljq.demo.springboot.dao.BaseDao;
    import com.ljq.demo.springboot.entity.ArticleTagEntity;
    import org.apache.ibatis.annotations.Param;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /** * 文章标签表 * * @author junqiang.lu * @date 2019-11-25 14:01:38 */
    @Repository
    public interface ArticleTagDao extends BaseDao<ArticleTagEntity> { 
    
        /** * 查询某一篇文章的标签(列表) * * @param articleId * @return */
        List<ArticleTagEntity> queryTagsByArticleId(@Param("articleId") long articleId);
    
    }

../demo-dao/src/main/java/com/ljq/demo/springboot/dao/article/ArticleDao.java

package com.ljq.demo.springboot.dao.article;
    
    import com.ljq.demo.springboot.dao.BaseDao;
    import com.ljq.demo.springboot.entity.ArticleEntity;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    import java.util.Map;
    
    /** * 文章表 * * @author junqiang.lu * @date 2019-11-25 14:01:38 */
    @Repository
    public interface ArticleDao extends BaseDao<ArticleEntity> { 
    
        /** * 查询列表 * * @param queryMap * @return */
        List<ArticleEntity> queryListPage(Map<String, Object> queryMap);
    	
    }

#### 4.3 所有相关类概览 ####

数据模型(实体类):

../demo-model/src/main/java/com/ljq/demo/springboot/entity/ArticleEntity.java
    ../demo-model/src/main/java/com/ljq/demo/springboot/entity/ArticleTagEntity.java
    ../demo-model/src/main/java/com/ljq/demo/springboot/entity/ArticleToTagEntity.java
    
    ../demo-model/src/main/java/com/ljq/demo/springboot/vo/article/ArticleListParam.java

数据持久层(DAO):

../demo-dao/src/main/java/com/ljq/demo/springboot/dao/article/ArticleTagDao.java
    ../demo-dao/src/main/java/com/ljq/demo/springboot/dao/article/ArticleDao.java

业务层:

../demo-service/src/main/java/com/ljq/demo/springboot/service/ArticleService.java
    ../demo-service/src/main/java/com/ljq/demo/springboot/service/impl/ArticleServiceImpl.java

控制层(Controller):

../demo-web/src/main/java/com/ljq/demo/springboot/web/controller/ArticleController.java

### 5 数据库SQL ###

../doc/sql/article-database-create.sql

/*==============================================================*/
    /* DBMS name: MySQL 5.0 */
    /* Created on: 2019/11/27 14:32:00 */
    /*==============================================================*/
    
    
    drop table if exists article;
    
    drop table if exists article_tag;
    
    drop table if exists article_to_tag;
    
    /*==============================================================*/
    /* Table: article */
    /*==============================================================*/
    create table article
    (
       id                   bigint unsigned not null auto_increment comment '文章 id,主键',
       title                varchar(100) not null default '' comment '文章标题',
       content              varchar(5000) not null default '' comment '文章内容',
       primary key (id)
    )
    ENGINE = InnoDB
    CHARSET = UTF8MB4;
    
    alter table article comment '文章表';
    
    /*==============================================================*/
    /* Table: article_tag */
    /*==============================================================*/
    create table article_tag
    (
       id                   bigint unsigned not null auto_increment comment '文章标签 id,主键',
       tag_name             varchar(20) not null default '' comment '标签名称',
       primary key (id)
    )
    ENGINE = InnoDB
    CHARSET = UTF8MB4;
    
    alter table article_tag comment '文章标签表';
    
    /*==============================================================*/
    /* Table: article_to_tag */
    /*==============================================================*/
    create table article_to_tag
    (
       id                   bigint unsigned not null auto_increment comment '文章-标签关联表 id,主键',
       article_id           bigint unsigned not null default 0 comment '文章 id',
       tag_id               bigint unsigned not null default 0 comment '标签 id',
       primary key (id)
    )
    ENGINE = InnoDB
    CHARSET = UTF8MB4;
    
    alter table article_to_tag comment '文章-标签关联表';

测试数据:

../doc/sql/article-database-test.sql

-- 批量插入文章标签
    INSERT INTO `article_tag`(`tag_name`) VALUES
        ('初级'),
    		('中级'),
    		('高级'),
    		('超高级'),
    		('spring'),
    		('springBoot'),
    		('springMVC');
    
    -- 批量插入文章
    INSERT INTO `article` (`title`, `content`) VALUES
        ('好风光', '今天天气好晴朗，处处好风光'),
    		('冰雨', '冷冷的冰雨在我脸上胡乱地拍，你就像一个刽子手把我出卖'),
    		('学习', '好好学习,天天向上'),
    		('静夜思', '窗前明月光，疑是地上霜。举头望明月，低头思故乡。'),
    		('冬日里的一把火', '你就像那一把火，熊熊火焰燃烧了我'),
    		('演员', '简单点，说话的方式简单点。递进的情绪请省略，你又不是个演员'),
    		('小苹果', '你是我的小丫小苹果，怎么爱你都不嫌多'),
    		('雨一直下', '雨一直下，气氛不算融洽');
    
    
    -- 批量给文章添加标签
    INSERT INTO `article_to_tag` (`article_id`, `tag_id`) VALUES
        (1,1),
        (1,2),
        (1,4),
        (2,3),
        (2,5),
        (3,6),
        (4,7),
        (5,1),
        (5,2),
        (5,3),
        (6,4),
        (6,1),
        (6,5),
        (6,7),
        (7,6),
        (7,1),
        (8,3),
        (8,6),
        (8,7),
        (8,4);

### 6 测试结果 ###

请求接口:

http://127.0.0.1:8088/api/article/list?currPage=1&direction=desc&pageLimit=5&properties=id

后台日志:

2019-11-27 16:00:33 | INFO  | http-nio-8088-exec-4 | com.ljq.demo.springboot.web.acpect.LogAspectLogAspect.java 68| [AOP-LOG-START]
    	requestMark: 04822886-af60-47ac-966f-0d8d6ea8c7fe
    	requestIP: 127.0.0.1
    	contentType:application/x-www-form-urlencoded
    	requestUrl: http://127.0.0.1:8088/api/article/list
    	requestMethod: GET
    	requestParams: currPage = [1];direction = [desc];pageLimit = [5];properties = [id];
    	targetClassAndMethod: com.ljq.demo.springboot.web.controller.ArticleController#list
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.dao.article.ArticleDao.queryListPage_COUNTBaseJdbcLogger.java 159| ==>  Preparing: SELECT count(0) FROM (SELECT a.`id`, a.`title`, a.`content` FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id WHERE 1 = 1 GROUP BY a.id) table_count 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.dao.article.ArticleDao.queryListPage_COUNTBaseJdbcLogger.java 159| ==> Parameters: 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.dao.article.ArticleDao.queryListPage_COUNTBaseJdbcLogger.java 159| <==      Total: 1
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.dao.article.ArticleDao.queryListPageBaseJdbcLogger.java 159| ==>  Preparing: SELECT a.`id`, a.`title`, a.`content` FROM article a LEFT JOIN `article_to_tag` att ON att.article_id = a.id LEFT JOIN `article_tag` at ON at.id = att.tag_id WHERE 1 = 1 GROUP BY a.id order by id desc LIMIT ? 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.dao.article.ArticleDao.queryListPageBaseJdbcLogger.java 159| ==> Parameters: 5(Integer)
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 8(Long)
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 4
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 7(Long)
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 2
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 6(Long)
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 4
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 5(Long)
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 3
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====>  Preparing: SELECT at.`id`, at.`tag_name` FROM `article_to_tag` att LEFT JOIN `article_tag` at ON at.id = att.tag_id LEFT JOIN `article` a ON a.id = att.article_id WHERE a.id = ? 
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| ====> Parameters: 4(Long)
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.d.a.ArticleTagDao.queryTagsByArticleIdBaseJdbcLogger.java 159| <====      Total: 1
    2019-11-27 16:00:33 | DEBUG | http-nio-8088-exec-4 | c.l.d.s.dao.article.ArticleDao.queryListPageBaseJdbcLogger.java 159| <==      Total: 5
    2019-11-27 16:00:33 | INFO  | http-nio-8088-exec-4 | c.l.d.springboot.service.impl.ArticleServiceImplArticleServiceImpl.java 46| 查询耗时: 18
    2019-11-27 16:00:33 | INFO  | http-nio-8088-exec-4 | com.ljq.demo.springboot.web.acpect.LogAspectLogAspect.java 76| [AOP-LOG-END]
    	<200 OK,ApiResult(code=1000, msg=成功, data=PageInfo{pageNum=1, pageSize=5, size=5, startRow=1, endRow=5, total=8, pages=2, list=Page{count=true, pageNum=1, pageSize=5, startRow=0, endRow=5, total=8, pages=2, reasonable=false, pageSizeZero=false}[ArticleEntity(id=8, title=雨一直下, content=雨一直下，气氛不算融洽, tagList=[ArticleTagEntity(id=3, tagName=高级), ArticleTagEntity(id=6, tagName=springBoot), ArticleTagEntity(id=7, tagName=springMVC), ArticleTagEntity(id=4, tagName=超高级)]), ArticleEntity(id=7, title=小苹果, content=你是我的小丫小苹果，怎么爱你都不嫌多, tagList=[ArticleTagEntity(id=6, tagName=springBoot), ArticleTagEntity(id=1, tagName=初级)]), ArticleEntity(id=6, title=演员, content=简单点，说话的方式简单点。递进的情绪请省略，你又不是个演员, tagList=[ArticleTagEntity(id=4, tagName=超高级), ArticleTagEntity(id=1, tagName=初级), ArticleTagEntity(id=5, tagName=spring), ArticleTagEntity(id=7, tagName=springMVC)]), ArticleEntity(id=5, title=冬日里的一把火, content=你就像那一把火，熊熊火焰燃烧了我, tagList=[ArticleTagEntity(id=1, tagName=初级), ArticleTagEntity(id=2, tagName=中级), ArticleTagEntity(id=3, tagName=高级)]), ArticleEntity(id=4, title=静夜思, content=窗前明月光，疑是地上霜。举头望明月，低头思故乡。, tagList=[ArticleTagEntity(id=7, tagName=springMVC)])], prePage=0, nextPage=2, isFirstPage=true, isLastPage=false, hasPreviousPage=false, hasNextPage=true, navigatePages=8, navigateFirstPage=1, navigateLastPage=2, navigatepageNums=[1, 2]}, extraData=null, timestamp=1574841633357),{Content-Type=[application/json]}>

### 7 参考文档推荐 ###

[官方文档 MyBatis Pagination - PageHelper][MyBatis Pagination - PageHelper]

[官方文档 How to use PageHelper][How to use PageHelper]

[官方文档 PageHelper integration with Spring Boot][PageHelper integration with Spring Boot]

### 8 Github 源码 ###

**Gtihub 源码地址** : [https://github.com/Flying9001/springBootDemo][https_github.com_Flying9001_springBootDemo]

个人公众号:**404Code**,分享半个互联网人的技术与思考，感兴趣的可以关注.  
![404Code][]

[MyBatis Pagination - PageHelper]: https://github.com/pagehelper/Mybatis-PageHelper
[How to use PageHelper]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md
[PageHelper integration with Spring Boot]: https://github.com/pagehelper/pagehelper-spring-boot
[https_github.com_Flying9001_springBootDemo]: https://github.com/Flying9001/springBootDemo
[404Code]: https://img-blog.csdn.net/20181010223005114?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70