SpringBoot使用Mybatis-plus及分页功能的细节部分
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.example</groupId>
<artifactId>other</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--数据库相关-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- SQL 执行过程 分析打印-->
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.0</version>
</dependency>
<!--数据库相关 over-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.7</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
</project>
application.yml
spring:
datasource:
# url: jdbc:mysql://localhost:33006/tt?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
# driver-class-name: com.mysql.cj.jdbc.Driver
# url和driver-class-name 改成p6spy的,就可以查看sql执行信息
url: jdbc:p6spy:mysql://localhost:33006/tt?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
type: com.alibaba.druid.pool.DruidDataSource
username: root
password: liuyalong
logging:
level:
root: error
#mybatis-plus配置控制台SQL语句
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
spy.properties
sql语句执行分析p6spy的匹配文件
# sql语句执行分析p6spy匹配文件
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2
MybatisPlusConfig.java
必须配置这个分页插件才是物理分页
package com.yalong.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @author liuyalong
* 分页插件
*/
@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量，默认 500 条，-1 不受限制
paginationInterceptor.setLimit(100);
// 开启 count 的 join 优化,只针对部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
}
pojo
package com.yalong.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.io.Serializable;
/**
* @author liuyalong
* 注解@TableName("t_metas"):设置表名字t_metas
* 注解@TableId(value = "mid",type = IdType.AUTO):表示此字段为表的主键,value属性可以指定表中字段,
* 注解@TableId(value = "mid",type = IdType.ASSIGN_ID):默认使用雪花算法生成id
* 当此字段为自增(IdType.AUTO)时,如果给它设设置指定值会不起作用
* 注解@TableField(value = "sort") 表明这是一个表中的字段,value指定表中的列名
* 注解@TableField(exist = false) 表示这个字段不是表中的字段
*/
@Data
@TableName("t_metas")
public class Metas implements Serializable {
@TableId(value = "mid",type = IdType.ASSIGN_ID)
private Long mid;
//@TableId(value = "mid",type = IdType.AUTO)
//private Integer mid;
@TableField(value = "sort")
private Integer sort;
private Integer parent;
private String name;
private String slug;
private String type;
private String description;
@TableField(exist = false)
private String fuck;
}
mapper
package com.yalong.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yalong.pojo.Metas;
/**
* @author liuyalong
*/
public interface MetasMapper extends BaseMapper<Metas> {
}
启动类
@MapperScan("com.yalong.mapper")
@SpringBootApplication
public class MyApplication {
public static void main(String[] args) {
SpringApplication.run(MyApplication.class, args);
}
}
Test
@Test
public void mybatisPlus() {
// HashMap<String, Object> hashMap = new HashMap<>();
// hashMap.put("mid",1);
// metasMapper.deleteByMap(hashMap);
//
// System.out.println("------查询id 6--------");
// Metas metas = metasMapper.selectById(6);
// System.out.println(metas);
// System.out.println("------插入数据--------");
// Metas metas1 = new Metas();
// metas1.setMid(998);
// metas1.setFuck("wulala1");
// metas1.setName("我叫刘亚龙11");
// metas1.setParent(5);
// metas1.setSort(0);
// int insert = metasMapper.insert(metas1);
// System.out.println("插入结果:"+insert);
//查询mid列,值大于100的
QueryWrapper<Metas> metasQueryWrapper = new QueryWrapper<>();
metasQueryWrapper.gt("mid", 100);
// System.out.println("-----条件查询1-----");
// List<Metas> metas2 = metasMapper.selectList(metasQueryWrapper);
// List<Metas> userList = metasMapper.selectList(null);
// metas2.forEach(System.out::println);
System.out.println("-----条件查询2-----");
// 注意这里的metasPage 和 metasPage1 是同一个对象,
// 即selectPage传入的对象和返回的对象是同一个
//这里需要去配置分页插件才是真正的物理分页,否则是内存分页,即查询全表,然后分页
Page<Metas> metasPage = new Page<>(100, 10);
Page<Metas> metasPage1 = metasMapper.selectPage(metasPage, metasQueryWrapper);
//不进行 count sql 优化，解决 MP 无法自动优化 SQL 问题，这时候你需要自己查询 count 部分
// metasPage.setOptimizeCountSql(false);
//设置setSearchCount(false),分页查询不会执行count(1)
// metasPage.setSearchCount(false);
/* System.out.println(metasPage==metasPage1);
System.out.println(metasPage.equals(metasPage1));*/
System.out.println("================= 相关的分页信息 ==================");
System.out.println("总条数:" + metasPage1.getTotal());
System.out.println("当前页码:" + metasPage1.getCurrent());
System.out.println("总页数:" + metasPage1.getPages());
System.out.println("每页显示条数:" + metasPage1.getSize());
System.out.println("是否有上一页:" + metasPage1.hasPrevious());
System.out.println("是否有下一页:" + metasPage1.hasNext());
//获取当页数据
List<Metas> records = metasPage1.getRecords();
records.forEach(System.out::println);
}
你要是觉得写的还不错,就点个关注,可以评论区留下足迹,以后方便查看.
你要是觉得写的很辣鸡,评论区欢迎来对线!
欢迎转载!
