ShardingSphere JDBC,定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库,以 jar 包形式提供服务,无需额外部署和依赖,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。
适用于任何基于 JDBC 的 ORM 框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC;
支持任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, HikariCP 等;
支持任意实现 JDBC 规范的数据库,目前支持 MySQL,PostgreSQL,Oracle,SQLServer 以及任何可使用 JDBC 访问的数据库。
ShardingSphere JDBC经常被用于在java项目中实现对数据库分库,分表,读写分离等业务操作。
早前关注的版本,在对不友好的表设计,复杂sql语句例如嵌套子查询,分组查询,聚合函数等支撑上面限制很多,虽然java开发者都看到过阿里规范上要求sql查询最好不要超过(包括等于)三张表,但不理解原因,一是合理的采用大表/宽表可以减少join的次数,并且有利于索引创建(类似ES,hive使用),同时多表查询不利于sql语法分析器与优化器做处理,同时不利于mycat这类代理分库分表查询 。
目前最新版是5.1.1,减少了不少限制,子查询,多表查询也有了解决方案,考虑试一下效果。偶然进去官网看了一圈,发现无论官方github,还是各大网站也没有关于5.1.x的可运行案例demo。 自已翻了下源码,动手写一个供大家参考,
我这里采用springboot2.x+druid1.2.9+mybatis plus3.4.2+mysql (驱动为mysql-connector-java 8.0.21)。
后面我也使用oracle11g,去测试过,可能会有驱动不兼容问题,查看关于T4CConnection.getSchema()报错解决
<!-- 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.9</version>
</dependency>
网上能找到的都是以往版本的例子,简单对比一下,发现5.x.x的yaml配置貌似跟以往版本变化较大,不兼容4.x版本配置,以官网为准配置以下,我这里想要实现sys_user表用id主键取模读取两张表(sys_user_0,sys_user_1)的数据
spring:
datasource:
druid:
max-active: 20
initial-size: 1
max-wait: 1000
min-idle: 3
remove-abandoned: true
remove-abandoned-timeout: 180
connection-properties:
clientEncoding: UTF-8
test-while-idle: true
shardingsphere:
mode:
type: Memory
datasource:
names: master #支持从哪些库读,分多个库的话,用逗号分隔。 如ds1,ds2,ds3
master: #这里连接的名称,自定义,我这里为master,分库则配置多个连接
url: jdbc:mysql://192.168.1.55:3306/demoDb?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useAffectedRows=true&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: demo
password: 12345678
type: com.alibaba.druid.pool.DruidDataSource
rules:
sharding:
tables:
sys_user: #要分表的名称
actual-data-nodes: master.sys_user_$->{0..1} #表示 从连接名为master的库中的sys_user_0到sys_user_1号表查询
table-strategy:
standard:
sharding-column: id #用id 这列来取模
sharding-algorithm-name: idModShardingAlgorithm #取模的算法名称
sharding-algorithms:
idModShardingAlgorithm:
props:
sharding-count: 2#用于mod取模
type: MOD
props:
sql:
show: true
Dao层 直接继承mybatisplus的IService,使用它提供的一些公共方法,如getById,save
public interface SysUserDataService extends IService<SysUserEntity> {
}
然后是service层
@ApiDoc(value = "测试查询接口", description = "通过id查用户", responseClassDescription = "返回用户DTO")
@Override
public SysUserDto getUserById(String userId) {
SysUserEntity entity = sysUserDataService.getById(userId);
SysUserDto result = BeanHelper.copyProperties(entity, SysUserDto::new);
return result;
}
最后,我在sys_user_0表和sys_user_1表分别存入几条测试用的ID为偶数和ID为奇数的数据在表中,这样我要实现当传入ID为奇数去sys_user_1表取,偶数去sys_user_0表取。
启动项目,进行测试,原以为sharding jdbc实现了shardingsphere-jdbc-core-spring-boot-starter,会自动装配完成bean对象就可以用了,结果先是报错,mybatis plus在启动过程中,依赖datasource未取到报错,看来需要自己创建了。
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration;
import org.apache.shardingsphere.infra.yaml.config.swapper.mode.ModeConfigurationYamlSwapper;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.spring.boot.datasource.DataSourceMapSetter;
import org.apache.shardingsphere.spring.boot.prop.SpringBootPropertiesConfiguration;
import org.apache.shardingsphere.spring.boot.schema.SchemaNameSetter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;
@Configuration
@ComponentScan({"org.apache.shardingsphere.spring.boot.converter"})
@EnableConfigurationProperties({SpringBootPropertiesConfiguration.class})
@ConditionalOnProperty(
prefix = "spring.shardingsphere",
name = {"enabled"},
havingValue = "true",
matchIfMissing = true
)
public class ShardingConfig implements EnvironmentAware {
private String schemaName;
private final SpringBootPropertiesConfiguration props;
private final Map<String, DataSource> dataSourceMap = new LinkedHashMap();
@Bean
public ModeConfiguration modeConfiguration() {
return null == this.props.getMode() ? null : (new ModeConfigurationYamlSwapper()).swapToObject(this.props.getMode());
}
@Override
public final void setEnvironment(Environment environment) {
this.dataSourceMap.putAll(DataSourceMapSetter.getDataSourceMap(environment));
this.schemaName = SchemaNameSetter.getSchemaName(environment);
}
public ShardingConfig(SpringBootPropertiesConfiguration props) {
this.props = props;
}
@Primary
@Bean
@ConditionalOnMissingBean
public DataSource dataSource() throws SQLException {
//这里调用ShardingSphereDataSourceFactory.createDataSource方法去创建最终datasource对象。
return !this.dataSourceMap.isEmpty() ? ShardingSphereDataSourceFactory.createDataSource(this.schemaName, modeConfiguration(), this.dataSourceMap, Collections.emptyList(), this.props.getProps()) : ShardingSphereDataSourceFactory.createDataSource(this.schemaName, modeConfiguration());
}
}
这里采用实现EnvironmentAware接口来将Envieronment对象回调到本配置类中,从yaml中读取配置创建连接,然后利用shardingjdbc的工具类创建一个datasource bean对象给orm框架(这里是mybatis plus)使用.
启动,不报错,可以运行,但是分表无效。貌似是分表规则并与算法配置的有问题,看来yaml中配置没生效,手动硬编码配置一下算了。改造一下上面的ShardingConfig ,
//这里将yml中配置的规则注入进来
@Primary
@Bean
public DataSource dataSource(RuleConfiguration shardingRuleConfiguration) throws SQLException {
//这里调用ShardingSphereDataSourceFactory.createDataSource方法去创建最终datasource对象。
return !this.dataSourceMap.isEmpty() ? ShardingSphereDataSourceFactory.createDataSource(this.schemaName, modeConfiguration(), this.dataSourceMap, Arrays.asList(shardingRuleConfiguration), this.props.getProps()) : ShardingSphereDataSourceFactory.createDataSource(this.schemaName, modeConfiguration());
}
再测试,搞定,分表功能已实现。
curl -X GET "http://localhost:8010/core/sys_user/getUserById?id=1513710097774526466" -H "accept: */*"
Response
{
"id": "1513710097774526466",
"userName": "string",
"password": "string",
"userState": 1,
"createTime": null,
"userDesc": "string",
"userImg": "string",
"deptId": "string",
"email": "string",
"mobile": "string",
"accountPlatform": 0,
"accountType": 0,
"emailVerified": false,
"mobileVerified": false
}
*这里我省略了中间无数次失败与排查过程。可以稍微提一下下为什么ShardingSphereAlgorithmConfiguration传入MOD的意义。
Shardingjdbc大量使用了SPI,类似IOC的思想,把一个类实例的创建从硬编码反转到文件配置上, 原理是在jar包的META-INF/services/目录里同时创建一个以服务接口命名的文件,在文件中列表该接口的实现,在调用java.util.ServiceLoader.load(serviceInterface)方法后通过反射实例化所有对象。
比如加载默认分片算法利用了JAVA SPI模式,,通org.apache.shardingsphere.sharding.spi.ShardingAlgorithm实例了多种已默认算法,包括我要用到的取模算法
下图为文件里边内置的算法
这里每个算法都实现了TypedSPI接口,它的作用是后续可以通过定义的算法Type去调用相应的算法。
而ModShardingAlgorithm的类型便为MOD,相应的还有INLINE,INTERVAL等等。
上述配置中采用了shardingjdbc在SPI中已定义好的ModShardingAlgorithm算法,满足我们对ID字段取模分片,对于sharding sphere在5.x.x自定义分片一直没头绪。
实现一个自定义分片算法
经过一段源码研究,可能是设计者为了插件化考虑,在上面SPI提供的内置分片算法规则中,提供了一个ClassBasedShardingAlgorithm这个算法,其内部根据设置property,将自定义的算法通过反射创建出来。
下面是ClassBasedShardingAlgorithm算法部分源码
public final class ClassBasedShardingAlgorithm implements StandardShardingAlgorithm<Comparable<?>>, ComplexKeysShardingAlgorithm<Comparable<?>>, HintShardingAlgorithm<Comparable<?>> {
private static final String STRATEGY_KEY = "strategy";
private static final String ALGORITHM_CLASS_NAME_KEY = "algorithmClassName";
...
@Getter
@Setter
private Properties props = new Properties();
@Override
public void init() {
String strategyKey = props.getProperty(STRATEGY_KEY);
Preconditions.checkNotNull(strategyKey, "The props `%s` cannot be null when uses class based sharding strategy.", STRATEGY_KEY);
strategy = ClassBasedShardingAlgorithmStrategyType.valueOf(strategyKey.toUpperCase().trim());
algorithmClassName = props.getProperty(ALGORITHM_CLASS_NAME_KEY);#读取配置中的props ,取得自定义算法类
Preconditions.checkNotNull(algorithmClassName, "The props `%s` cannot be null when uses class based sharding strategy.", ALGORITHM_CLASS_NAME_KEY);
createAlgorithmInstance();
}
private void createAlgorithmInstance() {
switch (strategy) {
case STANDARD:
standardShardingAlgorithm = ClassBasedShardingAlgorithmFactory.newInstance(algorithmClassName, StandardShardingAlgorithm.class, props);#就在这里反射创建实例
break;
case COMPLEX:
complexKeysShardingAlgorithm = ClassBasedShardingAlgorithmFactory.newInstance(algorithmClassName, ComplexKeysShardingAlgorithm.class, props);
break;
case HINT:
hintShardingAlgorithm = ClassBasedShardingAlgorithmFactory.newInstance(algorithmClassName, HintShardingAlgorithm.class, props);
break;
default:
break;
}
}
@Override
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue) {
return standardShardingAlgorithm.doSharding(availableTargetNames, shardingValue); #实际上用反射出来的实例去计算
}
因此改一下配置
shardingsphere:
mode:
type: Memory
datasource:
names: master
master:
url: jdbc:mysql://192.168.1.55:3306/demoDb?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useAffectedRows=true&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: demo
password: 12345678
type: com.alibaba.druid.pool.DruidDataSource
rules:
sharding:
binding-tables:
- "sys_user"
tables:
sys_user:
actual-data-nodes: master.sys_user_$->{0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: myShardingAlgorithm #引用算法名称
sharding-algorithms:
idModShardingAlgorithm:
props:
sharding-count: 2
type: MOD
myShardingAlgorithm: #新加的算法名称
props:
strategy: standard
# 自定义标准分配算法
algorithmClassName: com.kuizii.demo.core.config.IdModShardingAlgorithm
type: CLASS_BASED
自定义一个算法
public class IdModShardingAlgorithm implements StandardShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
for (String s : collection) {
Integer mod= Integer.parseInt( preciseShardingValue.getValue())%2;
if (s.endsWith( mod.toString())) {
return s;
}
continue;
}
return "sys_user";
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
return null;
}
@Override
public void init() {
}
@Override
public String getType() {
return "MOD1";
}
}
经过改造,再运行,自定义的分片算法完成了!
相关源码查看我的gitee项目