springBoot和MyBatis-Plus快速开发及分页实战过程

痛定思痛。 2022-04-13 14:26 233阅读 0赞

项目里面用的[MyBatis-Plus][]这个工具，刚开始各种不好使，熟悉了以后感觉还是很强大的

[http://mp.baomidou.com/guide/quick-start.html][http_mp.baomidou.com_guide_quick-start.html]

按官方的教程写了个demo，不过数据库我换成了mysql，里面还有些问题得注意，

#  先打开mysql准备数据 #

现有一张 `User` 表，其表结构如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>age</th> 
   <th>email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Jone</td> 
   <td>18</td> 
   <td>test1@baomidou.com</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Jack</td> 
   <td>20</td> 
   <td>test2@baomidou.com</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Tom</td> 
   <td>28</td> 
   <td>test3@baomidou.com</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Sandy</td> 
   <td>21</td> 
   <td>test4@baomidou.com</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Billie</td> 
   <td>24</td> 
   <td>test5@baomidou.com</td> 
  </tr> 
 </tbody> 
</table>

其对应的数据库 Schema 脚本如下：

DROP TABLE IF EXISTS user;
    
    CREATE TABLE user
    (
    	id BIGINT(20) NOT NULL COMMENT '主键ID',
    	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    	PRIMARY KEY (id)
    );

其对应的数据库 Data 脚本如下：

DELETE FROM user;
    
    INSERT INTO user (id, name, age, email) VALUES
    (1, 'Jone', 18, 'test1@baomidou.com'),
    (2, 'Jack', 20, 'test2@baomidou.com'),
    (3, 'Tom', 28, 'test3@baomidou.com'),
    (4, 'Sandy', 21, 'test4@baomidou.com'),
    (5, 'Billie', 24, 'test5@baomidou.com');

# 使用idea自带的 [Spring Initializr][] 快速初始化一个 Spring Boot 工程 #

## pom文件  ##

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.whale.luo</groupId>
        <artifactId>mybatis-plus</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <packaging>jar</packaging>
    
        <name>mybatis-plus</name>
        <description>Demo project for Spring Boot</description>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.3.RELEASE</version>
            <relativePath/> 
        </parent>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.0.6</version>
            </dependency>
    
            
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.13</version>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

## ** application.yml** ##

# DataSource Config
    spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
        username: root
        password: 12345678

## 实体 ##

@Data
    public class User {
        private Long id;
        private String name;
        private Integer age;
        private String email;
    }

>  注意数据库中的表名默认和实体有着对应关系的，比如  
>  实体User  对应表USER  
>  实体UserAddress对应表USER\_ADDRESS    
>  即表名默认是实体名称每个大小写之间加下划线

当然也可以不使用它这种默认的规则，但需要加@TableName注解 ，比如

@Data
    @TableName("USER")
    public class UserVo {
        private Long id;
        private String name;
        private Integer age;
        private String email;
    }

因此，这两个实体是一样的

我们选择第一个使用吧

## jdbc测试数据 ##

@RestController
    @SpringBootApplication
    @MapperScan("com.whale.luo.mybatisplus")
    public class MybatisPlusApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(MybatisPlusApplication.class, args);
        }
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @RequestMapping("/getUsers")
        public List<Map<String, Object>> getDbType(){
            String sql = "select * from USER";
            List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
            for (Map<String, Object> map : list) {
                Set<Map.Entry<String, Object>> entries = map.entrySet( );
                if(entries != null) {
                    Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
                    while(iterator.hasNext( )) {
                        Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
                        Object key = entry.getKey( );
                        Object value = entry.getValue();
                        System.out.println(key+":"+value);
                    }
                }
            }
            return list;
        }
    
    }

运行，访问地址[http://localhost:8080/getUsers][http_localhost_8080_getUsers]

![20181129215534436.png][]

接下我们就试试用这个插件实现上面的功能

## 先创建UserMapper  ##

> 我们应该继承BaseMappe<T>接口，其中接口中的T应该为我们对应的实体User，不能为其他的，这个插件会自动将根据这个实体去找到它关联的表。

@Component
    public interface UserMapper extends BaseMapper<User> {
    
    }

## 创建接口IUserService及其实现类 ##

在接口中我们也可以声明自己的方法

public interface IUserService extends IService<User> {
        
        List<User> getAll(User user);
        
    }

实现类

最后的Controller如下

@RestController
    @SpringBootApplication
    @MapperScan("com.whale.luo.mybatisplus")
    public class MybatisPlusApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(MybatisPlusApplication.class, args);
        }
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Autowired
        private IUserService iUserService;
    
        @RequestMapping("/getUsers")
        public List<Map<String, Object>> getDbType(){
            String sql = "select * from USER";
            List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
            for (Map<String, Object> map : list) {
                Set<Map.Entry<String, Object>> entries = map.entrySet( );
                if(entries != null) {
                    Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
                    while(iterator.hasNext( )) {
                        Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
                        Object key = entry.getKey( );
                        Object value = entry.getValue();
                        System.out.println(key+":"+value);
                    }
                }
            }
            return list;
        }
    
        @RequestMapping("/getUserList")
        public List<User> getUserList(){
             User user = new User();
            List<User> all = iUserService.getAll(user);
            //List<User> list = iUserService.list(); 也可以用这个方法返回
            return all;
        }
    
    }

运行[http://localhost:8080/getUserList][http_localhost_8080_getUserList]

![20181129221025501.png][]

> 最后注意：一般我们的实体是不能出现在controller层的，所以我们应当在service层把实体转换成vo再返到前台

流程就是这样，从controller中调服务，再从服务中调mapper

过程中我把service注解加到了接口上，一下子走弯了，这个注解应该加到它的实现类上

# 内置分页  #

[http://mp.baomidou.com/guide/page.html][http_mp.baomidou.com_guide_page.html]

直接贴代码

@RestController
    @SpringBootApplication
    @MapperScan("com.whale.luo.mybatisplus")
    public class MybatisPlusApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(MybatisPlusApplication.class, args);
        }
    
        /**
         * 分页插件
         */
        @Bean
        public PaginationInterceptor paginationInterceptor() {
            return new PaginationInterceptor();
        }
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Autowired
        private IUserService iUserService;
    
        @RequestMapping("/getUsers")
        public List<Map<String, Object>> getDbType(){
            String sql = "select * from USER";
            List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
            for (Map<String, Object> map : list) {
                Set<Map.Entry<String, Object>> entries = map.entrySet( );
                if(entries != null) {
                    Iterator<Map.Entry<String, Object>> iterator = entries.iterator( );
                    while(iterator.hasNext( )) {
                        Map.Entry<String, Object> entry =(Map.Entry<String, Object>) iterator.next( );
                        Object key = entry.getKey( );
                        Object value = entry.getValue();
                        System.out.println(key+":"+value);
                    }
                }
            }
            return list;
        }
    
        @RequestMapping("/getUserList")
        public List<User> getUserList(){
             User user = new User();
            List<User> all = iUserService.getAll(user);
            //List<User> list = iUserService.list(); 也可以用这个方法返回
            return all;
        }
    
         @RequestMapping("/selectUserPage")
        public IPage<User> selectUserPage(){
            //Page<User> page = new Page<>(0,0);
            // {"records":[],"total":4,"size":0,"current":1,"searchCount":true,"pages":0}
            // 可以看出"size":0,"current":1,"pages":0 即 一共有0页，每页为0条数据，当前页为第一页
    
            //Page<User> page = new Page<>(0,1);
            //{"records":[{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"}],"total":4,"size":1,"current":1,"searchCount":true,"pages":4}
            //"size":1,"current":1"pages":4   即 一共有4页，每页为1条数据，当前页为第一页
    
            Page<User> page = new Page<>(1,1);
            //{"records":[{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"}],"total":4,"size":1,"current":1,"searchCount":true,"pages":4}
            //可见返回的数据与上条数据一样
            
            //总结new Page<>(1,1);需要传递两个参数，
            // 第一个为current，为当前的页数，默认是从1开始，如果设置为0，它后台是默认请求第一页
            //第二个参数为size ，为每页显示多少条数据,如果为0，则每页为0条数据，所以一般这个值要>=1
            
            IPage<User> userIPage = iUserService.selectUserPage(page);
            return userIPage;
        }
    
    }

分页的时候我们要传一个page参数

总结new Page<>(1,1);需要传递两个参数，
     第一个为current，为当前的页数，默认是从1开始，如果设置为0，它后台是默认请求第一页
    第二个参数为size ，为每页显示多少条数据,如果为0，则每页为0条数据，所以一般这个值要>=1

为了测试方便，我直接把page写死了，实际中应该由http请求获得当前页

public interface IUserService extends IService<User> {
    
        List<User> getAll(User user);
    
        IPage<User>  selectUserPage(Page<User> page);
    
    }

@Service("iUserService")
    public class IUserServiceImpl extends ServiceImpl<UserMapper,User> implements IUserService {
        @Override
        public List<User> getAll(User user) {
            return this.baseMapper.selectList(null);
        }
    
        public IPage<User> selectUserPage(Page<User> page) {
            return this.baseMapper.selectPage(page,null);
        }
    }

运行页面访问 [http://localhost:8080/selectUserPage][http_localhost_8080_selectUserPage]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70][]

# 分页插件 Mybatis-PageHelper    #

这个插件适用于mybatis，也适用于 mybatis-plus ，因此，下面的操作都是通用的

[https://github.com/pagehelper/Mybatis-PageHelper][https_github.com_pagehelper_Mybatis-PageHelper]

[https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md][https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md]使用 \*

[https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md][https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md] 集成springBoot\*

[https://www.cnblogs.com/ljdblog/p/6725094.html][https_www.cnblogs.com_ljdblog_p_6725094.html]

上面加\*号的需要重点看，问题都可在上面找到答案

我的过程如下，还是基于上面接着写

## 1 pom文件加依赖 ##

<dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.3</version>
            </dependency>

##  2、修改配置文件application ##

server:
      port: 8080
    
    spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
        username: root
        password: 12345678
    mybatis:
      mapper-locations: classpath:mapping/*Mapper.xml
      type-aliases-package: com.whale.luo.mybatisdemo.po
    
    #pagehelper
    pagehelper:
        helperDialect: mysql
        reasonable: true
        supportMethodsArguments: true
        params: count=countSql

##  3.1、测试1 ##

还是在springBoot的启动类 MybatisPlusApplication上面测试

[https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md\#2-pagehelperstartpage-静态方法调用][https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md_2-pagehelperstartpage-]

@RequestMapping("/selectUserPage1")
        public List<User> selectUserPage1(){
            //获取第1页，2条内容，默认查询总数count
            PageHelper.startPage(1, 2);
            //紧跟着的第一个select方法会被分页
            List<User> list = iUserService.getAll(null);
            return list;
        }

访问[http://localhost:8080/selectUserPage1][http_localhost_8080_selectUserPage1]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70 1][]

返回两条数据

[{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"},{"id":3,"name":"Tom","age":28,"email":"test3@baomidou.com"}]

## 3.2 pageInfo测试2 ##

@RequestMapping("/selectUserPage2")
        public PageInfo selectUserPage2(){
    
            //获取第1页，10条内容，默认查询总数count
            PageHelper.startPage(1, 1);
            //紧跟着的第一个select方法会被分页
            List<User> list = iUserService.getAll(null);
            //用PageInfo对结果进行包装
            PageInfo pageInfo = new PageInfo(list);
            return pageInfo;
        }

运行 [http://localhost:8080/selectUserPage2][http_localhost_8080_selectUserPage2]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70 2][]

确实获得了一条数据，但还有好多其他东西

如下

\{  
    "pageNum":1,    当前页  
    "pageSize":1,     每页显示多少条数据  
    "size":1,  
    "startRow":1,  
    "endRow":1,"  
    total":4,  
    "pages":4,          一共有4页  
    "list":\[                 返回的list数据  
            \{"id":2,"name":"Jack","age":20,"email":"test2@baomidou.com"\}  
            \],  
    "prePage":0,  
    "nextPage":2,      下一页  
    "isFirstPage":true,     
    "isLastPage":false,  
    "hasPreviousPage":false,    
    "hasNextPage":true,  
    "navigatePages":8,  
    "navigatepageNums":\[1,2,3,4\],  
    "navigateFirstPage":1,  
    "navigateLastPage":4,  
    "lastPage":4,        
    "firstPage":1  
\}

这种方法感觉好一点

[MyBatis-Plus]: http://mp.baomidou.com/
[http_mp.baomidou.com_guide_quick-start.html]: http://mp.baomidou.com/guide/quick-start.html
[Spring Initializr]: https://start.spring.io/
[http_localhost_8080_getUsers]: http://localhost:8080/getUsers
[20181129215534436.png]: /images/20220413/c00e9d49e3564ec884f805aba09c5af2.png
[http_localhost_8080_getUserList]: http://localhost:8080/getUserList
[20181129221025501.png]: /images/20220413/bc5fde576e424b27bf8d1b2eba7440d5.png
[http_mp.baomidou.com_guide_page.html]: http://mp.baomidou.com/guide/page.html
[http_localhost_8080_selectUserPage]: http://localhost:8080/selectUserPage
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70]: /images/20220413/31f6259df78544429dcf701a64ba4aa0.png
[https_github.com_pagehelper_Mybatis-PageHelper]: https://github.com/pagehelper/Mybatis-PageHelper
[https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
[https_www.cnblogs.com_ljdblog_p_6725094.html]: https://www.cnblogs.com/ljdblog/p/6725094.html
[https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md_2-pagehelperstartpage-]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md#2-pagehelperstartpage-%E9%9D%99%E6%80%81%E6%96%B9%E6%B3%95%E8%B0%83%E7%94%A8
[http_localhost_8080_selectUserPage1]: http://localhost:8080/selectUserPage1
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70 1]: /images/20220413/080d8480cbb44d9e8f5e521e871c3700.png
[http_localhost_8080_selectUserPage2]: http://localhost:8080/selectUserPage2
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VvdGFpbA_size_16_color_FFFFFF_t_70 2]: /images/20220413/1d48189289184176ac96091df18488d5.png