mybatis-plus分页查询

推荐：MyBatis Plus汇总

mybatis-plus分页查询

首先创建一个数据库表，如下图所示：

 

 然后创建一个Spring Boot项目，pom.xml和配置如下：

<?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>org.kaven</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

application.yaml配置：

spring:
  application:
    name: mybatis-plus
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: ITkaven@123
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false

server:
  port: 8085

logging:
  level:
    root: warn
    com.kaven.mybatisplus.dao: trace
  pattern:
    console: '%p%m%n'

mybatis-plus:
  mapper-locations: classpath:mappers/*.xml

实体类User：

package com.kaven.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@TableName("user")
@Data
public class User {

    @TableId
    private String id;

    @TableField("username")
    private String username;

    @TableField("password")
    private String password;

    @TableField("age")
    private Integer age;

    /**
     * 使用 @TableField(exist = false) ，表示该字段在数据库中不存在 ，所以不会插入数据库中
     * 使用 transient 、 static 修饰属性也不会插入数据库中
     */
    @TableField(exist = false)
    private String phone;
}

Mapper接口UserMapper：

package com.kaven.mybatisplus.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kaven.mybatisplus.entity.User;
import org.springframework.stereotype.Component;


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

启动类：

package com.kaven.mybatisplus;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "com.kaven.mybatisplus.dao")
public class AppRun {
    public static void main(String[] args) {
        SpringApplication.run(AppRun.class , args);
    }
}

@MapperScan(basePackages = "com.kaven.mybatisplus.dao")这个一定要加上。

我们先在数据库中添加几行数据，方便演示。

 

 为了使用MyBatis-Plus来实现分页查询，还需要一些配置。

package com.kaven.mybatisplus.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        paginationInnerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

项目结构如下图：

 现在来使用一下分页查询方法selectPage()。

package com.kaven.mybatisplus.dao;

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.plugins.pagination.Page;
import com.kaven.mybatisplus.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;



@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperPageTest {

    @Autowired
    private UserMapper userMapper;


    @Test
    public void selectPage(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        userLambdaQueryWrapper.like(User::getUsername , "k");

        Page<User> userPage = new Page<>(1 , 2);
        IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+userIPage.getPages());
        System.out.println("总记录数： "+userIPage.getTotal());
        userIPage.getRecords().forEach(System.out::println);
    }
}

结果如下：

 结果是正确的，可以看到该方法执行了两条sql，一条是查询总记录数，一条是查询我们需要的数据。

如果我们并不需要总记录数，查询总记录数就完全没有必要，因为它也需要耗时，其实可以设置不查询总记录数。

package com.kaven.mybatisplus.dao;

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.plugins.pagination.Page;
import com.kaven.mybatisplus.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;



@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperPageTest {

    @Autowired
    private UserMapper userMapper;


    @Test
    public void selectPage(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        userLambdaQueryWrapper.like(User::getUsername , "k");

        Page<User> userPage = new Page<>(1 , 2 , false);
        IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+userIPage.getPages());
        System.out.println("总记录数： "+userIPage.getTotal());
        userIPage.getRecords().forEach(System.out::println);
    }
}

Page<User> userPage = new Page<>(1 , 2 , false);在这里设置为false即可。

结果如下：

 从上图可以看到只有一条sql，总页数和总记录数都为0，因为我们设置了不需要去查询这些信息。

再来演示一下selectMapsPage()的用法。

package com.kaven.mybatisplus.dao;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
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.kaven.mybatisplus.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperPageTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void selectMapsPage(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        userLambdaQueryWrapper.select(User::getUsername).like(User::getUsername , "k");

        Page<Map<String , Object>> mapPage = new Page<>(1 , 2 , false);
        IPage<Map<String , Object>> mapIPage = userMapper.selectMapsPage(mapPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+mapIPage.getPages());
        System.out.println("总记录数： "+mapIPage.getTotal());
        mapIPage.getRecords().forEach(System.out::println);
    }
}

结果如下：

  结果也是正确的，从用法上也可以看出应用场景。

基于可扩展性，MyBatis-Plus还可以使用自定义sql的方法来实现分页查询，这样便于多表等复杂条件。

修改UserMapper接口：

package com.kaven.mybatisplus.dao;

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.kaven.mybatisplus.entity.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.util.List;


@Component
public interface UserMapper extends BaseMapper<User> {
    IPage<User> selectByPage(IPage<User> userPage , @Param(Constants.WRAPPER) Wrapper<User> userWrapper);
}

UserMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.kaven.mybatisplus.dao.UserMapper">
    <select id="selectByPage" resultType="com.kaven.mybatisplus.entity.User">
        select * from user ${ew.customSqlSegment}
    </select>
</mapper>

也可以使用Mybatis的注解来代替xml配置文件（如@Select）。

测试代码：

package com.kaven.mybatisplus.dao;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
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.kaven.mybatisplus.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperPageTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void selectByPage(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        userLambdaQueryWrapper.like(User::getUsername , "k");

        Page<User> mapPage = new Page<>(1 , 2 , false);
        IPage<User> mapIPage = userMapper.selectByPage(mapPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+mapIPage.getPages());
        System.out.println("总记录数： "+mapIPage.getTotal());
        mapIPage.getRecords().forEach(System.out::println);
    }
}

结果如下：

 结果也是正确的。