MyBatis-Plus实现分页的方法使用详解

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测试数据' , '2021-01-23 09:33:36' , '2021-01-23 09:33:36' , 0);

INSERT INTO `t_user` VALUES (2, 'sky' , '天蓝' , '123@qq测试数据' , '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://HdhCmsTestw3.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 /> <!-- lookup parent from repository -->

     </ 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 >

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;

     }

}

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;

}

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);

}

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 {

}

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);

     }

}