SpringCloud H.SR7 + sharding

SpringCloud H.SR7 + sharding"/>

SpringCloud H.SR7 + sharding

本文是基于上一讲SpringCloud H.SR7 + sharding-jdbc 4.1.1-水平分库分表
接下来我们继续介绍分库分表和数据广播
先上一个yml配置,对了maven的配置上上一篇是一样这里就不介绍了

server:port: 8080
datasource0:url: jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8driver-class-name: com.mysql.cj.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSourceusername: rootpassword: root
datasource1:url: jdbc:mysql://localhost:3306/edu_db_1?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8driver-class-name: com.mysql.cj.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSourceusername: rootpassword: root
datasource2:url: jdbc:mysql://localhost:3306/edu_db_2?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8driver-class-name: com.mysql.cj.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSourceusername: rootpassword: root

然后开始写配置，首先是雪花算法配置类:

@Configuration
public class KeyIdConfig {@Bean("userKeyGenerator")public SnowflakeShardingKeyGenerator userKeyGenerator() {return new SnowflakeShardingKeyGenerator();}@Bean("orderKeyGenerator")public SnowflakeShardingKeyGenerator orderKeyGenerator() {return new SnowflakeShardingKeyGenerator();}
}

然后是数据源配置:

@Configuration
public class DBConf {@Value("${datasource0.url}")private String url0;@Value("${datasource0.username}")private String username0;@Value("${datasource0.password}")private String password0;@Value("${datasource0.driver-class-name}")private String driverClassName0;@Value("${datasource1.url}")private String url1;@Value("${datasource1.username}")private String username1;@Value("${datasource1.password}")private String password1;@Value("${datasource1.driver-class-name}")private String driverClassName1;@Value("${datasource2.url}")private String url2;@Value("${datasource2.username}")private String username2;@Value("${datasource2.password}")private String password2;@Value("${datasource2.driver-class-name}")private String driverClassName2;@Bean("dataSource")public DataSource dataSource() {try {// Sharding全局配置ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();//配置t_usershardingRuleConfiguration.getTableRuleConfigs().add(confTUserRule());//配置Course的水平分布shardingRuleConfiguration.getTableRuleConfigs().add(confCourseRule());//配置公共字典表shardingRuleConfiguration.getTableRuleConfigs().add(confTudictRule());//配置teacher表shardingRuleConfiguration.getTableRuleConfigs().add(confTeacherRule());//如果添加数据则三个库都会添加,如果删除则三个库都会删除shardingRuleConfiguration.getBroadcastTables().add("t_udict");// 自定义算法//shardingRuleConfiguration.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("t_id", "m1"));//shardingRuleConfiguration.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("t_id", new TableShardingAlgorithm()));// 创建数据源Properties props=new Properties();props.put("sql.show", "true");DataSource dataSource = ShardingDataSourceFactory.createDataSource(confDataSourceMap(), shardingRuleConfiguration, props);return dataSource;} catch (Exception ex) {ex.printStackTrace();return null;}}private TableRuleConfiguration confTeacherRule() {// 配置表规则TableRuleConfiguration teacherConf = new TableRuleConfiguration("teacher", "m$->{1..2}.teacher_$->{1..2}");// 行表达式分表规则teacherConf.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("t_id", new TableShardingAlgorithm()));// 行表达式分库规则teacherConf.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("t_key", new DatabaseShardingAlgorithm()));return teacherConf;}/*** 水平分库分表* @return*/private TableRuleConfiguration confCourseRule() {// 配置表规则TableRuleConfiguration courseConfig = new TableRuleConfiguration("course", "m$->{1..2}.course_$->{1..2}");// 行表达式分表规则courseConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("cid", "course_$->{cid % 2 + 1}"));// 行表达式分库规则courseConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "m$->{user_id % 2 + 1}"));return courseConfig;}/*** 配置公共字典表(每个数据库都有一个t_udict表)* @return*/private TableRuleConfiguration confTudictRule() {TableRuleConfiguration dictConfig = new TableRuleConfiguration("t_udict");dictConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "dict_id"));return dictConfig;}/*** 配置t_user专库配置(垂直分配)* @return*/private TableRuleConfiguration confTUserRule() {// 配置表规则: t_user表在m0库中TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "m$->{0}.t_user");//配置生成策略userRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "user_id"));// 行表达式分表规则userRuleConfiguration.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "t_user"));// 行表达式分库规则,专库专表不需要下面的配置//userRuleConfiguration.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "m$->{user_id % 2 + 1}"));return userRuleConfiguration;}/*** 配置数据源* @return*/private Map<String, DataSource> confDataSourceMap() {DruidDataSource dataSource0 = new DruidDataSource();dataSource0.setDriverClassName(this.driverClassName0);dataSource0.setUrl(this.url0);dataSource0.setUsername(this.username0);dataSource0.setPassword(this.password0);//dataSource0.setFilters(this.filters);DruidDataSource dataSource1 = new DruidDataSource();dataSource1.setDriverClassName(this.driverClassName1);dataSource1.setUrl(this.url1);dataSource1.setUsername(this.username1);dataSource1.setPassword(this.password1);//dataSource1.setFilters(this.filters);DruidDataSource dataSource2 = new DruidDataSource();dataSource2.setDriverClassName(this.driverClassName2);dataSource2.setUrl(this.url2);dataSource2.setUsername(this.username2);dataSource2.setPassword(this.password2);//dataSource2.setFilters(this.filters);//分库设置Map<String, DataSource> dataSourceMap = new HashMap<>(3);//添加两个数据库database0和database1dataSourceMap.put("m0", dataSource0);dataSourceMap.put("m1", dataSource1);dataSourceMap.put("m2", dataSource2);return dataSourceMap;}
}

confTeacherRule()这个方法中的分片策略不是采用配置,而是自定义算法,需要提供如下两个类实现,首先是数据库的分片策略实现类:

public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {@Overridepublic String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {for (String database : databaseNames) {if (database.endsWith("2")) {return database;}}return "";}
}

然后是表的分片策略实现类:

public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {@Overridepublic String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {for (String table : tableNames) {if (table.endsWith(String.valueOf(shardingValue.getValue() % 2 + 1))) {return table;}}return "";}
}

到这里关键的配置就完成了。这里跟上个小节主要的不同在于介绍了用实现类的方式来代替配置,还有就是广播,看下面的关键代码:

/*** 配置公共字典表(每个数据库都有一个t_udict表)* @return*/
private TableRuleConfiguration confTudictRule() {TableRuleConfiguration dictConfig = new TableRuleConfiguration("t_udict");dictConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "dict_id"));return dictConfig;
}//配置公共字典表
shardingRuleConfiguration.getTableRuleConfigs().add(confTudictRule());
//如果添加数据则三个库都会添加,如果删除则三个库都会删除
shardingRuleConfiguration.getBroadcastTables().add("t_udict");

这样一来当保存t_udict的数据时会往edu_db_1、edu_db_2、user_db三个数据库的t_udict表中插入。这里给出t_udict的sql语句,实体类代码就不给出了

CREATE TABLE `t_udict` (`dict_id` bigint(20) NOT NULL,`dict_status` varchar(100) DEFAULT NULL,`dict_value` varchar(100) DEFAULT NULL,PRIMARY KEY (`dict_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

到这里就结束完sharding-jdbc分库分表的实现,下一篇介绍读写分离。