MyBatis-Plus 之分页查询

灰太狼 2022-12-13 11:28 237阅读 0赞

推荐：[MyBatis Plus汇总][MyBatis Plus]

## MyBatis-Plus 之分页查询 ##

首先创建一个数据库表，如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]

然后创建一个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>

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")`这个一定要加上。

我们先在数据库中添加几行数据，方便演示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]  
为了使用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);
        }
    }

项目结构如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]

现在来使用一下分页查询方法`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);
        }
    }

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70_pic_center]

结果是正确的，可以看到该方法执行了两条`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`即可。

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]  
从上图可以看到只有一条`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);
        }
    }

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70_pic_center 1]

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

基于可扩展性，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);
        }
    }

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]  
结果也是正确的。

使用MyBatis-Plus来实现分页查询还是挺方便的。

写博客是博主记录自己的学习过程，如果有错误，请指正，谢谢！

## 附加 ##

博主看评论区有小伙伴问怎么实现分页？博主花了几个小时去写了一个小栗子，大家可以参考参考。

*  [前端Vue+ElementUI的Pagination分页组件实现分页展示 & 后端Spring Boot +Mybatis Plus实现分页接口][Vue_ElementUI_Pagination_ _ _Spring Boot _Mybatis Plus]

[MyBatis Plus]: https://kaven.blog.csdn.net/article/details/108982474
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]: /images/20221123/35c1d0164b2e480da084b0fb29bea16c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]: /images/20221123/12a2d31855704a8dbd4a2148f46094e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]: /images/20221123/4d9e4660895943c5866601173ffc78d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/f101c7a9b894438aab55339380981f54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]: /images/20221123/4b210b3b75634c17808a4482c23918d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/e568983eb77f4cc09d0504b9d7a968f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]: /images/20221123/2e9a3b99bc7b4e4d92c357c67753dc7a.png
[Vue_ElementUI_Pagination_ _ _Spring Boot _Mybatis Plus]: https://kaven.blog.csdn.net/article/details/115666805