SpringBoot集成Sharding-jdbc（水平分表）

koumaisui

已于 2024-04-20 11:16:33 修改

阅读量1.6k

点赞数 37

分类专栏： SpringBoot Sharding-JDBC Mybatis 文章标签： spring boot 后端 java Sharding-JDBC 分表

于 2024-04-20 11:13:56 首次发布

本文链接：https://blog.csdn.net/qq_42569028/article/details/137993202

版权

SpringBoot 同时被 3 个专栏收录

20 篇文章 0 订阅

订阅专栏

Mybatis

8 篇文章 0 订阅

订阅专栏

Sharding-JDBC

5 篇文章 0 订阅

订阅专栏

1.Sharding-jdbc的应用场景

其实就是针对分库分表后的操作简化，相当于增强版的JDBC驱动

框架：
在这里插入图片描述
执行步骤：

解析SQL，获取片键值，在本文中设置的片键值是order_id
Sharding-JDBC通过规则配置t_order_$->{order_id % 2+1}，可知当order_id为偶数时，应该往t_order_1表中插数据，为奇数时，往t_order_2表中插入数据
那么Sharding-JDBC根据order_id的值改写SQL语句，改写后的SQL语句是真实要执行的SQL语句
接着，执行改写后的真实SQL语句
最后将所有真正执行的SQL结果进行合并汇总，返回

从上面的执行过程可知，只需要配置好片键值，Sharding-JDBC自动帮你根据规则请求表，极大了简化了开发

2.实际使用

2.0 项目层级

在这里插入图片描述

2.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>
    <groupId>com.test.sharding</groupId>
    <artifactId>Sharding-JDBC-Test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Sharding-JDBC-Test</name>
    <description>Sharding-JDBC-Test</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.4.2</spring-boot.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <!--sharding-jdbc-->
        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!--mybatis分页依赖-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
            <version>2.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.jaxb</groupId>
            <artifactId>jaxb-runtime</artifactId>
            <version>2.3.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.test.sharding.ShardingJdbcTestApplication</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

这里需要特别注意SpringBoot、Sharding-jdbc和mybatis的版本，之前我使用的是最新的SpringBoot3.24版本，而Sharding-jdbc无论用啥版本都报错，显示无法找到url，驱动类。然后如果mybatis版本比较低的话，就会导致使用@Autowired无法自动注入，找不到对应的Bean。

因此，最好的解决方式就是使用SpringBoot的2.x版本，Jdk1.8即可。

2.2 application.yml配置


server:
  port: 8080

mybatis:
  #mapper文件存放位置
  mapper-locations: classpath:/mapper/*.xml
  # 实体类的存放地址
  type-aliases-package: com.test.sharding.domain.pojo
  configuration:
    # 是否开启驼峰 Ming == m_ing
    map-underscore-to-camel-case: true

spring:
  main:
    allow-bean-definition-overriding: true
  application:
    name: sharding-jdbc-test
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: db1
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        #mysql驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding-jdbc-test?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: root
    sharding:
      tables:
        #scs_product可以任意命名，sql中一致，为了方便理解，这里一般写分表共有的
        # 比如我的scs_product_1  和 scs_product_2  那就写scs_product好区分
        # 当执行sql中出现scs_product，sharding-jdbc会将其操作到对应的表
        scs_product:
          actual-data-nodes: db1.scs_product_$->{1..2}
          # 指定主键生成策略为雪花id,全局主键
          key-generator:
            column: PRODUCT_ID
            type: SNOWFLAKE
          #指定scs_product表的分片策略，  分片键和分片算法 用于计算真正的表名
          table-strategy:
            inline:
              # 偶数进到scs_product_1  奇数进到scs_product_2
              # 对于根据分片字段为条件的，会先判断是否涉及两张表，
              # 如何是两张表则会两个表都查，如果只涉及单表，则只查询一张表。
              algorithm-expression: scs_product_$->{PRODUCT_ID % 2 + 1}
              # 指定分片键为PRODUCT_ID
              sharding-column: PRODUCT_ID

2.3 dao层

ShardingMapper.java

@Mapper
public interface ShardingMapper {
    void insertProduct(@Param("scsProduct") ScsProduct scsProduct);

    List<ScsProduct> selectProduct();

    //根据id列表查询商品
    List<ScsProduct> selectProductByIds(@Param("ids") List<Long> ids);
}

2.4 对应的mybatis的xml文件

<?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="com.test.sharding.dao.ShardingMapper">
  <resultMap id="BaseResultMap" type="com.test.sharding.domain.pojo.ScsProduct">
    <!--@mbg.generated-->
    <id column="PRODUCT_ID" jdbcType="BIGINT" property="productId" />
    <result column="PRODUCT_CODE" jdbcType="VARCHAR" property="productCode" />
    <result column="PRODUCT_NAME" jdbcType="VARCHAR" property="productName" />
    <result column="PRODUCT_AMOUNT" jdbcType="INTEGER" property="productAmount" />
  </resultMap>
  <insert id="insertProduct" parameterType="com.test.sharding.domain.pojo.ScsProduct">
    insert into scs_product (PRODUCT_CODE, PRODUCT_NAME, PRODUCT_AMOUNT)
    values (#{scsProduct.productCode, jdbcType=VARCHAR}, #{scsProduct.productName, jdbcType=VARCHAR}, #{scsProduct.productAmount, jdbcType=INTEGER})
  </insert>

  <select id="selectProduct" resultMap="BaseResultMap">
    select PRODUCT_ID, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_AMOUNT
    from scs_product
<!--    where PRODUCT_CODE = 'SHARDING-JDBC166' --><!--此时的真是查询sql是两个表都查-->
<!--    PRODUCT_ID in (979716175636201483,979716175636201481) 因为都是奇数，则只查询 scs_product_2-->
    <!-- PRODUCT_ID = 979716175636201483  此时会触发分片机制，根据主键字段分片查询对应的表 scs_product_2-->
<!--    PRODUCT_ID in (979716175636201483,979716175636201481,979716185538953216,979716184402296836)-->
<!--    order by PRODUCT_AMOUNT desc-->
    <!-- 对于根据分片字段为条件的，会先判断是否涉及两张表，如何是两张表则会两个表都查，如果只涉及单表，则只查询一张表-->
  </select>

  <select id="selectProductByIds" resultMap="BaseResultMap" resultType="com.test.sharding.domain.pojo.ScsProduct">
    select PRODUCT_ID, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_AMOUNT
    from scs_product
    where PRODUCT_ID in
      <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
        #{item}
      </foreach>
  </select>
</mapper>

2.5 Service层

ShardingService.java

public interface ShardingService {
    void insertProduct(ScsProduct scsProduct);

    List<ScsProduct> queryProduct();

    PageInfo<ScsProduct> queryProductPage();

    List<ScsProduct> queryProductByIds(List<Long> ids);
}

ShardingServiceImpl.java

@Service
public class ShardingServiceImpl implements ShardingService {

    @Resource
    private ShardingMapper shardingMapper;
    @Override
    @Async(value = "taskThread")
//    @Transactional(rollbackFor = {TRANSACTION_REQUIRED.class})
    public void insertProduct(ScsProduct scsProduct) {
        scsProduct.setProductName("小商品系列-sharding-jdbc"+Thread.currentThread().getName());
        shardingMapper.insertProduct(scsProduct);
    }

    @Override
    public List<ScsProduct> queryProduct() {
        return shardingMapper.selectProduct();
    }

    @Override
    public PageInfo<ScsProduct> queryProductPage() {
        Integer pageIndex = 1;
        Integer pageSize = 15;
        PageHelper.startPage(pageIndex,pageSize);
        List<ScsProduct> scsProductList = shardingMapper.selectProduct();
        return new PageInfo<>(scsProductList);
    }

    @Override
    public List<ScsProduct> queryProductByIds(List<Long> ids) {
        return shardingMapper.selectProductByIds(ids);
    }
}

2.6 pojo

package com.test.sharding.domain.pojo;

import lombok.Data;

import java.math.BigInteger;

@Data
public class ScsProduct {
    /**
    * 商品主键id
    */

    private Long productId;

    /**
    * 商品编码
    */

    private String productCode;

    /**
    * 商品名称
    */

    private String productName;

    /**
    * 商品库存
    */

    private Integer productAmount;
}

2.7 controller

@RestController
@RequestMapping("/shardingJdbcController")
@Slf4j
public class ShardingJdbcController {
    @Resource
    private ShardingService shardingService;

    @PostMapping("/insertProduct")
    @CrossOrigin
    public String insertProduct(){
        ScsProduct scsProduct = new ScsProduct();
        for(int i = 1 ; i < 10 ; i++){
            scsProduct.setProductAmount(i);
            scsProduct.setProductCode("SHARDING-JDBC"+i);
            shardingService.insertProduct(scsProduct);
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
        }
        return "插入成功！";
    }
    @PostMapping("/queryProduct")
    @CrossOrigin
    public List<ScsProduct> queryProduct(){
        return shardingService.queryProduct();
    }

    @PostMapping("/queryProductPage")
    @CrossOrigin
    public PageInfo<ScsProduct> queryProductPage(){
        return shardingService.queryProductPage();
    }
}

2.8 多线程配置

@Configuration
@EnableAsync
public class ThreadPoolConfig {

    @Bean(value = "taskThread")
    public ThreadPoolTaskExecutor taskThread(){
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(12);
        executor.setMaxPoolSize(100);
        executor.setQueueCapacity(100);
        executor.setThreadNamePrefix("taskExecutor-product");
//        executor.setKeepAliveSeconds(1000);
        executor.initialize();
        return executor;
    }
}