4、广播表配置
对于stock_business业务表作为广播表处理:
在application-sharding.properties配置其它数据源:
spring.shardingsphere.datasource.names=defdb,ds-2021,ds-2022,ds-2023
spring.shardingsphere.datasource.defdb.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.defdb.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.defdb.url=jdbc:mysql://127.0.0.1:3306/stock_sys_db?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.defdb.username=root
spring.shardingsphere.datasource.defdb.password=root123
spring.shardingsphere.datasource.ds-2021.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-2021.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2021.url=jdbc:mysql://127.0.0.1:3306/stock_db_2021?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds-2021.username=root
spring.shardingsphere.datasource.ds-2021.password=root123
spring.shardingsphere.datasource.ds-2022.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-2022.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2022.url=jdbc:mysql://127.0.0.1:3306/stock_db_2022?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds-2022.username=root
spring.shardingsphere.datasource.ds-2022.password=root123
spring.shardingsphere.datasource.ds-2023.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-2023.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2023.url=jdbc:mysql://127.0.0.1:3306/stock_db_2023?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds-2023.username=root
spring.shardingsphere.datasource.ds-2023.password=root123
#配置默认数据源
spring.shardingsphere.sharding.default-data-source-name=defdb
#配置广播表
spring.shardingsphere.sharding.broadcast-tables=stock_business
#配置开启sql显示
spring.shardingsphere.props.sql.show=true
测试广播表:
@Autowired
private StockBusinessMapper stockBusinessMapper;
/**
* @Description 测试广播表
*/
@Test
public void testBroadCast(){
StockBusiness pojo = StockBusiness.builder().stockCode("90000")
.stockName("900000")
.blockLabel("900000")
.blockName("900000")
.business("900000")
.updateTime(new Date())
.build();
stockBusinessMapper.insert(pojo);
// stockBusinessMapper.deleteByPrimaryKey("90000");
}
运行完查看控制台
2024-01-12 22:44:10.293 INFO 16356 --- [ main] ShardingSphere-SQL : Actual SQL: defdb ::: insert into stock_business
(stock_code, stock_name, block_label, block_name, business, update_time) VALUES (?, ?, ?, ?, ?, ?) ::: [90000, 900000, 900000, 900000, 900000, 2024-01-12 22:44:08.952]
2024-01-12 22:44:10.294 INFO 16356 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2021 ::: insert into stock_business
(stock_code, stock_name, block_label, block_name, business, update_time) VALUES (?, ?, ?, ?, ?, ?) ::: [90000, 900000, 900000, 900000, 900000, 2024-01-12 22:44:08.952]
2024-01-12 22:44:10.296 INFO 16356 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2022 ::: insert into stock_business
(stock_code, stock_name, block_label, block_name, business, update_time) VALUES (?, ?, ?, ?, ?, ?) ::: [90000, 900000, 900000, 900000, 900000, 2024-01-12 22:44:08.952]
2024-01-12 22:44:10.308 INFO 16356 --- [ main] ShardingSphere-SQL : Actual SQL: ds-2023 ::: insert into stock_business
(stock_code, stock_name, block_label, block_name, business, update_time) VALUES (?, ?, ?, ?, ?, ?) ::: [90000, 900000, 900000, 900000, 900000, 2024-01-12 22:44:08.952]
2024-01-12 22:44:10.640 INFO 16356 --- [ionShutdownHook] o.s.a.r.l.SimpleMessageListenerContainer : Waiting for workers to finish.
2024-01-12 22:44:11.070 INFO 16356 --- [ionShutdownHook] o.s.a.r.l.SimpleMessageListenerContainer : Successfully waited for workers to finish.
四张表都插入了数据