jdbc sharding yml配置_ShardingSphere + MyBatis + Spring Boot单数据源分表配置

我的项目中依赖的DB很多，但是并不都需要通过ShardingSphere访问，所以需要额外配置一个通过ShardingSphere访问的数据源。我当初的第一选择是引入sharding-jdbc-spring-boot-starter，但是目前sharding-jdbc-spring-boot-starter在没有按照starter要求进行配置的情况下会报错。因此放弃了，改为单独引用sharding-jdbc。

POM

io.shardingsphere

sharding-jdbc

3.1.0

YAML配置

这是最坑的一部分，官网上的Yaml配置配上了根本不生效，于是根据源码的类自己写了yml文件。

两张表：stuff、company

分表规则：userid % 16sharding-sphere:

datasources:

test:

jdbc-url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=GBK&zeroDateTimeBehavior=convertToNull&noAccessToProcedureBodies=true

username: test

password: test

max-pool-size: 5

pool-name: ironman-hikari-pool

min-idle: 1

connection-timeout: 15000

idle-timeout: 300000

sharding-rules:

tableRule-configs:

-

logicTable: stuff

actualDataNodes: "test.stuff$->{0..15}"

tableShardingStrategyConfig:

shardingColumn: userid

algorithmExpression: stuff$->{userid % 16}

-

logicTable: company

actualDataNodes: "test.company$->{0..15}"

tableStrategy:

shardingColumn: userid

algorithmExpression: company{userid % 16}

binding-tables:

- stuff, company

default-dataSource-name: "test"

Java中的配置@Configuration

@MapperScan(basePackages = "com.garin.testapp.testapp-dal.mapper",

sqlSessionFactoryRef = "shardingMybatisSqlSessionFactory")

public class ShardingDataSourceConfig {

@Bean(name = "shardingRuleConf")

@ConfigurationProperties(prefix = "sharding-sphere.sharding-rules")

public ShardingRuleConfiguration shardingRuleConf() {

return new ShardingRuleConfiguration();

}

@Bean(name = "mysqlDatasource")

@ConfigurationProperties(prefix = "sharding-sphere.datasources.test")

public DataSource mysqlDatasource() {

return DataSourceBuilder.create().type(HikariDataSource.class).build();

}

@Bean(name = "shardingDatasource")

public DataSource shardingDatasource(@Qualifier("shardingRuleConf") ShardingRuleConfiguration shardingRuleConf,

@Qualifier("mysqlDatasource") DataSource mysqlDatasource)

throws SQLException {

Map dataSourceMap = new HashMap<>();

dataSourceMap.put("test", mysqlDatasource);

return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConf, new HashMap<>(), new Properties());

}

@Bean("shardingMybatisSqlSessionFactory")

public SqlSessionFactory shardingMybatisSqlSessionFactory(@Qualifier("shardingDatasource") DataSource dataSource)

throws Exception {

SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();

sessionFactoryBean.setDataSource(dataSource);

String patternPath = ResourcePatternResolver.CLASSPATH_URL_PREFIX + "sharding/*Mapper.xml";

sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()

.getResources(patternPath));

sessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);

return sessionFactoryBean.getObject();

}

}

其他问题

引入后遇到了一些报错，sharding-sphere会对分表的字段在项目启动时做强校验，校验每个分表的字段、字段的顺序做强校验。校验失败会导致项目启动失败。