(七)SpringBoot2.0整合Mybatis-Plus及分页查询

一. 项目示例

1. pom文件引入

只需要引入SpringBoot集成好的mybatis-plus依赖及github的pagehelper依赖即可。

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.4</version>
        </dependency>

2. application.yml配置

引入Mybatis-Plus的相关配置信息，日志级别设置为debug，开发环境方便。

#mybatis plus
mybatis-plus:
  mapper-locations: classpath:mapper/*Mapper.xml
  #实体扫描，多个package用逗号或者分号分隔
  typeAliasesPackage: com.hejr.springboot2_003.entiry
  #typeEnumsPackage: com.baomidou.springboot.entity.enums
  global-config:
    #刷新mapper 调试神器
    db-config:
      #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
      idtype: 0
      #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
      field-strategy: not_empty
      #驼峰下划线转换
      column-underline: true
      #数据库大写下划线转换
      #capitalmode: true
      #逻辑删除配置
      logic-delete-value: 1
      logic-not-delete-value: 0
      db-type: mysql
    refresh: true
      #自定义填充策略接口实现
      #metaobjecthandler: com.baomidou.springboot.xxx
      #自定义SQL注入器
  #sqlinjector: com.baomidou.mybatisplus.extension.injector.LogicSqlInjector
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false

logging.level.com.hejr.springboot2_003.mapper: debug

3. Mybatis-Plus配置类

该类主要配置mapper的扫描路径，配置mybatis的分页插件pageHelper及方言等其他。

/**
* @desc: Mybatis-Plus配置类
* @author:hejr
* @date:2019/4/8
*/
@Configuration
@MapperScan("com.hejr.springboot2_003.mapper")//这个注解，作用相当于下面的@Bean MapperScannerConfigurer，2者配置1份即可
public class MybatisPlusConfig {

    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setDialectType("mysql");
        return paginationInterceptor;
    }

    /**
     * sql注入器  逻辑删除插件
     *
     * @return
     */
    @Bean
    public ISqlInjector iSqlInjector() {
        return new LogicSqlInjector();
    }

    /**
     * sql性能分析插件，输出sql语句及所需时间
     *
     * @return
     */
    @Bean
    @Profile({"dev", "test"})// 设置 dev test 环境开启
    public PerformanceInterceptor performanceInterceptor() {
        PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
        performanceInterceptor.setFormat(true);
        return performanceInterceptor;
    }

    /**
     * 配置mybatis的分页插件pageHelper
     * @return
     */
    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("offsetAsPageNum", "true");
        properties.setProperty("rowBoundsWithCount", "true");
        properties.setProperty("reasonable", "true");
        properties.setProperty("dialect", "mysql");
        pageHelper.setProperties(properties);
        return pageHelper;
    }

    /**
     * 乐观锁插件
     *
     * @return
     */
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }
}

4. Service层

由于使用了Mybatis-Plus，则Service层与之前的接口结构也有区别，继承了Mybatis-Plus的IService接口，该接口中有好多已经定义好的并实现的接口。基本的操作方法可以直接调用，无需再自己重写。

如此实现类直接调用现成的方法即可，简化了代码量。

@Service("userService")
@Data
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {


    @Override
    public Object addUser(String userName) {
        User user = new User(null, userName);
        int ret = baseMapper.insert(user);
        return ret;
    }

    @Override
    public Object findUserList(Integer pageNo, Integer pageSize) {
        IPage<User> page = new Page<>(pageNo, pageSize);
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("USER_ID");
        IPage<User> userIPage = baseMapper.selectPage(page, wrapper);
        return userIPage;
    }
}

5. 数据库表结构

创建一个TB_USER表，里面添加了12条数据，作为分页测试。直接复制如下建表语句执行即可。

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50722
Source Host           : 192.168.0.248:3306
Source Database       : springboot2.0_db

Target Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001

Date: 2019-04-08 09:44:31
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `USER_ID` int(20) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', '何金荣');
INSERT INTO `tb_user` VALUES ('2', '何金荣1');
INSERT INTO `tb_user` VALUES ('3', '何金荣2');
INSERT INTO `tb_user` VALUES ('4', '何金荣3');
INSERT INTO `tb_user` VALUES ('5', '何金荣4');
INSERT INTO `tb_user` VALUES ('6', '何金荣5');
INSERT INTO `tb_user` VALUES ('7', '何金荣6');
INSERT INTO `tb_user` VALUES ('8', '何金荣7');
INSERT INTO `tb_user` VALUES ('9', '何金荣8');
INSERT INTO `tb_user` VALUES ('10', '何金荣11');
INSERT INTO `tb_user` VALUES ('11', '何金荣9');
INSERT INTO `tb_user` VALUES ('12', '何金荣10');

6. 分页测试

http://localhost:8090/findUserList?pageNo=1&pageSize=5

{"message":"获取用户列表成功...",
"code":200,
"result":{"records":[
{"userId":1,"userName":"何金荣"},
{"userId":2,"userName":"何金荣1"},
{"userId":3,"userName":"何金荣2"},
{"userId":4,"userName":"何金荣3"},
{"userId":5,"userName":"何金荣4"}],
"total":12,"size":5,"current":1,"pages":3}}

http://localhost:8090/findUserList?pageNo=2&pageSize=5

{"message":"获取用户列表成功...",
"code":200,
"result":{"records":[
{"userId":6,"userName":"何金荣5"},
{"userId":7,"userName":"何金荣6"},
{"userId":8,"userName":"何金荣7"},
{"userId":9,"userName":"何金荣8"},
{"userId":10,"userName":"何金荣11"}],"total":12,"size":5,"current":2,"pages":3}}

二. 源码下载

https://gitee.com/hejr.cn.com/SpringBoot2.0_2019/tree/master/springboot2_003

下一篇：(八)SpringBoot2.0整合Mybatis-Plus多数据源及代码生成器