原理和开源库
原理: 通过解析sql语句, 根据分库分表的规则,替换掉表名和库名
文档资料
https://github.com/apache/incubator-shardingsphere/
https://shardingsphere.apache.org/
对应的SQL解析器为SQLParsingEngine
, 有兴趣可以细看一下
完整示例
https://github.com/wzjwhut/spring-example-all/tree/master/sharding-jdbc
使用方法
准备工具
安装mysql, 准备好需要的数据库和表
https://github.com/wzjwhut/spring-jdbc-sharding/blob/master/src/main/resources/config.sql
maven依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>3.1.0</version>
</dependency>
配置DataSource
使用jdbc-sharding的data source将spring的data source包裹起来
@Configuration
@Log4j2
public class DataSourceConfig {
/** 使用sharding-jdbc的源 */
@Bean
DataSource createDataSource() throws SQLException, NoSuchFieldException, IllegalAccessException {
log.info("build data source");
//收集分库列表
Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
dataSourceMap.put("test", createDataSource("test"));
dataSourceMap.put("test_0", createDataSource("test_0"));
dataSourceMap.put("test_1", createDataSource("test_1"));
//city表的分库分表规则
TableRuleConfiguration cityRule = new TableRuleConfiguration();
cityRule.setLogicTable("city");
cityRule.setActualDataNodes("test_${0..1}.city_${0..1}");
//分库策略
cityRule.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(
"name",
new ModuleDatabaseShardingAlgorithm()));
//分表策略
cityRule.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(
"name",
new ModuleTableShardingAlgorithm(),null));
ShardingRuleConfiguration ruleConfig = new ShardingRuleConfiguration();
ruleConfig.setDefaultDataSourceName("test");
ruleConfig.getTableRuleConfigs().addAll(Arrays.asList(cityRule));
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap,
ruleConfig, Collections.EMPTY_MAP, null);
return dataSource;
}
private static DataSource createDataSource(final String dataSourceName) {
log.info("create data source: {}", dataSourceName);
HikariDataSource result = new HikariDataSource();
result.setJdbcUrl(String.format("jdbc:mysql://localhost:3306/%s", dataSourceName));
result.setDriverClassName(Driver.class.getName());
result.setUsername("root");
result.setPassword("123456");
return result;
}
}