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语句为主。感兴趣的网友，可以参考。