Mybatis Plus由于其便利性,在项目中使用越来越多,而且还提供多数据源功能,其切换用的是dynamic-datasource-spring-boot-starter,只要加@DS注解就可以切换,非常方便。对于读写分离分库分表,可以选择shardingsphere无代码侵入解决方案,将shardingsphere交给dynamic-datasource管理,既可以方便切换数据源又可以使用shardingsphere读写分离分库分表等功能。本系列是记录在Spring Boot集成以上功能时遇到的问题,解决办法,可以一起探讨
项目使用的pom依赖
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.16</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>ft-server</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>ft-server</name>
<description>ft-server</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<!-- 由于Mybatis Plus使用了druid作为默认的数据源,分库分表使用的是HikariDataSource作为数据源管理,所以需要排除druid的starter -->
<exclusions>
<exclusion>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- 分库分表,先去掉druid数据源 -->
<!-- <dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.18</version>
</dependency>-->
<!-- web 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.7.16</version>
</dependency>
<!-- Mybatis与JacksonJson时间转换异常处理 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-typehandlers-jsr310</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.5.3.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.5.3.1</version>
</dependency>
<!-- mybatis-plus 依赖 -->
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
<!-- mybatis plus 解决sharding+动态数据源启动报错:Property ‘sqlSessionFactory‘ or ‘sqlSessionTemplate‘ are required -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<!-- mybatis-plus 代码生成器依赖 -->
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-generator -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.3.1</version>
</dependency>
<!-- freemarker 依赖 -->
<!-- https://mvnrepository.com/artifact/org.freemarker/freemarker -->
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.31</version>
</dependency>
<dependency>
<groupId>com.example</groupId>
<artifactId>ft-contract</artifactId>
<version>0.0.1-SNAPSHOT</version>
<scope>compile</scope>
</dependency>
<!-- 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.yml</include>
</includes>
</resource>
<resource>
<directory>src/main/java</directory>
<excludes>
<exclude>**/*.java</exclude>
</excludes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.*</include>
</includes>
</resource>
</resources>
</build>
</project>
yml配置文件
分库分表配置(application-sharding-jdbc.yml):
spring:
sharding-sphere:
datasource:
names: ftdb0,ftdb1
ftdb0:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://localhost:3306/ftdb0?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123mysql
ftdb1:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://localhost:3306/ftdb1?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123mysql
sharding:
tables:
position: # 分库表名
database-strategy:
inline:
sharding-column: id
algorithm-expression: ftdb$->{id % 2}
key-generator:
column: id
type: MYKEY #使用自定义主键id
position_detail:
database-strategy:
inline:
sharding-column: pid
algorithm-expression: ftdb$->{pid % 2}
key-generator:
column: id
type: SNOWFLAKE #使用雪花算法
city:
key-generator:
column: id
type: MYKEY
broadcast-tables: city #广播表
props:
sql:
show: true
# 分库分表是否开启SQL显示,默认值: false
application.yml配置:
server:
port: 8081
spring:
application:
name: ft-server
profiles:
active: sharding-jdbc #分库分表的yml配置,为application-sharding-jdbc.yml文件去掉前面application和.yml结尾
datasource:
dynamic:
primary: master0 # Mybatis Plus动态多数据源设置默认的数据源或者数据源组,默认值即为master
datasource:
master0:
driver-class-name: com.mysql.cj.jdbc.Driver # mysql版本为5.x使用该配置: com.mysql.jdbc.Driver,8.0版本使用:com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/ftdb1?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: root
password: 123mysql
master1:
driver-class-name: com.mysql.cj.jdbc.Driver # mysql版本为5.x使用该配置: com.mysql.jdbc.Driver,8.0版本使用:com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/ftdb0?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: root
password: 123mysql
Mybatis Plus配置类编写
package com.example.ftserver.config;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.example.ftserver.datapermission.MyDataPermission;
import org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractDataSourceAdapter;
import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
/**
* @description
*/
@Configuration
@MapperScan(basePackages = "com.example.ftserver.mapper")
// 需要配置自动注入设置,不然无法使用,SpringBootConfiguration.class是位于 org.apache.shardingsphere.shardingjdbc.spring.boot.包下的类
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class MybatisPlusConfig {
@Autowired
private DynamicDataSourceProperties properties;
/**
* shardingjdbc有四种数据源,需要根据业务注入不同的数据源
*
* <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
* <p>2. 主从数据源: masterSlaveDataSource;
* <p>3. 脱敏数据源:encryptDataSource;
* <p>4. 影子数据源:shadowDataSource
*
*/
@Lazy
@Resource(name ="shardingDataSource") // 这里在IDEA编辑器里面会报红,不影响程序运行
private AbstractDataSourceAdapter shardingDataSource;
/**
* 自定义SQL注入拦截器
* @return
*/
@Bean
public CustomizedSqlInjector sqlInjector() {
return new CustomizedSqlInjector();
}
/**
* 添加分页插件
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 添加数据权限
interceptor.addInnerInterceptor(new MyDataPermission());
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));//如果配置多个插件,切记分页最后添加
return interceptor;
}
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasource = properties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = this.createDataSourceMap(datasource);
dataSourceMap.put("sharding", shardingDataSource);
return dataSourceMap;
}
};
}
@Bean
@Primary
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
// 该参数在高版本中已经废弃,目前该demo中使用的版本为3.3.2
dataSource.setProvider(dynamicDataSourceProvider);
return dataSource;
}
}
自定义主键策略,可以使用Redis,zk等主键策略,本次demo使用AtomicLong来作为测试
package com.example.ftserver.sharding;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;
import java.util.Properties;
import java.util.concurrent.atomic.AtomicLong;
@Slf4j
public class MyShardingKeyGenerator implements ShardingKeyGenerator {
private final AtomicLong atomicLong = new AtomicLong(0);
@Override
public Comparable<?> generateKey() {
log.info("generateKey 开始生成");
Long id = atomicLong.incrementAndGet();
log.info("generateKey 生成结束,生成结果:{}", id);
// 这里可以根据自己的业务需求生成不同的key
return id;
}
@Override
public String getType() {
// 这里需要和配置文件中属性 sharding.tables. key-generator.type值一致
return "MYKEY";
}
@Override
public Properties getProperties() {
return null;
}
@Override
public void setProperties(Properties properties) {
}
}
SPI接口配置
在Apache ShardingSphere中,很多功能实现类的加载方式是通过SPI注入的方式完成的。Service Provider Interface (SPI)是一种为了被第三方实现或扩展的API,它可以用于实现框架扩展或组件替换。
为了让用户通过实现Apache ShardingSphere提供的相应接口,动态将用户自定义的实现类加载其中,从而在保持Apache ShardingSphere架构完整性与功能稳定性的情况下,满足用户不同场景的实际需求。
如果使用自定义id策略,则需要进行spi接口配置,具体配置如下:
在resources目录下新建META-INF文件夹,再新建services文件夹,然后新建文件的名字为org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator的文件【META-INF/services/org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator】,文件内容为自定义主键生成策略的全限定类名【 com.example.ftserver.sharding.MyShardingKeyGenerator】
结构如图所示:
注意:
1.未配置自定义的id策略的时候,只做分库分表时,Mybatis Plus生成的实体类,@TableId 需要去掉type = IdType.AUTO。示例:
@TableId(value = "Id")
private Long id;
2.在配置了自定义的id策略的时候,使用自定义的id策略时,需要保留@TableId ,type = IdType.AUTO。示例:
@TableId(value = "Id",type = IdType.AUTO)
private Long id;
其他配置
由于个人demo在的Mapper,是继承了原来的BaseMapper上,并自定义了批量新增(相关内容地址:https://blog.youkuaiyun.com/TaylorSwiftRed/article/details/133983970),所以需要重写BaseMapper里所有的方法(否则会报空指针异常),并将@DS()注解配置在类上面,对于继承了RootMapper的类,如果有自定义查询,修改等方法,也需要在类或者方法上配置@DS()注解,否则会报空指针异常
package com.example.ftserver.plugin;
import com.baomidou.dynamic.datasource.annotation.DS;
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.example.ftserver.datapermission.DataEnum;
import com.example.ftserver.datapermission.DataScope;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;
/**
* @author aaa
* @description
*/
@DS("sharding") // 指定数据源,需要与MybatisPlusConfig类里面的dynamicDataSourceProvider()方法指定的数据源名称一致
public interface RootMapper<T> extends BaseMapper<T> {
/**
* 自定义批量新增,代替mybatis plus 自带的批量新增
*
* @param batchList 批量新增参数
* @return int
*/
int insertBatch(@Param("list") Collection<T> batchList);
/**
* 根据id批量修改
*
* @param batchList 批量更新参数
* @return int
*/
int updateBatch(@Param("list") Collection<T> batchList);
/**
* 根据 ID 查询
*
* @param id 主键ID
*/
@Override
@DataScope
T selectById(Serializable id);
/**
* 查询(根据ID 批量查询)
*
* @param idList 主键ID列表(不能为 null 以及 empty)
*/
@Override
@DataScope
List<T> selectBatchIds(@Param(Constants.COLL) Collection<? extends Serializable> idList);
/**
* 查询(根据 columnMap 条件)
*
* @param columnMap 表字段 map 对象
*/
@Override
@DataScope
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
/**
* 根据 Wrapper 条件,查询总记录数
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
@Override
@DataScope
Long selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 entity 条件,查询全部记录
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
@Override
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询全部记录
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
@Override
@DataScope
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询全部记录
* <p>注意: 只返回第一个字段的值</p>
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
@Override
@DataScope
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 entity 条件,查询全部记录(并翻页)
*
* @param page 分页查询条件(可以为 RowBounds.DEFAULT)
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
@Override
<P extends IPage<T>> P selectPage(P page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询全部记录(并翻页)
*
* @param page 分页查询条件
* @param queryWrapper 实体对象封装操作类
*/
@Override
@DataScope
<P extends IPage<Map<String, Object>>> P selectMapsPage(P page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 entity 条件,查询一条记录
* <p>查询一条记录,例如 qw.last("limit 1") 限制取一条记录, 注意:多条数据会报异常</p>
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
@Override
@DataScope
default T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper) {
return BaseMapper.super.selectOne(queryWrapper);
}
/**
* 根据 Wrapper 条件,判断是否存在记录
*
* @param queryWrapper 实体对象封装操作类
* @return 是否存在记录
*/
@Override
@DataScope
default boolean exists(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper) {
return BaseMapper.super.exists(queryWrapper);
}
@Override
int insert(T entity);
@Override
int deleteById(Serializable id);
@Override
int deleteById(T entity);
@Override
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
@Override
int delete(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
@Override
int deleteBatchIds(@Param(Constants.COLL) Collection<?> idList);
@Override
int updateById(@Param(Constants.ENTITY) T entity);
@Override
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
}
剩下的,就是按照普通的curd方法来处理即可。
解释一下广播表,如城市,省会,字典等数据,需要在每个数据库里面都要用,所以需要在每个库里面都要进行增删改等,只需要加入 broadcast-tables配置即可
项目配置: