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>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.1</version>
</dependency>
创建数据表
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 = '资源统计表';
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 (
</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
sharding:
default-data-source-name: m1
masterslave:
name: dataSource
master-data-source-name: m1
slave-data-source-names: m2
load-balance-algorithm-type: round_robin
props:
sql:
show: false
分库分表
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
props:
sql:
show: false
sharding:
default-data-source-name: m1
tables:
resource_statistics_list:
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