ShardingSphere-JDBC实战

一、环境准备

1.数据库

创建2个库2个表：

• xdclass_shop_order_0
  • product_order_0
  • product_order_1
  • ad_config
  • product_order_item_0
  • product_order_item_1
• xdclass_shop_order_1
  • product_order_0
  • product_order_1
  • ad_config
  • product_order_item_0
  • product_order_item_1

数据库脚本：

CREATE TABLE `product_order_0` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
  `state` varchar(11) DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单',
  `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
  `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
  `nickname` varchar(64) DEFAULT NULL COMMENT '昵称',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `ad_config` (
  `id` bigint unsigned NOT NULL COMMENT '主键id',
  `config_key` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
  `config_value` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
  `type` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `product_order_item_0` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_order_id` bigint DEFAULT NULL COMMENT '订单号',
  `product_id` bigint DEFAULT NULL COMMENT '产品id',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `buy_num` int DEFAULT NULL COMMENT '购买数量',
  `user_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2.代码工程

1.工程创建

1. 创建Maven工程，添加相关Maven依赖，

 
        1.8
        11
        11
        2.5.5
        3.4.0
        1.18.16
        4.1.1
        4.12
        1.1.16
        
        true
    
     
        
            org.springframework.boot
            spring-boot-starter-web
            ${spring.boot.version}
        
        
            org.springframework.boot
            spring-boot-starter-test
            ${spring.boot.version}
            test
        
        
        
            com.baomidou
            mybatis-plus-boot-starter
            ${mybatisplus.boot.starter.version}
        
	
        
            mysql
            mysql-connector-java
            8.0.27
        

        
            org.projectlombok
            lombok
            ${lombok.version}
            
        
	
        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            ${sharding-jdbc.version}
        
        
            junit
            junit
            ${junit.version}
        
    
    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
                ${spring.boot.version}
                
                    true
                    true
                
            
        
    

2. 添加数据库配置文件，根据配置文件可知，配置了两个数据库ds0,ds1；

spring.application.name=yb-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true

# 数据源 ds0 ds1
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ybe_shop_order0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=*****

# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ybe_shop_order1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=*****

2.广播表介绍和配置实战

1. 添加AdConfigDO实体类和添加ProductOrderDOMapper类

//数据库实体类
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("ad_config")
public class AdConfigDO {
    private Long id;
    private String configKey;
    private String configValue;
    private String type;
}

//数据库实体配置类
public interface AdConfigMapper extends BaseMapper {
}

2. 设置ad_config为广播表，如果需要配置多个用 逗号 (,) 分开；设置id为生成算法为雪花算法。配置文件中添加如下代码，

#配置广播表
spring.shardingsphere.sharding.broadcast-tables=ad_config
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

3. 添加测试方法

@Test
public void testSaveAdConfig(){
    AdConfigDO adConfigDO = new AdConfigDO();
    adConfigDO.setConfigKey("banner");
    adConfigDO.setConfigValue("ybe.com");
    adConfigDO.setType("ad");
    adConfigMapper.insert(adConfigDO);
}

4. 执行结果，两个数据库的表都进行了更新。如下图

3.绑定表介绍和配置实战

1. 添加ProductOrderItemDO实体类和添加ProductOrderDOMapper类

//数据库实体类
@Data
@TableName("product_order_item")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderItemDO {
    private Long id;
    private Long productOrderId;
    private Long productId;
    private String productName;
    private  Integer buyNum;
    private  Long userId;
}

//数据库实体配置类
public interface ProductOrderItemMapper extends BaseMapper {
}

2. 添加配置文件，设置product_order和product_order_ite为广播表，如果需要配置多个需要配置多行，binding-tables是个数据

#配置绑定表
spring.shardingsphere.sharding.binding-tables[0]=product_order,product_order_item

3. 添加测试方法

@Test
public void testBinding(){
    List