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>
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);
}
}
结果如下:
结果也是正确的。
使用MyBatis-Plus来实现分页查询还是挺方便的。
写博客是博主记录自己的学习过程,如果有错误,请指正,谢谢!
附加
博主看评论区有小伙伴问怎么实现分页?博主花了几个小时去写了一个小栗子,大家可以参考参考。
更多推荐
所有评论(0)