MyBatis-Plus--分页--方法/教程/实例

待我称王封你为后i 2023-09-30 10:26 1阅读 0赞

原文网址：

## 简介 ##

本文介绍MyBatis-Plus的分页的方法。

包括：

1.  不传参数时的默认结果
2.  查询不存在的数据
3.  手动包装page
4.  自定义SQL

## 建库建表 ##

DROP DATABASE IF EXISTS mp;
    CREATE DATABASE mp DEFAULT CHARACTER SET utf8;
    USE mp;
    
    DROP TABLE IF EXISTS `t_user`;
    SET NAMES utf8mb4;
    
    CREATE TABLE `t_user`
    (
        `id`           BIGINT(0) NOT NULL AUTO_INCREMENT,
        `user_name`    VARCHAR(64) NOT NULL COMMENT '用户名（不能重复）',
        `nick_name`    VARCHAR(64) NULL COMMENT '昵称（可以重复）',
        `email`        VARCHAR(64) COMMENT '邮箱',
        `create_time`  DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        `update_time`  DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        `deleted_flag` BIGINT(0) NOT NULL DEFAULT 0 COMMENT '0：未删除 其他：已删除',
        PRIMARY KEY (`id`) USING BTREE,
        UNIQUE KEY `index_user_name_deleted_flag` (`user_name`, `deleted_flag`),
        KEY `index_create_time`(`create_time`)
    ) ENGINE = InnoDB COMMENT = '用户';
    
    INSERT INTO `t_user` VALUES (1, 'knife', '刀刃', 'abc@qq.com', '2021-01-23 09:33:36', '2021-01-23 09:33:36', 0);
    INSERT INTO `t_user` VALUES (2, 'sky', '天蓝', '123@qq.com', '2021-01-24 18:12:21', '2021-01-24 18:12:21', 0);

## 依赖 ##

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.3.12.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.example</groupId>
        <artifactId>MyBatis-Plus_Simple</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>MyBatis-Plus_Simple</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.github.xiaoymin</groupId>
                <artifactId>knife4j-spring-boot-starter</artifactId>
                <version>3.0.3</version>
            </dependency>
    
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

## 配置 ##

**application.yml**

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/mp?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        username: root
        password: 222333
    
    #mybatis-plus配置控制台打印完整带参数SQL语句
    mybatis-plus:
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

**分页插件配置（必须）**

package com.example.demo.config;
    
    import com.baomidou.mybatisplus.annotation.DbType;
    import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
     
    @Configuration
    public class MyBatisPlusConfig {
    
        /**
         * 分页插件
         */
        @Bean
        public MybatisPlusInterceptor mybatisPlusInterceptor() {
            MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
            interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
            return interceptor;
        }
    }

## 代码 ##

### Entity ###

package com.example.demo.user.entity;
    
    import com.baomidou.mybatisplus.annotation.IdType;
    import com.baomidou.mybatisplus.annotation.TableId;
    import com.baomidou.mybatisplus.annotation.TableLogic;
    import com.baomidou.mybatisplus.annotation.TableName;
    import com.baomidou.mybatisplus.extension.activerecord.Model;
    import lombok.Data;
    
    import java.time.LocalDateTime;
    
    @Data
    @TableName(value = "t_user")
    public class User {
        @TableId(value = "id", type = IdType.AUTO)
        private Long id;
    
        /**
         * 用户名（不能重复）
         */
        private String userName;
    
        /**
         * 昵称（可以重复）
         */
        private String nickName;
    
        /**
         * 邮箱
         */
        private String email;
    
        /**
         * 创建时间
         */
        private LocalDateTime createTime;
    
        /**
         * 修改时间
         */
        private LocalDateTime updateTime;
    
        /**
         * 0：未删除 其他：已删除
         */
        @TableLogic(delval = "id")
        private Long deletedFlag;
    }

### Mapper ###

package com.example.demo.user.mapper;
    
    import com.baomidou.mybatisplus.core.conditions.Wrapper;
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.core.toolkit.Constants;
    import com.example.demo.user.entity.User;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface UserMapper extends BaseMapper<User> {
        @Select("SELECT * FROM t_user ${ew.customSqlSegment}")
        IPage<User> findUser(IPage<User> page, @Param(Constants.WRAPPER) Wrapper wrapper);
    }

### Service ###

**接口**

package com.example.demo.user.service;
    
    import com.baomidou.mybatisplus.extension.service.IService;
    import com.example.demo.user.entity.User;
    
    public interface UserService extends IService<User> {
    }

**实现**

package com.example.demo.user.service.impl;
    
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.example.demo.user.entity.User;
    import com.example.demo.user.mapper.UserMapper;
    import com.example.demo.user.service.UserService;
    import org.springframework.stereotype.Service;
    
    @Service
    public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    }

### Controller ###

package com.example.demo.user.controller;
    
    import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.core.toolkit.Wrappers;
    import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.example.demo.user.entity.User;
    import com.example.demo.user.mapper.UserMapper;
    import com.example.demo.user.service.UserService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @Api(tags = "分页")
    @RestController
    @RequestMapping("page")
    public class PageController {
    
        @Autowired
        private UserService userService;
    
        // 我为了简单直接注入mapper，项目中controller要通过service调mapper
        @Autowired
        private UserMapper userMapper;
    
        @ApiOperation("不传参数")
        @GetMapping("noParam")
        public IPage<User> noParam(Page<User> page) {
            return userService.page(page);
        }
    
        @ApiOperation("查不存在的数据")
        @GetMapping("notExist")
        public IPage<User> notExist(Page<User> page) {
            // 如果查出来为空，会返回入参page里边的数据，比如：current，size等。不需要自己判空。
            return userService.lambdaQuery()
                    .eq(User::getUserName, "abcd")
                    .page(page);
        }
    
        @ApiOperation("手动包装")
        @GetMapping("manualPack")
        public IPage<User> manualPack(Page<User> page) {
            List<User> skyList = userService.lambdaQuery()
                    .eq(User::getUserName, "sky")
                    .list();
    
            // 因为Page是IPage的实现类，所以可以直接返回page
            // 也可以自己new 一个Page，然后设置值，不过这样就麻烦了
            return page.setRecords(skyList);
        }
    
        @ApiOperation("自定义SQL")
        @GetMapping("customSQL")
        public IPage<User> customSQLPage(Page<User> page) {
            LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
            wrapper.eq(User::getUserName, "sky");
    
            // 这样写会报错：MybatisPlusException: can not use this method for "getCustomSqlSegment"
            // LambdaQueryChainWrapper<User> wrapper = userService.lambdaQuery()
            //         .eq(User::getUserName, "sky");
    
            return userMapper.findUser(page, wrapper);
        }
    }

## 测试 ##

访问knife4j：[http://localhost:8080/doc.html][http_localhost_8080_doc.html]

![756359b50bfd49b18ea3804bc9b8e7af.png][]

**1. 不传参数**

![c1c5fd8e170c475c95d502dcac773afe.png][]

**2. 查不存在的数据**

![9889777b6c5940a895c63d6a8d7a29b2.png][]

**3. 手动包装**

![545a791e04204820baea0003687aa9bc.png][]

**4. 自定义SQL**

![f5057436a4a84850acf29c3b1a913d5d.png][]

[http_localhost_8080_doc.html]: http://localhost:8080/doc.html
[756359b50bfd49b18ea3804bc9b8e7af.png]: https://img-blog.csdnimg.cn/756359b50bfd49b18ea3804bc9b8e7af.png
[c1c5fd8e170c475c95d502dcac773afe.png]: https://img-blog.csdnimg.cn/c1c5fd8e170c475c95d502dcac773afe.png
[9889777b6c5940a895c63d6a8d7a29b2.png]: https://img-blog.csdnimg.cn/9889777b6c5940a895c63d6a8d7a29b2.png
[545a791e04204820baea0003687aa9bc.png]: https://img-blog.csdnimg.cn/545a791e04204820baea0003687aa9bc.png
[f5057436a4a84850acf29c3b1a913d5d.png]: https://img-blog.csdnimg.cn/f5057436a4a84850acf29c3b1a913d5d.png