MybatisPlus集成baomidou-dynamic,多数据源配置使用、MybatisPlus分页分组等操作示例
2024-01-19 16:45:01
Mybatisplus特性
无侵入:只做增强,不做改变,引入不会影响现有工程,如丝般光滑,损失小:启动后基本自动注入 CURD,性能基本无损失,直接面向对象操作强大 CRUD 操作:内置通用 Mapper、通用 Service,单表的大部分只能通过少量配置来实现 CRUD 操作,更强大的条件结构,支持满足各种使用需求,支持各种使用需求 Lambda 形式调用:通过 Lambda 表达式,方便编写各种查询条件,无需担心字段错误,支持主键自动生成:支持多达 4 主要策略(唯一的分布式) ID 生成器 - Sequence),可自由配置,完美解决关键问题支持支持完美解决关键问题 ActiveRecord 模式:支持 ActiveRecord 形式调用,实体类只需继承 Model 类可以强大 CRUD 操作支持自定义全局通用操作:支持注入全局通用方法( Write once, use anywhere )内置代码生成器:使用代码或 Maven 可以快速生成插件 Mapper 、 Model 、 Service 、 Controller 基于层代码,支持模板引擎,有更多的自定义配置等待您使用内置的分页插件: MyBatis 物理分页,开发者不需要关心具体操作,配置好插件后,写分页等同于普通 List 支持各种数据库的分页插件查询:支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 内置性能分析插件等多种数据库:可输出 SQL 句子及其执行时间,建议在开发测试时启用此功能,内置全局拦截插件可以快速找到慢查询:提供全表 delete 、 update 操作智能分析阻断,也可以定制拦截规则,防止误操作
Mybatisplus支持数据库MySQL,Oracle,DB2,H2,HSQL,SQLite,PostgreSQL,SQLServer,Phoenix,Gauss ,ClickHouse,Sybase,OceanBase,Firebird,Cubrid,Goldilocks,csiidb,informix,TDengine,redshift
大梦数据库、虚谷数据库、全国人大金仓数据库、南大通用(华库)数据库、南大通用数据库、神通数据库、汉高数据库、优炫数据库、星瑞格数据库
MybatisPlus 架构多数据源应用场景1、业务需求需要操作多个DB场景。例如,在下订单时,您需要从用户库中查询用户信息,并将订单插入订单库;2、读写分离场景;有两种常见的实现方案:
- AOP + ThreadLocal ,如:Mybatis-plus的多数据源(dynamic-datasource);
- 语义分析,如:客户端侧:ShardingSphere-Jdbc,服务端侧:ShardingSphere-Proxy,阿里云,腾讯云proxy。
<dependencies> <!--springboot依赖于mybatisplus集成Springboot起步--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.2</version> </dependency> <!--MySQL 驱动依赖--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> <!--druid 数据连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.20</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.3.6</version> </dependency> </dependencies>
配置配置文件
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImplspring.datasource.dynamic.primary=mysqlspring.datasource.dynamic.strict=falsespring.datasource.dynamic.datasource.mysql.url=jdbc:mysql://192.168.0.111:3306/database?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false¤tSchema=publicspring.datasource.dynamic.datasource.mysql.username=rootspring.datasource.dynamic.datasource.mysql.password=123456spring.datasource.dynamic.datasource.mysql.driverClassName=com.mysql.cj.jdbc.Driverspring.datasource.dynamic.datasource.mysql.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.dynamic.datasource.mysql.druid.maxActive=300spring.datasource.dynamic.datasource.mysql.druid.initialSize=20spring.datasource.dynamic.datasource.mysql.druid.maxWait=6000spring.datasource.dynamic.datasource.mysql.druid.minIdle=20spring.datasource.dynamic.datasource.mysql.druid.timeBetweenEvictionRunsMillis=60000spring.datasource.dynamic.datasource.mysql.druid.minEvictableIdleTimeMillis=30000spring.datasource.dynamic.datasource.mysql.druid.validationQuery=select 'x'spring.datasource.dynamic.datasource.mysql.druid.testWhileIdle=truespring.datasource.dynamic.datasource.mysql.druid.testOnBorrow=truespring.datasource.dynamic.datasource.mysql.druid.testOnReturn=falsespring.datasource.dynamic.datasource.postgresql.url= jdbc:postgresql://127.0.0.1:5432/database?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false¤tSchema=publicspring.datasource.dynamic.datasource.postgresql.username=postgresspring.datasource.dynamic.datasource.postgresql.password=123456spring.datasource.dynamic.datasource.postgresql.driverClassName=org.postgresql.Driverspring.datasource.dynamic.datasource.postgresql.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.dynamic.datasource.postgresql.druid.maxActive=300spring.datasource.dynamic.datasource.postgresql.druid.initialSize=20spring.datasource.dynamic.datasource.postgresql.druid.maxWait=6000spring.datasource.dynamic.datasource.postgresql.druid.minIdle=20spring.datasource.dynamic.datasource.postgresql.druid.timeBetweenEvictionRunsMillis=60000spring.datasource.dynamic.datasource.postgresql.druid.minEvictableIdleTimeMillis=30000spring.datasource.dynamic.datasource.postgresql.druid.validationQuery=select 'x'spring.datasource.dynamic.datasource.postgresql.druid.testWhileIdle=truespring.datasource.dynamic.datasource.postgresql.druid.testOnBorrow=truespring.datasource.dynamic.datasource.postgresql.druid.testOnReturn=false
配置类
解决分页失效的问题
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@MapperScan("com.ais.**.mapper.**")public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor()); return interceptor; }}
示例代码实体类@TableName("tableName")@TableField@TableIdMapper参考代码
import com.UserEntity;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.extension.plugins.pagination.Page;import lombok.extern.slf4j.Slf4j;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import javax.annotation.Resource;import java.util.ArrayList;import java.util.List;@Slf4j@RunWith(SpringRunner.class)@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)public class MybatisTest { @Resource private UserMapper mapper; /** * 分页操作 Mysql MybatisPlus */ @Test public void page() { IPage page = new Page(2, 2); IPage iPage = mapper.selectPage(page, null); System.out.println(iPage.getRecords()); } /** * PGSQL SQL分页自定义 */ @Test public void pagePg() { IPage page = new Page(1, 1); IPage iPage = mapper.selectAll(page); System.out.println(iPage.getRecords()); } /** * 分页加排序降序 */ @Test public void sort() { IPage page = new Page(1, 10); QueryWrapper<Entity> queryWrapper = new QueryWrapper<>(); //降序 queryWrapper.orderByDesc("center_Id"); ///升序//// queryWrapper.orderByAsc("center_Id"); IPage iPage = mapper.selectPage(page, queryWrapper); System.out.println(iPage.getRecords()); } /** * 分页加排序降序 lambda 表达式 */ @Test public void lambdaSort() { IPage page = new Page(1, 10); LambdaQueryWrapper<Entity> queryWrapper = new LambdaQueryWrapper<>(); //降序 queryWrapper.orderByDesc(Entity::getCenterId); ///升序//// queryWrapper.orderByAsc(Entity::getCenterId); IPage iPage = mapper.selectPage(page, queryWrapper); System.out.println(iPage.getRecords()); } /** * 过滤in条件 lambda 表达式 */ @Test public void selectIn() { IPage page = new Page(1, 10); LambdaQueryWrapper<Entity> queryWrapper = new LambdaQueryWrapper<>(); List<Long> ids = new ArrayList<>(); ids.add(1l); ids.add(2l); ids.add(3l); ids.add(4l); queryWrapper.in(Entity::getCenterId, ids);// queryWrapper.notIn(Entity::getCenterId, ids); IPage iPage = mapper.selectPage(page, queryWrapper); System.out.println(iPage.getRecords()); } /** * 过滤in条件 lambda 表达式 */ @Test public void selectInSql() { IPage page = new Page(1, 10); LambdaQueryWrapper<Entity> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.inSql(Entity::getCenterId, "select center_Id from center where center_Id>5");// queryWrapper.notIn(Entity::getCenterId, ids); IPage iPage = mapper.selectPage(page, queryWrapper); System.out.println(iPage.getRecords()); } @Test public void select() { IPage page = new Page(1, 10); LambdaQueryWrapper<Entity> queryWrapper = new LambdaQueryWrapper<>(); IPage iPage = mapper.selectPage(page, queryWrapper); System.out.println(iPage.getRecords()); } /** * max 加分组 */ @Test public void selectMax() { IPage page = new Page(1, 10); QueryWrapper<Entity> queryWrapper = new QueryWrapper<>(); queryWrapper.select("max(center_id) as center_id").groupBy("created_By"); IPage iPage = mapper.selectPage(page, queryWrapper); System.out.println(iPage.getRecords()); } /** * count 加分组 */ @Test public void selectCount() { IPage page = new Page(1, 10); QueryWrapper<Entity> queryWrapper = new QueryWrapper<>(); queryWrapper.select("count(center_id) as count").groupBy("created_By"); mapper.selectPage(page, queryWrapper); }}