©2020-2024 ioDraw All rights reserved
shardingSphere的精确分片和复杂分片的应用。
订单表实际表DDL如下：
CREATE TABLE `t_order_06` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id`
bigint(20) DEFAULT NULL, `order_id` bigint(20) DEFAULT NULL, `regdate`
timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB
AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
Maven依赖
<dependency> <groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId> <version>4.0.0-RC1</version>
</dependency>
自定义单一字段精确分区算法
public class MyPreciseShardingAlgorithm implements
PreciseShardingAlgorithm<java.util.Date> { @Override public String
doSharding(Collection<String> collection, PreciseShardingValue<Date>
preciseShardingValue) { String logicTableName =
preciseShardingValue.getLogicTableName(); Date date =
preciseShardingValue.getValue(); List<String> shardingSuffix = new
ArrayList<>(); //获取日期时间所在的月份 String str = DateFormatUtil.formatMonth(date);
//添加记录所在分表表名集合 shardingSuffix.add(logicTableName + "_" + str); return null; } }
自定义时间字段复杂分区算法
@Slf4j public class MyComplexShardingAlgorithm implements
ComplexKeysShardingAlgorithm { @Override public Collection<String>
doSharding(Collection collection, ComplexKeysShardingValue
complexKeysShardingValue) { log.info("自定义按照日期进行分表"); List<String>
shardingSuffix = new ArrayList<>(); //获取分表字段及字段值 Map<String, Collection<Date>>
map = complexKeysShardingValue.getColumnNameAndShardingValuesMap(); //获取字段值
Collection<Date> shardingValues = map.get("regdate"); if
(!CollectionUtils.isEmpty(shardingValues)) { for (Date date : shardingValues) {
//获取日期时间所在的月份 String str = DateFormatUtil.formatMonth(date); //添加记录所在分表表名集合
shardingSuffix.add(complexKeysShardingValue.getLogicTableName() + "_" + str); }
} return shardingSuffix; } }
单库分表代码
@Slf4j public class Demo { public static void main(String[] args) throws
SQLException { Map<String, DataSource> dataSourceMap = new HashMap<>();
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/spark?autoReconnect=true&useUnicode=true&characterEncoding"
+ "=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true");
dataSource1.setUsername("root"); dataSource1.setPassword("root");
dataSourceMap.put("database0", dataSource1); //数据库表分库分表规则
TableRuleConfiguration tableRuleConfiguration = new
TableRuleConfiguration("t_order"); //根据字段进行分库 //
tableRuleConfiguration.setDatabaseShardingStrategyConfig(new
InlineShardingStrategyConfiguration("user_id", // "database${user_id % 2}"));
//根据字段进行分表 // tableRuleConfiguration.setTableShardingStrategyConfig(new
InlineShardingStrategyConfiguration("order_id", // "t_order_${order_id % 2}"));
//自定义复杂分表设置 MyComplexShardingAlgorithm自定义分表算法
tableRuleConfiguration.setTableShardingStrategyConfig(new
ComplexShardingStrategyConfiguration("regdate", new
MyComplexShardingAlgorithm())); ShardingRuleConfiguration
shardingRuleConfiguration = new ShardingRuleConfiguration();
shardingRuleConfiguration.getTableRuleConfigs().add(tableRuleConfiguration);
DataSource dataSource =
ShardingDataSourceFactory.createDataSource(dataSourceMap,
shardingRuleConfiguration, new Properties()); String sql = "insert into t_order
(user_id,order_id,regdate) values (?, ?, ?)"; Date date = new
Date(System.currentTimeMillis()); Connection connection =
dataSource.getConnection(); PreparedStatement preparedStatement =
connection.prepareStatement(sql); preparedStatement.setInt(1, 3);
preparedStatement.setInt(2, 2); preparedStatement.setDate(3, date);
preparedStatement.execute(); } }