笔记：基于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，不规范的属性名称（如：驼峰或下划线等）会导致属性设置不生
效。