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依賴
org.apache.shardingsphere
sharding-jdbc-core
4.0.0-RC1
自定義單一字段精确分區算法
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm{
@Override
public String doSharding(Collectioncollection, PreciseShardingValuepreciseShardingValue) {
String logicTableName = preciseShardingValue.getLogicTableName();
Date date = preciseShardingValue.getValue();
ListshardingSuffix = new ArrayList<>();
//擷取日期時間所在的月份
String str = DateFormatUtil.formatMonth(date);
//添加記錄所在分表表名集合
shardingSuffix.add(logicTableName + "_" + str);
return null;
}
}
自定義時間字段複雜分區算法
@Slf4j
public class MyComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {
@Override
public CollectiondoSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
log.info("自定義按照日期進行分表");
ListshardingSuffix = new ArrayList<>();
//擷取分表字段及字段值
Map> map = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
//擷取字段值
CollectionshardingValues = 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 {
MapdataSourceMap = 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();
}
}