SpringBoot - 后端条件分页查询-MyBatis解决方案

最近用SpringBoot+MyBatis在做个人博客网站，遇了条件分页查询，现在把解决流程与源代码记录下来，便于后续参考。

0. pom依赖

这里把主要的依赖放进来，其他相关依赖请自行导入

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <!-- pagehelper 分页的一个工具 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>

1.数据表

数据表sql:

CREATE DATABASE /*!32312 IF NOT EXISTS*/`myblog` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `myblog`;

/*Table structure for table `articles` */

DROP TABLE IF EXISTS `articles`;

CREATE TABLE `articles` (
  `article_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `title` varchar(255) DEFAULT NULL COMMENT '文章标题',
  `name` varchar(255) DEFAULT NULL,
  `view_num` int(11) DEFAULT NULL COMMENT '浏览数',
  `comment_num` int(11) DEFAULT NULL COMMENT '评论数',
  `category_id` bigint(20) DEFAULT NULL COMMENT '文章分类ID',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  `like_num` int(11) DEFAULT '0',
  PRIMARY KEY (`article_id`),
  KEY `key_category_id` (`category_id`),
  KEY `key_userId` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8 COMMENT='文章表';

/*Data for the table `articles` */

insert  into `articles`(`article_id`,`user_id`,`title`,`name`,`view_num`,`comment_num`,`category_id`,`create_time`,`like_num`) values (1,0,'555','555.md',0,3,1,'2020-09-22 00:00:00',0),(83,1,'daidai','daidai.md',0,4,1,'2020-09-30 11:09:42',0),(84,1,'redis安装','redis安装.md',0,1,1,'2020-09-30 11:09:43',0),(85,1,'淘宝革命的启示录','淘宝革命的启示录.md',0,2,1,'2020-09-30 11:09:43',0),(98,1,'444','444.md',0,9,0,'2020-10-01 10:55:45',0);

2. 定义pojo

定义与数据表相对应的Article 类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Article implements Serializable {

    private Long articleId;
    private Long userId=1l ;
    private String title;
    private String name;
    private Integer viewNum=0 ;
    private Integer commentNum=0 ;
    private long categoryId ;
    private Timestamp createTime;
}

定义查询类

@Data
public class ArticleQuery extends Article {

    //排序字段
    public String sortView;  //传入的sort字符串
    public String sort;

    public String getSort(){
        if(sortView!=null){
        	//驼峰命名转化为横线命名 例如commentNum=>commment_num 适应sql语句字段规则
            this.sort=StringTool.humpToLine(this.sortView);  
        }
        return this.sort;
    }

    //排序方向
    public String direction;
    //页面大小
    public Integer pageSize;
    //页数
    public Integer pageNum;

这里放一个驼峰命名转换的工具类：

public class StringTool {
    private static Pattern linePattern = Pattern.compile("_(\\w)");

    /** 下划线转驼峰 */
    public static String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    /** 驼峰转下划线(简单写法，效率低于{@link #humpToLine2(String)}) */
    public static String humpToLine(String str) {
        return str.replaceAll("[A-Z]", "_$0").toLowerCase();
    }

    private static Pattern humpPattern = Pattern.compile("[A-Z]");

    /** 驼峰转下划线,效率比上面高 */
    public static String humpToLine2(String str) {
        Matcher matcher = humpPattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    public static void main(String[] args) {
        String lineToHump = lineToHump("f_parent_no_leader");
        System.out.println(lineToHump);// fParentNoLeader
        System.out.println(humpToLine(lineToHump));// f_parent_no_leader
        System.out.println(humpToLine2(lineToHump));// f_parent_no_leader
    }

}

3.MyBatis配置

在application.yml中对mybatis相关属性进地配置，其他数据库连接相关配置就不说了。

#mybatis配置
mybatis:
  #config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml  #定义.xml文件位置，注意不能与config-location共存
  type-aliases-package: com.xinxin.pojo  #定义别名，直接找到pojo这个包 
  configuration:
    map-underscore-to-camel-case: true  #驼峰命名设置为true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #运时打印出sql语句

4. Dao层

    //根据条件查询文章
    public List<Article> queryByCondition(ArticleQuery aq);

5. mapper层

在ArticleMapper.xml写对应的sql语句

    <!--文章查询的sql片断，建议是以单表为单位定义查询条件，建议将常用的查询条件都写出来-->
    <sql id="query_items_where">
        <if test="name!=null and name!=''">
            and name like '%${name}%'
        </if>
        <if test="title!=null and title!=''">
            and title like '%${title}%'
        </if>
        <if test="articleId!=null">
            and article_id = #{articleId}
        </if>
    </sql>

	
    <select id="queryByCondition" parameterType="ArticleQuery" resultType="Article">
            -- 注意ORDER BY后面用的是$而不是#
            select * from articles
            <where>
                <include refid="query_items_where" />
            </where>
            <if test="sort!=null and sort!=''">
                ORDER BY ${sort}
            </if>
            <if test="direction!=null and direction!=''">
                ${direction}
            </if>

    </select>

注意一定要开启mybatis二级缓存，这样如果第一次select，后面就不用都去查询数据库了，系统会变快！只需要在ArticleMapper.xml加一个“”即可。

<mapper namespace="com.xinxin.dao.ArticleDao">
<!--    开启二级缓存,这样对所有select操作都会作缓存处理-->
    <cache />
 ...
</mapper>

6. service层

    //根据条件查询文章
    public List<Article> queryByCondition(ArticleQuery aq);

7. service层对应的Impl

    public PageResult findPage(ArticleQuery aq) {
        return PageUtils.getPageResult(getPageInfo(aq));
    }
	
   private PageInfo<Article> getPageInfo(ArticleQuery aq) {
       PageHelper.startPage(aq.getPageNum(), aq.getPageSize());
       List<Article> sysMenus = articleDao.queryByCondition(aq);
       return new PageInfo<Article>(sysMenus);
    }

这里用到的工具类PageUtils定义为

import com.github.pagehelper.PageInfo;
public class PageUtils {
    
    /**
     * 将分页信息封装到统一的接口
     * @param pageInfo
     * @return
     */
    public static PageResult getPageResult(PageInfo<?> pageInfo) {
        PageResult pageResult = new PageResult();
        pageResult.setPageNum(pageInfo.getPageNum());
        pageResult.setPageSize(pageInfo.getPageSize());
        pageResult.setTotalSize(pageInfo.getTotal());
        pageResult.setTotalPages(pageInfo.getPages());
        pageResult.setContent(pageInfo.getList());
        return pageResult;
    }
}

对应的PageResulto类封了分页所需要的信息：

public class PageResult<T> {
    /**
     * 当前页码
     */
    private int pageNum;
    /**
     * 每页数量
     */
    private int pageSize;
    /**
     * 记录总数
     */
    private long totalSize;
    /**
     * 页码总数
     */
    private int totalPages;
    /**
     * 数据模型
     */
    private List<T> content;
    public int getPageNum() {
        return pageNum;
    }
    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }
    public int getPageSize() {
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public long getTotalSize() {
        return totalSize;
    }
    public void setTotalSize(long totalSize) {
        this.totalSize = totalSize;
    }
    public int getTotalPages() {
        return totalPages;
    }
    public void setTotalPages(int totalPages) {
        this.totalPages = totalPages;
    }
    public List<T> getContent() {
        return content;
    }
    public void setContent(List<T> content) {
        this.content = content;
    }
}

8. Controller层

    @PostMapping("api/findPage")
    public Object findPage(ArticleQuery aq) {
        return articleService.findPage(aq);
    }

9. 用Postman工具进行测试

10. 打印的sql语句与显示结果

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@119290b9] will not be managed by Spring
==>  Preparing: SELECT count(0) FROM articles
==> Parameters: 
<==    Columns: count(0)
<==        Row: 5
<==      Total: 1
==>  Preparing: -- 注意ORDER BY后面用的是$而不是# select * from articles ORDER BY comment_num desc LIMIT ?
==> Parameters: 2(Integer)
<==    Columns: article_id, user_id, title, name, view_num, comment_num, category_id, create_time
<==        Row: 98, 1, 444, 444.md, 0, 9, 1, 2020-10-01 10:55:45
<==        Row: 83, 1, daidai, daidai.md, 0, 4, 1, 2020-09-30 11:09:42
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1a345298]
Article(articleId=98, userId=1, title=444, name=444.md, viewNum=0, commentNum=9, categoryId=1, createTime=2020-10-01 10:55:45.0)
Article(articleId=83, userId=1, title=daidai, name=daidai.md, viewNum=0, commentNum=4, categoryId=1, createTime=2020-09-30 11:09:42.0)

成功打印出两行数据，且是按comment_num倒序排列，打印的sql语句也显示正确。

11.总结

这里没有用mybatis-plus是想自己多实际写一写sql语句，同时复习一下之前的知识，实际项目中仍然以原生sql语句为主。感兴趣的网友，可以参考。