©2020-2024 ioDraw All rights reserved
shardingSphere The application of precise slicing and complex slicing .
Order form actual table DDL as follows ：
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 rely on
<dependency> <groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId> <version>4.0.0-RC1</version>
</dependency>
User defined precise partition algorithm for single field
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<>(); // Gets the month of the date time String str = DateFormatUtil.formatMonth(date);
// Table name collection of added record shardingSuffix.add(logicTableName + "_" + str); return null; } }
Complex partition algorithm of custom time field
@Slf4j public class MyComplexShardingAlgorithm implements
ComplexKeysShardingAlgorithm { @Override public Collection<String>
doSharding(Collection collection, ComplexKeysShardingValue
complexKeysShardingValue) { log.info(" User defined sub table by date "); List<String>
shardingSuffix = new ArrayList<>(); // Get sub table fields and field values Map<String, Collection<Date>>
map = complexKeysShardingValue.getColumnNameAndShardingValuesMap(); // Get field value
Collection<Date> shardingValues = map.get("regdate"); if
(!CollectionUtils.isEmpty(shardingValues)) { for (Date date : shardingValues) {
// Gets the month of the date time String str = DateFormatUtil.formatMonth(date); // Table name collection of added record
shardingSuffix.add(complexKeysShardingValue.getLogicTableName() + "_" + str); }
} return shardingSuffix; } }
Code of single database and sub table
@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); // Rules of database table sub database and sub table
TableRuleConfiguration tableRuleConfiguration = new
TableRuleConfiguration("t_order"); // The database is divided according to the field //
tableRuleConfiguration.setDatabaseShardingStrategyConfig(new
InlineShardingStrategyConfiguration("user_id", // "database${user_id % 2}"));
// Divide the table according to the field // tableRuleConfiguration.setTableShardingStrategyConfig(new
InlineShardingStrategyConfiguration("order_id", // "t_order_${order_id % 2}"));
// Custom complex sub table settings MyComplexShardingAlgorithm User defined sub table algorithm
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(); } }
Technology