ShardingSphere集成SpringBoot实现分库分表(一)-分库_springboot shardingsphere

1.ShardingSphere概述：

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成,我们只关注 Sharding-JDBC即可    详情可见官网（地址：功能 :: ShardingSphere）

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

1.1 适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
1.2 基于任何第三方的数据库连接池，如：DBCP, C3P0, Druid等。
1.3 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL

1.4  Sharding-JDBC主要实现数据分片和读写分离，通过Sharding-JDBC，应用可以透明的使用jdbc访问已经分库分表，读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布

2.搭建ShardingSphere项目：

2.1  项目结果如下：

2.2 引入相关的pom依赖,本项目主要集成mybatis-plus  引入的依赖如下：

<?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.7.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.shardingsphere</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingsphere</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
            <version>4.1.1</version>
        </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>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.8</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.3  创建测试的数据库表user 分别创建两个测试库ds-0  ds-1  然后分别在ds-0  ds-1库中创建数据库表user  创建脚本如下：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(32) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(5) NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `education` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `creation_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

2.4 规则配置  主要通过sharding-jdbc对分库分表的规则进行配置,配置内容包括：数据源、主键生成策略、分片策略等  application.yml配置文件如下：

spring:
  shardingsphere:
    datasource:
      names: ds-0,ds-1
      ds_0:
        url: jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: hzp123456
      ds_1:
        url: jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: hzp123456
    sharding:
      tables:
        user:
          actual-data-nodes: ds-$->{0..1}.user
          database-strategy:
            standard:
              sharding-column: age
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker.id: 11
              executor.size: 4
              max.connections.size.per.query: 1
              check.table.metadata.enabled: false
    props:
      sql.show: true

以上配置解释说明：

配置数据节点,指定节点的信息：actual-data-nodes: ds-$->{0..1}.user    $ 会被 大括号中的 {0…1} 所替换  生成对应的结果为ds-0.user和ds-1.user 

分库策略：

sharding-column: age

precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm

主要通过数据库中表user的age实现表的分库  具体可根据自己项目实际业务逻辑实现   以上配置通过类DefaultModuloPreciseShardingAlgorithm实现   DefaultModuloPreciseShardingAlgorithm类为：

package com.shardingsphere.algorithm;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

public class DefaultModuloPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String modulo = String.valueOf(shardingValue.getValue() % availableTargetNames.size());

        for (String targetName : availableTargetNames) {
            if (targetName.endsWith(modulo)) return targetName;
        }

        throw new UnsupportedOperationException();
    }
}

由以上可看出当age为偶数时向ds-0库中的user表插入数据   当age为奇数时   向ds-1库中的user表插入数据  具体结果如下  ds-0库：

ds-1库：

2.5  测试user实现分库  首先创建下user实体类：

package com.shardingsphere.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @Author 何志鹏
 * @Date 2022/7/15 15:02
 * @Version 1.0
 */
@Data
public class User implements Serializable {

    private static final long serialVersionUID = -94704944729049233L;

    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private  String name;
    private Long age;
    private  String sex;
    private  String education;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date creationTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date updateTime;

}

2.6  创建一个UserMapper接口  继承mybatisplus中的BaseMapper  如下：

package com.shardingsphere.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingsphere.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

/**
 * @Author 何志鹏
 * @Date 2022/7/15 15:10
 * @Version 1.0
 */
@Mapper
@Repository
public interface UserMapper  extends BaseMapper<User> {

}

2.7  写一个测试类DemoTest   向user表中写入数据  测下分库的结果  并且进行查询操作  代码如下：

package com.shardingsphere;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.shardingsphere.Mapper.UserMapper;
import com.shardingsphere.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;

/**
 * @Author 何志鹏
 * @Date 2022/7/15 15:15
 * @Version 1.0
 */
@SpringBootTest
public class DemoTest {



    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        User user = new User();
        user.setName("何志鹏");
        int randomNum = (int) (Math.random()*9000+1000);
        user.setAge(new Long(randomNum));
        user.setSex("1");
        user.setEducation("1");
        user.setCreationTime(new Date());
        user.setUpdateTime(new Date());
        userMapper.insert(user);

        //ds-0库
        User user0 = userMapper.selectById("1547874667537178626");
        System.err.println("ds-0库:"+JSONUtil.parseObj(user0));
        //ds-1库
        User user1 = userMapper.selectById("1547874960127582210");
        System.err.println("ds-1库:"+JSONUtil.parseObj(user1));


    }
}

执行查询结果如下：

最后附上代码： https://gitee.com/hezhipeng_ek/shardingsphere.git