环境配置:
JDK1.8
SpringBoot 2.6.3
Mysql 8.0
1、初始环境搭建成功:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>org.apache</groupId>
<artifactId>ShardingSphere</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>ShardingSphere</name>
<description>ShardingSphere</description>
<packaging>jar</packaging>
<properties>
<java.version>1.8</java.version>
<druid.version>1.1.13</druid.version>
<commons.lang.version>2.6</commons.lang.version>
<commons.io.version>2.5</commons.io.version>
<commons.configuration.version>1.10</commons.configuration.version>
<fastjson.version>1.2.60</fastjson.version>
<pagehelper.spring.boot.version>1.4.1</pagehelper.spring.boot.version>
<mybatis-plus.version>3.5.2</mybatis-plus.version>
<hutool.version>5.8.3</hutool.version>
<swagger.version>3.0.0</swagger.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>${commons.lang.version}</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>${commons.io.version}</version>
</dependency>
<dependency>
<groupId>commons-configuration</groupId>
<artifactId>commons-configuration</artifactId>
<version>${commons.configuration.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper.spring.boot.version}</version>
<exclusions>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</exclusion>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--mysql-connector-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--swagger3-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-boot-starter</artifactId>
<version>${swagger.version}</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.6</version>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<!--hutool-util-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>${hutool.version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<configuration>
<skip>true</skip>
</configuration>
</plugin>
</plugins>
</build>
</project>
2、集成ShardingSphere
<!--shardingsphere-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
2.1 根据ID进行分表:
- 建表user_1,user_2
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 100307
Source Host : localhost:3306
Source Schema : system
Target Server Type : MySQL
Target Server Version : 100307
File Encoding : 65001
Date: 14/08/2022 17:06:01
*/
SET NAMES utf8mb4;
SET
FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
`age` int NULL DEFAULT NULL COMMENT '年龄',
`sex` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
`tel` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地址',
`email` varchar(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
SET
FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `user_2`;
CREATE TABLE `user_2`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
`age` int NULL DEFAULT NULL COMMENT '年龄',
`sex` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
`tel` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地址',
`email` varchar(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
SET
FOREIGN_KEY_CHECKS = 1;
- 配置文件
# 根据ID进行分表
# 根据ID分表,奇数user_1,偶数user_2
spring.shardingsphere.datasource.names=db
spring.main.allow-bean-definition-overriding=true
## 配置数据源
spring.shardingsphere.datasource.db.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db.url=jdbc:mysql://127.0.0.1:3307/db?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db.username=root
spring.shardingsphere.datasource.db.password=root
# 指定库、表分布情况
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db.user_$->{1..2}
# 指定user表的主键生成策略为SNOWFLAKE
# 指定user表里面主键id 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 表策略
# 指定分片策略 约定id值偶数添加到user_1表,如果id是奇数添加到user_2表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id%2+1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
- 生成100个用户结果:
2.2 根据性别gender分库,id分表
# 根据ID进行分表
# 根据ID分表,奇数user_1,偶数user_2
spring.shardingsphere.datasource.names=db1,db2
spring.main.allow-bean-definition-overriding=true
## 配置数据源1
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://127.0.0.1:3307/db1?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root
## 配置数据源2
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://127.0.0.1:3307/db2?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root
# 指定库、表分布情况
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db$->{1..2}.user_$->{1..2}
# 指定user表的主键生成策略为SNOWFLAKE
# 指定user表里面主键id 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 表策略
# 指定分片策略 约定id值偶数添加到user_1表,如果id是奇数添加到user_2表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id%2+1}
# 指定分库策略 约定gender值偶数添加到db1库,如果gender是奇数添加到db2库
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=gender
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=m$->{gender%2+1}
# 库策略
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
- 生成100个用户数据:
期待结果:age偶数放db1,age奇数放db2,id偶数放user_1,id奇数放user_2.
遇到的问题:
- DJK17 报错:
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.reflect.InaccessibleObjectException: Unable to make field private static final long java.lang.Number.serialVersionUID accessible: module java.base does not "opens java.lang" to unnamed module @97e1986
### The error may exist in com/demo/sharding/dao/UserDao.java (best guess)
### The error may involve com.demo.sharding.dao.UserDao.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO user ( id, name, age, sex, tel, address, email ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
### Cause: java.lang.reflect.InaccessibleObjectException: Unable to make field private static final long java.lang.Number.serialVersionUID accessible: module java.base does not "opens java.lang" to unnamed module @97e1986
解决方法:
-ea
--add-opens
java.base/java.lang=ALL-UNNAMED
--add-opens
java.base/java.util=ALL-UNNAMED
--add-opens
java.base/java.nio=ALL-UNNAMED
--add-opens
java.base/sun.nio.ch=ALL-UNNAMED
代码demo地址:
https://gitee.com/Romen_Geng/sharding-demo.git