ShardingSphere （一）

ShardingSphere-JDBC 与 SpringBoot 集成

ShardingSphere-JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC；
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库。

分库

通过配置user_id将不同的数据存入不同的库表中

依赖配置

org.apache.shardingsphereshardingsphere-jdbc-core-spring-boot-starter5.2.1org.yamlsnakeyaml1.33org.springframework.bootspring-boot-starter-data-jpamysqlmysql-connector-javaorg.springframework.bootspring-boot-starter-testjunitjunit

如果出现以下错误，则需引入 snakeyaml版本为1.33

Caused by: java.lang.NoSuchMethodError: 'void org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(int)'at org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.(ShardingSphereYamlConstructor.java:44)at org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor.(ShardingSphereYamlConstructor.java:41)at org.apache.shardingsphere.infra.util.yaml.YamlEngine.unmarshal(YamlEngine.java:83)

application.yml 配置

spring:shardingsphere:datasource:names: ds-0, ds-1ds-0:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.79.177:14306/datas_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8username: rootpassword: 123456ds-1:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.79.177:14306/datas_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8username: rootpassword: 123456rules:sharding:default-database-strategy:standard:# 以字段分片sharding-column: user_id# 分片算法名称，可自定义sharding-algorithm-name: db-inline# 绑定广播表，多库存在此表且数据一致broadcast-tables: t_subject# 绑定表规则列表binding-tables[0]: t_student, t_student_subjecttables:t_student:actual-data-nodes: ds-$->{0..1}.t_studentkey-generate-strategy:column: idkey-generator-name: snowflaket_student_subject:actual-data-nodes: ds-$->{0..1}.t_student_subjectkey-generate-strategy:column: idkey-generator-name: snowflakekey-generators:snowflake:type: SNOWFLAKEsharding-algorithms:db-inline:type: INLINEprops:algorithm-expression: ds-$->{user_id % 2}props:sql-show: truejpa:properties:hibernate:hbm2ddl:auto: updatedialect: org.hibernate.dialect.MySQL5Dialect
#        show_sql: truedatabase: mysql

创建数据库

创建database datas_0, datas_1

drop database if exists datas_0;
drop database if exists datas_1;
create database datas_0;
create database datas_1;

编写代码

创建对象结构

• Student: id, name, userId
• Subject: id, name
• StudentSubject: id, userId, subjectId

public interface IdObj extends Serializable {Long getId();
}

public class Student implements IdObj {private Long id;private Long userId;private String name;// ommit getter and setter
}

public class StudentSubject implements IdObj {private Long id;private Long subjectId;private Long userId;// ommit getter and setter
}

public class Subject implements IdObj {private Long id;private String name;// ommit getter and setter
}

@Entity
@Table(name = "t_student")
public class StudentEntity extends Student {@javax.persistence.Id@Column(name = "id")@GeneratedValue(strategy = GenerationType.IDENTITY)@Overridepublic Long getId() {return super.getId();}@Column(name = "user_id")@Overridepublic Long getUserId() {return super.getUserId();}@Column(name = "name")@Overridepublic String getName() {return super.getName();}
}

@Entity
@Table(name = "t_student_subject")
public class StudentSubjectEntity extends StudentSubject {@Id@Column(name = "id")@GeneratedValue(strategy = GenerationType.IDENTITY)@Overridepublic Long getId() {return super.getId();}@Column(name = "subject_id")@Overridepublic Long getSubjectId() {return super.getSubjectId();}@Column(name = "user_id")@Overridepublic Long getUserId() {return super.getUserId();}
}

@Entity
@Table(name = "t_subject")
public class SubjectEntity extends Subject {@Id@Column(name = "id")@GeneratedValue(strategy = GenerationType.IDENTITY)@Overridepublic Long getId() {return super.getId();}@Column(name = "name")@Overridepublic String getName() {return super.getName();}
}

创建持久接口

@Repository
public interface StudentRepository extends CrudRepository {
}

@Repository
public interface StudentSubjectRepository extends CrudRepository {
}

@Repository
public interface SubjectRepository extends CrudRepository {
}

创建服务类

@Service
public class StudentService {@ResourceStudentRepository studentRepository;public void save(StudentEntity studentEntity) {studentRepository.save(studentEntity);}
}

@Service
public class StudentSubjectService {@ResourceStudentSubjectRepository studentSubjectRepository;public void save(StudentSubjectEntity studentSubjectEntity) {studentSubjectRepository.save(studentSubjectEntity);}
}

@Service
public class SubjectService {@ResourceSubjectRepository subjectRepository;public SubjectEntity save(SubjectEntity subjectEntity) {return subjectRepository.save(subjectEntity);}
}

测试用例

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingSphereMain.class)
@Rollback(value = false)
public class TestApp {@ResourceStudentService studentService;@ResourceStudentSubjectService studentSubjectService;@ResourceSubjectService subjectService;public void init() {for (long i = 1; i <= 10; i++) {StudentEntity studentEntity = new StudentEntity();studentEntity.setUserId(i);studentService.save(studentEntity);SubjectEntity subjectEntity = new SubjectEntity();subjectEntity.setName("computer_" + i);subjectEntity = subjectService.save(subjectEntity);StudentSubjectEntity studentSubjectEntity = new StudentSubjectEntity();studentSubjectEntity.setSubjectId(10 + subjectEntity.getId());studentSubjectEntity.setUserId(studentEntity.getUserId());studentSubjectService.save(studentSubjectEntity);}}@Testpublic void testData() {init();}
}

执行testcase 生成基础数据

Student 实体类数据会按user_id % 2 的方式入 datas_0, datas_1对应的t_student表中
StudentSubject 实体类数据会按user_id % 2 的方式入 datas_0, datas_1对应的t_student_subject表中
Subject 实体类数据会广播到datas_0, datas_1 对应的t_subject表中

datas_0.t_student

datas_0.t_subject

datas_0.t_student_subject

datas_1.t_student

datas_1.t_subject

datas_1.t_student_subject

分库分表

分库分表只需对上面的文件稍加改动

application.yml 配置

spring:shardingsphere:datasource:names: ds-0, ds-1ds-0:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.79.177:14306/datas_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8username: rootpassword: 123456ds-1:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.79.177:14306/datas_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8username: rootpassword: 123456rules:sharding:default-database-strategy:standard:# 以字段分片sharding-column: user_id# 分片算法名称，可自定义sharding-algorithm-name: db-inline# 绑定广播表，多库存在此表且数据一致broadcast-tables: t_subject# 绑定表规则列表binding-tables[0]: t_student, t_student_subjecttables:t_student:actual-data-nodes: ds-$->{0..1}.t_studentkey-generate-strategy:column: idkey-generator-name: snowflaket_student_subject:actual-data-nodes: ds-$->{0..1}.t_student_subjectkey-generate-strategy:column: idkey-generator-name: snowflakekey-generators:snowflake:type: SNOWFLAKEsharding-algorithms:db-inline:type: INLINEprops:algorithm-expression: ds-$->{user_id % 2}props:sql-show: truejpa:properties:hibernate:hbm2ddl:auto: updatedialect: org.hibernate.dialect.MySQL5Dialect
#        show_sql: truedatabase: mysql