各依赖版本:
springboot 2.2.5.RELEASE
mybatis-plus-core 3.4.0
shardingsphere-jdbc-core-spring-boot-starter 5.2.0
pom依赖引入
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.0</version>
</dependency>
配置文件,在原有项目基础上集成,原有项目使用properties配置文件,此处sharding-jdbc也使用properties。新建配置文件,在原有的配置文件中,引用即可:(spring.profiles.include=shardingjdbc)
# 数据源参数配置
initialSize=5
minIdle=5
maxIdle=100
maxActive=20
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
# 库名
spring.shardingsphere.datasource.names=db0
# 配置db0
spring.shardingsphere.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driver-class-name=${spring.datasource.driver-class-name}
spring.shardingsphere.datasource.db0.jdbc-url=${spring.datasource.jdbc-url}
spring.shardingsphere.datasource.db0.username=${spring.datasource.username}
spring.shardingsphere.datasource.db0.password=${spring.datasource.password}
spring.shardingsphere.datasource.db0.initialSize=${initialSize}
spring.shardingsphere.datasource.db0.minIdle=${minIdle}
spring.shardingsphere.datasource.db0.maxActive=${maxActive}
spring.shardingsphere.datasource.db0.maxWait=${maxWait}
spring.shardingsphere.datasource.db0.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.db0.timeBetweenEvictionRunsMillis=${timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.db0.minEvictableIdleTimeMillis=${minEvictableIdleTimeMillis}
# 分表配置
# (表一表名)配置
spring.shardingsphere.rules.sharding.tables.表一表名.actual-data-nodes=db0.表一表名_$->{2019..2029}
spring.shardingsphere.rules.sharding.tables.表一表名.table-strategy.standard.sharding-column=CREATE_TIME
spring.shardingsphere.rules.sharding.tables.表一表名.table-strategy.standard.sharding-algorithm-name=分表算法规则配置名
# (表二表名)配置配置
spring.shardingsphere.rules.sharding.tables.表二表名.actual-data-nodes=db0.表二表名_$->{2019..2029}
spring.shardingsphere.rules.sharding.tables.表二表名.table-strategy.standard.sharding-column=RD_TIME
spring.shardingsphere.rules.sharding.tables.表二表名.table-strategy.standard.sharding-algorithm-name=znxd-data-share-sharding
# 分表算法属性配置-自定义算法规则
spring.shardingsphere.rules.sharding.sharding-algorithms.分表算法规则配置名.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.分表算法规则配置名.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.分表算法规则配置名.props.algorithmClassName=com.XXX.sharding.XXXShardingAlgorithm
# 打印分库分表日志
spring.shardingsphere.props.sql-show=true
自定义算法规则:
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import java.util.*;
import java.util.stream.Collectors;
/**
* @Description 自定义分片算法-按年分表
*/
@Slf4j
public class XXXShardingAlgorithm implements StandardShardingAlgorithm<Date> {
/**
* 分隔符
*/
private final static String CUT = "_";
/**
* 初始配置
*/
private Properties props = new Properties();
/**
* @Description 精确分片算法类名称,用于=和IN
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
Date value = preciseShardingValue.getValue();
if (collection == null || (collection.stream().findFirst().orElse(null)) == null || value == null) {
throw new IllegalArgumentException("sharding jdbc not find logic table,please check config");
}
String targetTableName = createTableName(preciseShardingValue.getLogicTableName(), String.valueOf(DateUtil.year(value)));
if (collection.contains(targetTableName)) {
return targetTableName;
} else {
throw new IllegalArgumentException("sharding jdbc not find logic table,No date specified,(single) " + value);
}
}
/**
* @Description 范围分片算法类名称,用于BETWEEN等
*/
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
String logicTable;
Range<Date> valueRange;
if (collection == null || (logicTable = collection.stream().findFirst().orElse(null)) == null
|| rangeShardingValue == null || (valueRange = rangeShardingValue.getValueRange()) == null) {
throw new IllegalArgumentException("sharding jdbc not find logic table,please check config");
}
List<String> list = new ArrayList<>();
// 开始时间
int beginYear = DateUtil.year(valueRange.lowerEndpoint());
// 结束时间
int endYear = DateUtil.year(valueRange.upperEndpoint());
List<Integer> yearList = collection.stream().map(table -> Integer.valueOf(table.substring(table.lastIndexOf("_") + 1))).collect(Collectors.toList());
// 数据库的最小年份
Integer minYear = NumberUtil.min(yearList.toArray(new Integer[]{}));
// 数据库的最大年份
Integer maxYear = NumberUtil.max(yearList.toArray(new Integer[]{}));
if (endYear < minYear || beginYear > maxYear) {
throw new IllegalArgumentException("sharding jdbc not find logic table,No date specified,(scope) " + beginYear + " to " + endYear);
}
// 查询区间在数据库区间内,返回查询区间
if (beginYear > minYear && endYear < maxYear) {
for (int year = beginYear; year <= endYear; year++) {
list.add(createTableName(logicTable, String.valueOf(year)));
}
}
// 查询区间在数据库反向区间内,返回数据库区间
if (beginYear < minYear && endYear > maxYear) {
return collection;
}
// 查询起始点小于库中起始点,返回库中起始点到查询结束点
if (beginYear < minYear) {
for (int year = minYear; year <= endYear; year++) {
list.add(createTableName(logicTable, String.valueOf(year)));
}
}
// 查询结束点大于库中结束点,返回查询起始点到库中结束点
if (endYear > maxYear) {
for (int year = beginYear; year <= maxYear; year++) {
list.add(createTableName(logicTable, String.valueOf(year)));
}
}
return list;
}
/**
* 根据源表名和路由生成目标表名
*
* @param logicTable 源表名
* @param route 路由,如年份规则2023
* @return 目标表名
*/
public static String createTableName(String logicTable, String route) {
return String.format("%s%s%s", logicTable, CUT, route);
}
@Override
public String getType() {
return "CLASS_BASED";
}
@Override
public Properties getProps() {
return this.props;
}
@Override
public void init(Properties properties) {
this.props = properties;
}
}
至此,一般项目基本都可以使用分表了。配置规则的表,会走自定义算法的规则,未配置分片规则的表,按单表逻辑进行。
但是,原有项目中用到动态数据源和封装过一层的mybatisplus。导致项目无法触发分表,无法找到sharding数据源,原始sql如count报错等问题。
以下问题只是罗列记录,不存在先后关系,也不是同一个问题的解决办法,不要重叠尝试:
一、如果在动态数据源的项目中,只使用sharding的数据源来做分表和非分表数据的操作,要注意把动态数据源配置中的默认数据源,指定为sharding-jdbc的数据源。
1、创建DynamicDataSource动态数据源时,在方法参数中直接引入,spring会自动注入:
@Qualifier("shardingSphereDataSource") DataSource dataSource
2、在默认数据源中配置成sharding数据源
DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
dynamicDataSource.setDefaultTargetDataSource(dataSource);
二、原有的oracle版本过低,集成sharing5.2.0时,启动项目报错,报错截图:
升级oracle驱动版本即可,多升级几个版本尝试一下。
项目用的oracle11g,原本使用的是
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.4.0-atlassian-hosted</version>
</dependency>
升级为:
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>18.3.0.0</version>
</dependency>
要注意,排除一下依赖下载有问题造成的影响,去maven的本地仓库里看一下依赖是否下载成功了,没有成功的话,手动去下载jar包,放到仓库的指定位置。同时,注意驱动类的写法,不同的驱动版本有不同的写法:
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
三、由于项目中mybatisplus和sharding集成存在一些问题,项目中大量使用了mybatisplus中的内部方法,如count等,导致解析SQL错误。
sharding源码中报错的位置:
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext setIndexForAggregationProjection方法 columnLabelIndexMap.containsKey(columnLabel)
改进过程:
1、配成多数据源,涉及到分表的表,交给sharding,其他不需要分表的表,还交给spring创建数据源管理,减少影响范围。注意,在配置多数据源时,sharding可能找不到mybatis内部的例如selectOne等方法,在创建SqlSessionFactory时,指定成MybatisSqlSessionFactoryBean即可。
/**
* 创建Mybatis的连接会话工厂实例
*/
@Bean(name = "shardingJdbcSqlSessionFactory")
public SqlSessionFactory shardingJdbcSqlSessionFactory(@Qualifier("shardingSphereDataSource") DataSource shardingJdbcDataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(shardingJdbcDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:shardingmapper/**/*.xml"));
Interceptor[] plugins = {mybatisPlusInterceptor};
sqlSessionFactoryBean.setPlugins(plugins);
return sqlSessionFactoryBean.getObject();
}
2、重写count方法,弃用mybatisplus内部的方法,重写方法时count(1),括号里不带空格即可。