springboot使用sharding-jdbc分表分库/读写分离

pom依赖

<!--分表分库-->
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.0.0-RC1</version>
</dependency>
<!--数据库连接池-->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>1.2.9</version>
</dependency>
<!--mybatis依赖-->
<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>2.2.1</version>
</dependency>

创建数据表

• 数据库A

DROP TABLE IF EXISTS resource_statistics_list_01;
CREATE TABLE resource_statistics_list_01(
    resource_id BIGINT NOT NULL   COMMENT '资源统计主键ID' ,
    resource_dosage INTEGER    COMMENT '资源用量' ,
    energy_type INTEGER    COMMENT '能源类型1=用电,2=用水' ,
    resource_type INTEGER    COMMENT '资源类型1=生产用电,2=照明用电,3=空调用电,4=生产用水,5=卫生间用水,6=中水用水' ,
    revision VARCHAR(64)    COMMENT '乐观锁' ,
    created_by VARCHAR(64)    COMMENT '创建人' ,
    created_time VARCHAR(64)    COMMENT '创建时间' ,
    updated_by VARCHAR(64)    COMMENT '更新人' ,
    updated_time VARCHAR(64)    COMMENT '更新时间' ,
    PRIMARY KEY (resource_id)
)  COMMENT = '资源统计表';


DROP TABLE IF EXISTS resource_statistics_list_02;
CREATE TABLE resource_statistics_list_02(
    resource_id BIGINT NOT NULL   COMMENT '资源统计主键ID' ,
    resource_dosage INTEGER    COMMENT '资源用量' ,
    energy_type INTEGER    COMMENT '能源类型1=用电,2=用水' ,
    resource_type INTEGER    COMMENT '资源类型1=生产用电,2=照明用电,3=空调用电,4=生产用水,5=卫生间用水,6=中水用水' ,
    revision VARCHAR(64)    COMMENT '乐观锁' ,
    created_by VARCHAR(64)    COMMENT '创建人' ,
    created_time VARCHAR(64)    COMMENT '创建时间' ,
    updated_by VARCHAR(64)    COMMENT '更新人' ,
    updated_time VARCHAR(64)    COMMENT '更新时间' ,
    PRIMARY KEY (resource_id)
)  COMMENT = '资源统计表';

• 数据库B

DROP TABLE IF EXISTS resource_statistics_list_01;
CREATE TABLE resource_statistics_list_01(
    resource_id BIGINT NOT NULL   COMMENT '资源统计主键ID' ,
    resource_dosage INTEGER    COMMENT '资源用量' ,
    energy_type INTEGER    COMMENT '能源类型1=用电,2=用水' ,
    resource_type INTEGER    COMMENT '资源类型1=生产用电,2=照明用电,3=空调用电,4=生产用水,5=卫生间用水,6=中水用水' ,
    revision VARCHAR(64)    COMMENT '乐观锁' ,
    created_by VARCHAR(64)    COMMENT '创建人' ,
    created_time VARCHAR(64)    COMMENT '创建时间' ,
    updated_by VARCHAR(64)    COMMENT '更新人' ,
    updated_time VARCHAR(64)    COMMENT '更新时间' ,
    PRIMARY KEY (resource_id)
)  COMMENT = '资源统计表';

DROP TABLE IF EXISTS resource_statistics_list_02;
CREATE TABLE resource_statistics_list_02(
    resource_id BIGINT NOT NULL   COMMENT '资源统计主键ID' ,
    resource_dosage INTEGER    COMMENT '资源用量' ,
    energy_type INTEGER    COMMENT '能源类型1=用电,2=用水' ,
    resource_type INTEGER    COMMENT '资源类型1=生产用电,2=照明用电,3=空调用电,4=生产用水,5=卫生间用水,6=中水用水' ,
    revision VARCHAR(64)    COMMENT '乐观锁' ,
    created_by VARCHAR(64)    COMMENT '创建人' ,
    created_time VARCHAR(64)    COMMENT '创建时间' ,
    updated_by VARCHAR(64)    COMMENT '更新人' ,
    updated_time VARCHAR(64)    COMMENT '更新时间' ,
    PRIMARY KEY (resource_id)
)  COMMENT = '资源统计表';

SQL语句

• resource_id不能写入sql中
• sql中不能有空值否则报错Caused by: java.lang.NullPointerException

    <!--新增数据-->
    <insert id="insert">
        insert into resource_statistics_list(resource_dosage, energy_type, resource_type, created_by,
                                             created_time)
        values (#{resourceDosage}, #{energyType}, #{resourceType}, #{createdBy}, #{createdTime})
    </insert>

主从复制

spring:
 #分表分库配置
  shardingsphere:
    datasource:
      m1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.0.54:32689/energy_consumption_01?serverTimezone=GMT%2B8
        username: root
        password: DCH@2021
      m2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.0.54:32689/energy_consumption_02?serverTimezone=GMT%2B8
        username: root
        password: DCH@2021
      names: m1,m2
    #配置默认数据源ds0 默认数据源,主要用于写
    sharding:
    	#设置默认数据库连接
      default-data-source-name: m1
    #读写分离 单个读写分离
    masterslave:
      #配置主从名称
      name: dataSource
      #配置主库master,负责数据的写入
      master-data-source-name: m1
      #配置从库slave节点
      slave-data-source-names: m2
      #配置slave节点的负载均衡均衡策略,采用轮询机制
      load-balance-algorithm-type: round_robin
    # 显示sql
    props:
      sql:
        show: false
        # show: true

分库分表

spring:
 #分表分库配置
  shardingsphere:
    datasource:
      m1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.0.54:32689/energy_consumption_01?serverTimezone=GMT%2B8
        username: root
        password: DCH@2021
      m2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.0.54:32689/energy_consumption_02?serverTimezone=GMT%2B8
        username: root
        password: DCH@2021
      names: m1,m2
    #是否打印SQL
    props:
      sql:
        show: false
    sharding:
      #设置默认数据库连接
      default-data-source-name: m1
      tables:
        #需要替换的表名称
        resource_statistics_list:
          #指定数据库分布的情况和数据表分布的情况m1 m2   resource_statistics_list_01 resource_statistics_list_02
          actual-data-nodes: m$->{1..2}.resource_statistics_list_0$->{1..2}
          #指定分库策略
          database-strategy:
            inline:
              algorithm-expression: m$->{resource_id% 2 + 1}
              sharding-column: resource_id
          #指定表内主键生成策略
          key-generator:
            column: resource_id
            type: SNOWFLAKE
          #指定分表策略
          table-strategy:
            inline:
              algorithm-expression: resource_statistics_list_0$->{resource_type % 2 + 1}
              sharding-column: resource_type