4,项目分库分表实现-广播表配置

敲代码的翠花

已于 2024-01-27 01:49:16 修改

阅读量796

点赞数 13

分类专栏：项目：【今日指数金融】 # (十)分库分表项目集成和安全框架文章标签： java sharding-jdbc

于 2024-01-12 22:50:35 首次发布

本文链接：https://blog.csdn.net/weixin_44478828/article/details/135563118

版权

项目：【今日指数金融】同时被 2 个专栏收录

70 篇文章 1 订阅

订阅专栏

(十)分库分表项目集成和安全框架

10 篇文章 0 订阅

订阅专栏

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.

四张表都插入了数据