Spring boot + Mybatis plus + sharding jdbc 4.1.1整合的基本分片Demo使用及踩坑记录
该文章仅介绍基础的分表,记录过程中整合的关键点及踩坑点。
1.maven引入及相关踩坑
pom文件中部分关键引用展示:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.4</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-cloud-kubernetes.version>2.0.3</spring-cloud-kubernetes.version>
<mybatis-plus.version>3.4.3</mybatis-plus.version>
<druid.version>1.1.21</druid.version>
<fastdfs.client>1.27.0.0</fastdfs.client>
<fastjson.version>1.2.47</fastjson.version>
<logstash-logback-encoder.version>5.2</logstash-logback-encoder.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>javax.validation</groupId>
<artifactId>validation-api</artifactId>
<version>2.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
<exclusions>
<exclusion>
<groupId>com.sun</groupId>
<artifactId>tools</artifactId>
</exclusion>
<exclusion>
<groupId>com.sun</groupId>
<artifactId>jconsole</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
踩坑点:Druid数据库连接池,使用以下maven引入时,会报错。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
目前具体原因暂不清楚,事实上,使用以上方式进行依赖时,项目也没有报任何编译时的错误,也没有出现maven依赖冲突的问题,但就是无法正常启动项目,会抛出以下异常。
Caused by: java.lang.IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required
at org.springframework.util.Assert.notNull(Assert.java:201) ~[spring-core-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.mybatis.spring.support.SqlSessionDaoSupport.checkDaoConfig(SqlSessionDaoSupport.java:122) ~[mybatis-spring-2.0.4.jar:2.0.4]
at org.mybatis.spring.mapper.MapperFactoryBean.checkDaoConfig(MapperFactoryBean.java:73) ~[mybatis-spring-2.0.4.jar:2.0.4]
at org.springframework.dao.support.DaoSupport.afterPropertiesSet(DaoSupport.java:44) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1853) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1790) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
... 43 common frames omitted
该处折磨了很久终于解决,还是在github上出现相同问题的帖子中,底下评论中小小的一段回复中看到后才解决的,因此遇到同样问题的老铁们可以参考以上做法进行maven导入的修改,如果有知道原因的,能告知,也万分感谢。
2.配置文件内容即相关分片策略类
spring:
main:
allow-bean-definition-overriding: true
jackson:
time-zone: GMT+8
date-format: yyyy-MM-dd HH:mm:ss
shardingsphere:
datasource: ##配置datasource数据源,Mybatis plus会自动注入该datasource
names: ds0 ##数据库别名,如要进行分库,此处可添加多个,以下也根据实际情况添加多个
ds0:
type: com.alibaba.druid.pool.DruidDataSource #数据库连接池
driver-class-name: com.mysql.cj.jdbc.Driver #数据库驱动
url: jdbc:mysql://数据库地址:3306/数据库名?allowMultiQueries=true&useUnicode=truecharacterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
username: 数据库账号名
password: 数据库账号密码
####数据库连接池的其他配置
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/*
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
sharding:
tables: ##配置表策略,同样,如果有多张表,下面就进行多张表的配置,查询时,如果不是以下表,就没有分表操作。
unit_15min: #表名(逻辑表名),即此处的表明对应的是实体类Entity中的@TableName中的名称
actual-data-nodes: ds0.unit_15min_202109,ds0.unit_15min_202110 ##实际节点,必须是数据库中已存在的表,如需要更佳的动态建表找表方案,需要再查阅其他资料,目前我暂无发现更好的方式,此处仅简单的列了两张表,实际上可使用范围来列表,具体配置可参考官方文档
table-strategy: ##表策略配置
standard: ##本Demo采用标准分片策略
sharding-column: stat_time ##数据库表中用于标记表分片的列,如该列为2021-09-21 00:00:00,那么就找到表unit_15min_202109
range-algorithm-class-name: com.hgny.energydata.module.data.sharding.StatTimeRangeShardingAlgorithm ##配置分片算法策略的类,当查询Sql中以sharding-column中设置的stat_time做=,即in条件查询时,会跳入该分片策略,类中具体内容会在下面列出。
precise-algorithm-class-name: com.hgny.energydata.module.data.sharding.StatTimePreciseAlgorithm ##配置分片算法策略的类,当查询Sql中以sharding-column中设置的stat_time做between条件查询时,会跳入该分片策略,根据官方文档,该项在标准分片策略中时必配项类中具体内容会在下面列出。
props:
sql.show: true #是否打印sql
StatTimeRangeShardingAlgorithm:
public class StatTimeRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> tableNames, RangeShardingValue<String> rangeShardingValue) {
Set<String> result = new LinkedHashSet<>();
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
// between and 的起始值
LocalDateTime lower = LocalDateTime.parse(rangeShardingValue.getValueRange().lowerEndpoint(),formatter);
LocalDateTime upper = LocalDateTime.parse(rangeShardingValue.getValueRange().upperEndpoint(),formatter);
//获取时间列表
//补充说明:其实tableNames中的值就是配置文件中的actual-data-nodes中配置的所有表,此处返回的是要进行查询的表
List<String> timeList = packDateStrListByInterval(lower.toLocalDate(),upper.toLocalDate());
// 循环范围计算分表逻辑
for(String time : timeList){
for(String tableName : tableNames){
if(tableName.endsWith(time)){
result.add(tableName);
}
}
}
return result;
}
//与Demo的业务相关,不需要具体看清该业务逻辑,只要知道doSharding方法时用于筛选除需要进行查询的所有表即可
private List<String> packDateStrListByInterval(LocalDate startDate, LocalDate endDate) {
List<String> list = Lists.newArrayList();
if (endDate.isBefore(startDate)) {
return list;
}
LocalDate tmp = startDate;
list.add(tmp.format(DateTimeFormatter.ofPattern("yyyyMM")));
while (true) {
tmp = tmp.plusMonths(1);
if (endDate.isBefore(tmp)) {
break;
}
list.add(tmp.format(DateTimeFormatter.ofPattern("yyyyMM")));
}
return list;
}
}
StatTimePreciseAlgorithm:
public class StatTimePreciseAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime selectDate = LocalDateTime.parse(preciseShardingValue.getValue(),formatter);
String yyyyMM = selectDate.format(DateTimeFormatter.ofPattern("yyyyMM"));
//补充说明:其实tableNames中的值就是配置文件中的actual-data-nodes中配置的所有表,此处返回的是要进行查询的表
for (String tableName : collection){
if(tableName.endsWith(yyyyMM)){
return tableName;
}
}
throw new IllegalArgumentException();
}
}
总的来说,对上述所有配置来说,最终实现的效果就是,当Mybatis plus执行以下SQL的时候,
select * from unit_15min where stat_time between '2021-07-18 11:45:00' and '2021-09-21 11:45:00';
会跳入到StatTimeRangeShardingAlgorithm类中,根据实现的doSharding方法,将需要进行查询的所有表返回,所以此处实际执行的SQL如下:
select * from unit_15min_202107 where stat_time between '2021-07-18 11:45:00' and '2021-09-21 11:45:00';
select * from unit_15min_202108 where stat_time between '2021-07-18 11:45:00' and '2021-09-21 11:45:00';
select * from unit_15min_202109 where stat_time between '2021-07-18 11:45:00' and '2021-09-21 11:45:00';
当Mybatis plus执行以下SQL时:
select * from unit_15min where stat_time ='2021-07-18 11:45:00';
会跳入到StatTimePreciseAlgorithm类中,根据实现的doSharding方法,计算除实际需要查询的表,执行以下的实际SQL:
select * from unit_15min_202107 where stat_time ='2021-07-18 11:45:00';
本文仅介绍简单的数据库表分表查询,sharding jdbc总的使用上来说并不复杂,主要记录下踩坑点,本文的配置介绍较为简陋,详情可参考官方文档:
https://shardingsphere.apache.org/document/4.1.1/en/overview/
官方spring boot 的数据分片配置文件解释:
spring.shardingsphere.datasource.names= #数据源名称,多数据源以逗号分隔
spring.shardingsphere.datasource.<data-source-name>.type= #数据库连接池类名称
spring.shardingsphere.datasource.<data-source-name>.driver-class-name= #数据库驱动类名
spring.shardingsphere.datasource.<data-source-name>.url= #数据库url连接
spring.shardingsphere.datasource.<data-source-name>.username= #数据库用户名
spring.shardingsphere.datasource.<data-source-name>.password= #数据库密码
spring.shardingsphere.datasource.<data-source-name>.xxx= #数据库连接池的其它属性
spring.shardingsphere.sharding.tables.<logic-table-name>.actual-data-nodes= #由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点,用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况
#分库策略,缺省表示使用默认分库策略,以下的分片策略只能选其一
#用于单分片键的标准分片场景
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.sharding-column= #分片列名称
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.precise-algorithm-class-name= #精确分片算法类名称,用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.range-algorithm-class-name= #范围分片算法类名称,用于BETWEEN,可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
#用于多分片键的复合分片场景
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.complex.sharding-columns= #分片列名称,多个列以逗号分隔
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.complex.algorithm-class-name= #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
#行表达式分片策略
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.inline.sharding-column= #分片列名称
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.inline.algorithm-expression= #分片算法行表达式,需符合groovy语法
#Hint分片策略
spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.hint.algorithm-class-name= #Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
#分表策略,同分库策略
spring.shardingsphere.sharding.tables.<logic-table-name>.table-strategy.xxx= #省略
spring.shardingsphere.sharding.tables.<logic-table-name>.key-generator.column= #自增列名称,缺省表示不使用自增主键生成器
spring.shardingsphere.sharding.tables.<logic-table-name>.key-generator.type= #自增列值生成器类型,缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型:SNOWFLAKE/UUID
spring.shardingsphere.sharding.tables.<logic-table-name>.key-generator.props.<property-name>= #属性配置, 注意:使用SNOWFLAKE算法,需要配置worker.id与max.tolerate.time.difference.milliseconds属性。若使用此算法生成值作分片值,建议配置max.vibration.offset属性
spring.shardingsphere.sharding.binding-tables[0]= #绑定表规则列表
spring.shardingsphere.sharding.binding-tables[1]= #绑定表规则列表
spring.shardingsphere.sharding.binding-tables[x]= #绑定表规则列表
spring.shardingsphere.sharding.broadcast-tables[0]= #广播表规则列表
spring.shardingsphere.sharding.broadcast-tables[1]= #广播表规则列表
spring.shardingsphere.sharding.broadcast-tables[x]= #广播表规则列表
spring.shardingsphere.sharding.default-data-source-name= #未配置分片规则的表将通过默认数据源定位
spring.shardingsphere.sharding.default-database-strategy.xxx= #默认数据库分片策略,同分库策略
spring.shardingsphere.sharding.default-table-strategy.xxx= #默认表分片策略,同分表策略
spring.shardingsphere.sharding.default-key-generator.type= #默认自增列值生成器类型,缺省将使用org.apache.shardingsphere.core.keygen.generator.impl.SnowflakeKeyGenerator。可使用用户自定义的列值生成器或选择内置类型:SNOWFLAKE/UUID
spring.shardingsphere.sharding.default-key-generator.props.<property-name>= #自增列值生成器属性配置, 比如SNOWFLAKE算法的worker.id与max.tolerate.time.difference.milliseconds
spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.master-data-source-name= #详见读写分离部分
spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[0]= #详见读写分离部分
spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[1]= #详见读写分离部分
spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[x]= #详见读写分离部分
spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-class-name= #详见读写分离部分
spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-type= #详见读写分离部分
spring.shardingsphere.props.sql.show= #是否开启SQL显示,默认值: false
spring.shardingsphere.props.executor.size= #工作线程数量,默认值: CPU核数