笔记:基于springboot+ShardingSphere-jdbc5.0.0的分库分表(偏yml配置)
笔记:基于springboot+ShardingSphere-jdbc5.0.0的分库分表
虽然ShardingSphere-jdbc5.0.0的版本已经比较低了,但是毕竟是之前项目中比较熟悉的版本,先拿出来回忆熟悉一下,然后再去尝试新版的配置。
这里主要使用yml的方式配置ShardingSphereJdbc分库分表。
如果你喜欢代码配置方式:笔记:基于springboot+ShardingSphere-jdbc5.0.0的分库分表(偏代码配置)
1、Maven的基本配置。
<!-- spring boot 集合包-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${springboot.Version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<!-- 引入fastjson依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>2.0.9</version>
</dependency>
<!-- 引入mybatis-plus依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<!-- shardingsphere集合springboot依赖 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0</version>
</dependency>
2、yml配置数据源和分库分表策略,当id<2025000放到库1,否则放库2,如果id是偶数放表1,是奇数放表2。
spring:
shardingsphere:
# 展示修改以后的sql语句
props:
sql-show: true
datasource:
names: childtrain1,childtrain2 # 数据源名称,逗号分隔
childtrain1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/childtrain1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
childtrain2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/childtrain2?serverTimezone=UTC&useSSL=false
username: root
password: 123456
rules:
sharding:
tables:
student: # 需要进行分库分表的表名,根据实际情况修改
actual-data-nodes: childtrain$->{1..2}.student_$->{1..2} # 分库分表策略,例如 ds0, ds1 分库;t_order_0, t_order_1 分表
database-strategy:
standard:
sharding-column: id # 分片列名称,例如 order_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: id # 分片列名称,例如 order_id
sharding-algorithm-name: student-inline
# key-generator:
# type: SNOWFLAKE # 主键生成器类型,例如 SNOWFLAKE(雪花算法)
# column: id # 主键列名称,例如 id
sharding-algorithms:
database-inline:
type: INLINE # 行表达式策略类型
props:
algorithm-expression: childtrain$->{id < 2025000 ? 1:2} # 分片算法表达式,例如根据 order_id 的奇偶性决定使用哪个分表
# type: CLASS_BASED # 行表达式策略类型
# props:
# strategy: STANDARD
# algorithmClass‐Name: cloud.cloudwings.hwq.third.common.sharding.DbStandardShardingAlgorithm # 路径
student-inline:
type: INLINE # 行表达式策略类型
props:
algorithm-expression: student_$->{id % 2 + 1} # 分片算法表达式,例如根据 order_id 的奇偶性决定使用哪个分表
# type: CLASS_BASED # 行表达式策略类型
# props:
# strategy: STANDARD
# algorithmClass‐Name: cloud.cloudwings.hwq.third.common.sharding.TableStandardShardingAlgorithm # 路径
3、增加测试的新增和查询。
创建类:Student.java、StudentController.java、IStudentService.java、StudentServiceImpl.java、StudentMapper.java、StudentMapper.xml。
import java.io.Serializable;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
*
* @author HuWenQing
* @date 2025年3月5日 上午11:26:55
*
*/
@Data
@TableName("student")
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private String name;
private Integer age;
private Date createdate;
private boolean isdelete;
}
import java.util.List;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.fastjson.JSONObject;
import cloud.cloudwings.hwq.second.modules.student.entity.Student;
import cloud.cloudwings.hwq.second.modules.student.service.IStudentService;
import lombok.AllArgsConstructor;
/**
*
* @author HuWenQing
* @date 2025年3月5日 上午11:33:28
*
*/
@RestController
@AllArgsConstructor
@RequestMapping("/student")
public class StudentNewController {
private final IStudentService studentService;
@GetMapping("/selectAll")
public JSONObject selectAll(Student student) {
JSONObject returnJson = new JSONObject();
List<Student> students = studentService.selectAll(student);
returnJson.put("code", 200);
returnJson.put("message", "成功");
returnJson.put("data", students);
return returnJson;
}
@GetMapping("/insertBatchTest")
public JSONObject insertBatchTest() {
JSONObject returnJson = new JSONObject();
studentService.insertBatchTest();
returnJson.put("code", 200);
returnJson.put("message", "成功");
returnJson.put("data", null);
return returnJson;
}
}
import java.util.List;
import com.baomidou.mybatisplus.extension.service.IService;
import cloud.cloudwings.hwq.second.modules.student.entity.Student;
/**
*
* @author HuWenQing
* @date 2025年3月5日 上午11:40:22
*
*/
public interface IStudentService extends IService<Student> {
/**
* 查询
* @param student
* @return
*/
List<Student> selectAll(Student student);
/**
* 批次新增-测试
*/
void insertBatchTest();
}
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import cloud.cloudwings.hwq.second.modules.student.entity.Student;
import cloud.cloudwings.hwq.second.modules.student.mapper.StudentMapper;
import cloud.cloudwings.hwq.second.modules.student.service.IStudentService;
/**
*
* @author HuWenQing
* @date 2025年3月5日 上午11:41:02
*
*/
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements IStudentService {
@Override
public List<Student> selectAll(Student student) {
return baseMapper.selectAll(student);
// QueryWrapper<Student> wrapper = new QueryWrapper<>();
// return baseMapper.selectList(wrapper);
}
@Override
public void insertBatchTest() {
List<Student> students = new ArrayList<>();
for(int i=0; i<20; i++) {
Student student = new Student();
student.setId(2024990 + i);
student.setName("student" + i);
student.setAge(10);
student.setCreatedate(new Date());
students.add(student);
}
super.saveBatch(students);
}
}
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import cloud.cloudwings.hwq.second.modules.student.entity.Student;
/**
*
* @author HuWenQing
* @date 2025年3月5日 上午11:44:54
*
*/
public interface StudentMapper extends BaseMapper<Student> {
public List<Student> selectAll(@Param("student") Student student);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cloud.cloudwings.hwq.second.modules.student.mapper.StudentMapper">
<!-- 通用查询映射结果 -->
<resultMap id="studentResultMap" type="cloud.cloudwings.hwq.second.modules.student.entity.Student">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="createdate" property="createdate"/>
<result column="isdelete" property="isdelete"/>
</resultMap>
<select id="selectAll" resultMap="studentResultMap">
select a.* from student a where a.isdelete = 0
<include refid="selectAllWhere"/>
order by a.id desc
</select>
<sql id="selectAllWhere">
<if test="student.id != null">
and a.id = #{student.id}
</if>
</sql>
</mapper>
4、创建数据库childtrain1和childtrain2,两个库都创建student、student_1、student_2。
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(16) DEFAULT NULL COMMENT '学生姓名',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`createdate` datetime DEFAULT NULL,
`isdelete` tinyint(1) NOT NULL COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2018113 DEFAULT CHARSET=utf8;
CREATE TABLE `student_1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(16) DEFAULT NULL COMMENT '学生姓名',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`createdate` datetime DEFAULT NULL,
`isdelete` tinyint(1) NOT NULL COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2024999 DEFAULT CHARSET=utf8;
CREATE TABLE `student_2` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(16) DEFAULT NULL COMMENT '学生姓名',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`createdate` datetime DEFAULT NULL,
`isdelete` tinyint(1) NOT NULL COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2025000 DEFAULT CHARSET=utf8;
5、启动类application.java和完整的yml配置文件:
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan(basePackages = "cloud.cloudwings.hwq.third.modules.*.mapper")
public class ThirdApplication {
public static void main(String[] args) {
SpringApplication.run(ThirdApplication.class, args);
}
}
server:
port: 8083 #端口号
spring:
main:
allow-circular-references: true #允许循环依赖
#忽略数据源
autoconfigure:
# exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
exclude: org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
shardingsphere:
# 展示修改以后的sql语句
props:
sql-show: true
datasource:
names: childtrain1,childtrain2 # 数据源名称,逗号分隔
childtrain1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/childtrain1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
childtrain2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/childtrain2?serverTimezone=UTC&useSSL=false
username: root
password: 123456
rules:
sharding:
tables:
student: # 需要进行分库分表的表名,根据实际情况修改
actual-data-nodes: childtrain$->{1..2}.student_$->{1..2} # 分库分表策略,例如 ds0, ds1 分库;t_order_0, t_order_1 分表
database-strategy:
standard:
sharding-column: id # 分片列名称,例如 order_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: id # 分片列名称,例如 order_id
sharding-algorithm-name: student-inline
# key-generator:
# type: SNOWFLAKE # 主键生成器类型,例如 SNOWFLAKE(雪花算法)
# column: id # 主键列名称,例如 id
sharding-algorithms:
database-inline:
type: INLINE # 行表达式策略类型
props:
algorithm-expression: childtrain$->{id < 2025000 ? 1:2} # 分片算法表达式,例如根据 order_id 的奇偶性决定使用哪个分表
# type: CLASS_BASED # 行表达式策略类型
# props:
# strategy: STANDARD
# algorithmClass‐Name: cloud.cloudwings.hwq.third.common.sharding.DbStandardShardingAlgorithm # 路径
student-inline:
type: INLINE # 行表达式策略类型
props:
algorithm-expression: student_$->{id % 2 + 1} # 分片算法表达式,例如根据 order_id 的奇偶性决定使用哪个分表
mybatis: #mybatis配置
mapper-locations: classpath*:/mapper/*.xml
mybatis-plus:
mapper-locations: classpath:cloud.cloudwings.hwq.third.modules/**/mapper/*Mapper.xml
#实体扫描,多个package用逗号或者分号分隔
typeAliasesPackage: cloud.cloudwings.hwq.third.modules.**.entity
global-config:
# 关闭MP3.0自带的banner
banner: false
db-config:
#主键类型 0:"数据库ID自增", 1:"不操作", 2:"用户输入ID",3:"数字型snowflake", 4:"全局唯一ID UUID", 5:"字符串型snowflake";
id-type: AUTO
#字段策略
insert-strategy: not_null
update-strategy: not_null
where-strategy: not_null
#驼峰下划线转换
table-underline: true
# 逻辑删除配置
# 逻辑删除全局值(1表示已删除,这也是Mybatis Plus的默认配置)
logic-delete-value: 1
# 逻辑未删除全局值(0表示未删除,这也是Mybatis Plus的默认配置)
logic-not-delete-value: 0
configuration:
#实体属性驼峰转换
map-underscore-to-camel-case: true
cache-enabled: false
jdbc-type-for-null: 'null'
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #打印sql日志,会打印查询结果,不推荐
# log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
logging:
level:
#打印sql日志,不打印查询结果,推荐
cloud.cloudwings.hwq.third.modules: debug
9、编译并启动。
打开网页,输入http://localhost:8082/student/insertBatchTest新增数据,可以看到如下控制台打印。
查看数据库,发现确实进行了分库分表存储。
库1数据:
库2数据:
输入http://localhost:8082/student/selectAll新增数据,可以看到如下返回。
到此,分库分表完成。
10、如果你想结合代码的方式配置分库分表算法,那么只要将sharding-algorithms改为下面的配置。
sharding-algorithms:
database-inline:
# type: INLINE # 行表达式策略类型
# props:
# algorithm-expression: childtrain$->{id < 2025000 ? 1:2} # 分片算法表达式,例如根据 order_id 的奇偶性决定使用哪个分表
type: CLASS_BASED # 行表达式策略类型
props:
strategy: STANDARD
algorithmClass‐Name: cloud.cloudwings.hwq.third.common.sharding.DbStandardShardingAlgorithm # 路径
student-inline:
# type: INLINE # 行表达式策略类型
# props:
# algorithm-expression: student_$->{id % 2 + 1} # 分片算法表达式,例如根据 order_id 的奇偶性决定使用哪个分表
type: CLASS_BASED # 行表达式策略类型
props:
strategy: STANDARD
algorithmClass‐Name: cloud.cloudwings.hwq.third.common.sharding.TableStandardShardingAlgorithm # 路径
并创建DbStandardShardingAlgorithm.java和TableStandardShardingAlgorithm.java。
import java.util.Collection;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import lombok.extern.slf4j.Slf4j;
/**
*
* @author HuWenQing
* @date 2025年3月13日 下午3:02:32
*
*/
@Slf4j
public class DbStandardShardingAlgorithm implements StandardShardingAlgorithm<String>{
@Override
public String getType() {
log.info("进入DbStandardShardingAlgorithm的getType方法");
return "STANDARD";
}
@Override
public void init() {
log.info("进入DbStandardShardingAlgorithm的init方法");
}
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
log.info("进入DbStandardShardingAlgorithm的doSharding1方法:" + availableTargetNames);
log.info("进入DbStandardShardingAlgorithm的doSharding1方法:" + shardingValue);
String id = String.valueOf(shardingValue.getValue());
if(id != null && id.trim().length() > 0) {
return "childtrain" + (Integer.parseInt(id) < 2025000 ? 1: 2);
}
throw new UnsupportedOperationException();
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
RangeShardingValue<String> shardingValue) {
log.info("进入DbStandardShardingAlgorithm的doSharding2方法:" + availableTargetNames);
log.info("进入DbStandardShardingAlgorithm的doSharding2方法:" + shardingValue);
return availableTargetNames;
}
}
import java.util.Collection;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import lombok.extern.slf4j.Slf4j;
/**
* 分表算法配置
* @author HuWenQing
* @date 2025年3月10日 下午1:40:46
*
*/
@Slf4j
public class TableStandardShardingAlgorithm implements StandardShardingAlgorithm<String> {
@Override
public String getType() {
log.info("进入TableStandardShardingAlgorithm的getType方法");
return "STANDARD";
}
@Override
public void init() {
log.info("进入TableStandardShardingAlgorithm的init方法");
}
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
log.info("进入TableStandardShardingAlgorithm的doSharding1方法:" + availableTargetNames);
log.info("进入TableStandardShardingAlgorithm的doSharding1方法:" + shardingValue);
String id = String.valueOf(shardingValue.getValue());
if(id != null && id.trim().length() > 0) {
return shardingValue.getLogicTableName() + "_" + (Integer.parseInt(id) % 2 + 1);
}
throw new UnsupportedOperationException();
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
RangeShardingValue<String> shardingValue) {
log.info("进入TableStandardShardingAlgorithm的doSharding2方法:" + availableTargetNames);
log.info("进入TableStandardShardingAlgorithm的doSharding2方法:" + shardingValue);
return availableTargetNames;
}
}
最后的运行效果和上面的配置是一样的。
PS:这里补充一下碰到的问题,中间出现启动之后报错:Inline sharding algorithm expression cannot be null.
这个其实我策略名称(sharding-algorithm-name: database_inline)使用了下划线,无法识别到配置信息。阅读官方文档之后,发现策略名称配置成下划线和驼峰都不行。
以下是ShardingSphere5.0.0官方文档9.8.30原文:
9.8.30 [其他] 使用 Spring Boot 2.x 集成 ShardingSphere 时,配置文件中的属性设置不生效?
回答:
需要特别注意,Spring Boot 2.x 环境下配置文件的属性名称约束为仅允许小写字母、数字和短横线,即[a-z][0-9] 和-。
原因如下:
Spring Boot 2.x 环境下,ShardingSphere 通过 Binder 来绑定配置文件,属性名称不规范(如:驼峰或下划线等)会导致属性设置不生效从而校验属性值时抛出 NullPointerException 异常。参考以下错误
示例:
下划线示例:database_inline
spring.shardingsphere.rules.sharding.sharding-algorithms.database_inline.
type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database_inline.props.
algorithm-expression=ds-$->{user_id % 2}
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating
bean with name 'database_inline': Initialization of bean failed; nested exception
is java.lang.NullPointerException: Inline sharding algorithm expression cannot be
null.
...
Caused by: java.lang.NullPointerException: Inline sharding algorithm expression
cannot be null.
at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:897)
at org.apache.shardingsphere.sharding.algorithm.sharding.inline.
InlineShardingAlgorithm.getAlgorithmExpression(InlineShardingAlgorithm.java:58)
at org.apache.shardingsphere.sharding.algorithm.sharding.inline.
InlineShardingAlgorithm.init(InlineShardingAlgorithm.java:52)
at org.apache.shardingsphere.spring.boot.registry.
AbstractAlgorithmProvidedBeanRegistry.
postProcessAfterInitialization(AbstractAlgorithmProvidedBeanRegistry.java:98)
at org.springframework.beans.factory.support.
AbstractAutowireCapableBeanFactory.
applyBeanPostProcessorsAfterInitialization(AbstractAutowireCapableBeanFactory.
java:431)
...
驼峰示例:databaseInline
spring.shardingsphere.rules.sharding.sharding-algorithms.databaseInline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.databaseInline.props.
algorithm-expression=ds-$->{user_id % 2}
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating
bean with name 'databaseInline': Initialization of bean failed; nested exception is
java.lang.NullPointerException: Inline sharding algorithm expression cannot be
null.
9.8. FAQ 315
Apache ShardingSphere document
...
Caused by: java.lang.NullPointerException: Inline sharding algorithm expression
cannot be null.
at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:897)
at org.apache.shardingsphere.sharding.algorithm.sharding.inline.
InlineShardingAlgorithm.getAlgorithmExpression(InlineShardingAlgorithm.java:58)
at org.apache.shardingsphere.sharding.algorithm.sharding.inline.
InlineShardingAlgorithm.init(InlineShardingAlgorithm.java:52)
at org.apache.shardingsphere.spring.boot.registry.
AbstractAlgorithmProvidedBeanRegistry.
postProcessAfterInitialization(AbstractAlgorithmProvidedBeanRegistry.java:98)
at org.springframework.beans.factory.support.
AbstractAutowireCapableBeanFactory.
applyBeanPostProcessorsAfterInitialization(AbstractAutowireCapableBeanFactory.
java:431)
...
从异常堆栈中分析可知:AbstractAlgorithmProvidedBeanRegistry.registerBean 方法调用
PropertyUtil.containPropertyPrefix(environment, prefix) 方法判断指定前缀 prefix
的配置是否存在,而 PropertyUtil.containPropertyPrefix(environment, prefix) 方法,在
Spring Boot 2.x 环境下使用了 Binder,不规范的属性名称(如:驼峰或下划线等)会导致属性设置不生
效。