写在前面
关于shardingsphere不做介绍，建议移步官网。直接进入主题
准备数据库，数据表
数据库自己建
数据表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for uc_department
-- ----------------------------
DROP TABLE IF EXISTS `uc_department`;
CREATE TABLE `uc_department` (
`id` bigint(20) NOT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 1:删除；0：正常',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gmt_modify` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`create_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`modify_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`db_remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
`org_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '组织id',
`dept_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门id',
`dept_name` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名字',
PRIMARY KEY (`id`) USING BTREE,
INDEX `uc_department_org_id_dept_name_index`(`org_id`, `dept_name`) USING BTREE,
INDEX `idx_org_dept`(`org_id`, `dept_id`) USING BTREE,
INDEX `idx_org_id_id`(`org_id`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '部门表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for uc_user0
-- ----------------------------
DROP TABLE IF EXISTS `uc_user0`;
CREATE TABLE `uc_user0` (
`id` bigint(20) NOT NULL,
`org_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组织id',
`user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
`user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '创建人',
`modify_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modify` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除：0-否，1-是',
`gender` tinyint(4) NULL DEFAULT NULL COMMENT '性别: 0女 1男',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for uc_user1
-- ----------------------------
DROP TABLE IF EXISTS `uc_user1`;
CREATE TABLE `uc_user1` (
`id` bigint(20) NOT NULL,
`org_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组织id',
`user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
`user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '创建人',
`modify_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modify` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除：0-否，1-是',
`gender` tinyint(4) NULL DEFAULT NULL COMMENT '性别: 0女 1男',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
引入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--lombok实体工具-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.4.2</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatisPlus.version}</version>
</dependency>
<!--mybatis-plus 码生成器 添加 模板引擎依赖 这个需要增加模板引擎依赖，如freemarker-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatisPlus.version}</version>
</dependency>
<!--代码生成器依赖-->
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.30</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
这里引入shardingsphere-jdbc-core-spring-boot-starter依赖，便于通过配置文件进行配置
有想法的可以自己引入shardingsphere-jdbc-core，通过java代码形式进行整合，更灵活
添加配置
server.port=8080
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.global-config.db-config.logic-delete-field=deleted
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0
mybatis-plus.mapper-locations=classpath:mapper/*.xml,classpath:mapper/**/*.xml
# 开启sql打印
spring.shardingsphere.props.sql-show=true
spring.shardingsphere.datasource.names=user11,user33,user42
spring.shardingsphere.datasource.user11.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.user11.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.user11.url=jdbc:mysql://localhost:3306/user11?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2b8&rewriteBatchedStatements=true&allowMultiQueries=true
spring.shardingsphere.datasource.user11.username=root
spring.shardingsphere.datasource.user11.password=123456
spring.shardingsphere.datasource.user33.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.user33.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.user33.url=jdbc:mysql://localhost:3306/user33?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2b8&rewriteBatchedStatements=true&allowMultiQueries=true
spring.shardingsphere.datasource.user33.username=root
spring.shardingsphere.datasource.user33.password=123456
spring.shardingsphere.datasource.user42.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.user42.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.user42.url=jdbc:mysql://localhost:3306/user42?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2b8&rewriteBatchedStatements=true&allowMultiQueries=true
spring.shardingsphere.datasource.user42.username=root
spring.shardingsphere.datasource.user42.password=123456
# 自定义分库路由规则 以org_id前两位进行分库 default-sharding 名称可以自定义
spring.shardingsphere.rules.sharding.sharding-algorithms.default-sharding.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.default-sharding.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.default-sharding.props.algorithmClassName=com.example.demo.config.DefaultOrgIdPreciseShardingAlgorithm
# 自定义分表路由规则 以性别进行分表 gender-sharding 名称可以自定义
spring.shardingsphere.rules.sharding.sharding-algorithms.gender-sharding.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.gender-sharding.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.gender-sharding.props.algorithmClassName=com.example.demo.config.GenderPreciseShardingAlgorithm
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=org_id
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=default-sharding
spring.shardingsphere.rules.sharding.tables.uc_department.database-strategy.standard.sharding-column=org_id
spring.shardingsphere.rules.sharding.tables.uc_department.database-strategy.standard.sharding-algorithm-name=default-sharding
spring.shardingsphere.rules.sharding.tables.uc_user.database-strategy.standard.sharding-column=org_id
spring.shardingsphere.rules.sharding.tables.uc_user.database-strategy.standard.sharding-algorithm-name=default-sharding
spring.shardingsphere.rules.sharding.tables.uc_user.actual-data-nodes=user11.uc_user$->{0..1},user33.uc_user$->{0..1},user42.uc_user$->{0..1}
spring.shardingsphere.rules.sharding.tables.uc_user.table-strategy.standard.sharding-column=gender
spring.shardingsphere.rules.sharding.tables.uc_user.table-strategy.standard.sharding-algorithm-name=gender-sharding
spring.shardingsphere.datasource.names=user11,user33,user42 这里我们配置三个真实的数据库名称，以字段org_id 的前两位作为数据库后缀，以进行分库
spring.shardingsphere.rules.sharding.tables.uc_department.database-strategy.standard.sharding-column=org_id
以表里的org_id字段作为分库路由字段
定义分库规则时，需要实现StandardShardingAlgorithm接口，然后将类名配入配置文件
@Slf4j
@NoArgsConstructor
public class DefaultOrgIdPreciseShardingAlgorithm implements StandardShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
return null;
}
private String getSuffix(String orgId) {
if (StringUtils.isEmpty(orgId)) {
return "99";
}
return orgId.substring(0, 2);
}
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> preciseShardingValue) {
String index = getSuffix(preciseShardingValue.getValue());
List<String> datasourceList = availableTargetNames
.stream()
.filter(datasource -> datasource.endsWith(index))
.collect(Collectors.toList());
if (datasourceList.size() == 1) {
return datasourceList.get(0);
}
log.error("分库路由获取失败,{}", index);
throw new RuntimeException("分库路由获取失败");
}
@Override
public void init() {
}
@Override
public String getType() {
return "CLASS_BASED";
}
}
定义分表规则时和分库规则相似
spring.shardingsphere.rules.sharding.tables.uc_user.table-strategy.standard.sharding-column=gender
以表里的gender字段作为分表路由字段 0进user0，1进user1
@Slf4j
@NoArgsConstructor
public class GenderPreciseShardingAlgorithm implements StandardShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> preciseShardingValue) {
List<String> datasourceList = availableTargetNames
.stream()
.filter(datasource -> datasource.endsWith(preciseShardingValue.getValue().toString()))
.collect(Collectors.toList());
if (datasourceList.size() == 1) {
return datasourceList.get(0);
}
log.error("分表路由获取失败");
throw new RuntimeException("分库路由获取失败");
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {
return null;
}
@Override
public void init() {
}
@Override
public String getType() {
return "CLASS_BASED";
}
}
效果图
插入一条记录，orgId 33aa gender 1
逻辑sql 走uc_user表
实际sql路由到user33库，uc_user1表