springboot整合sharding-jdbc数据库分表

做梦i

已于 2023-02-22 17:00:01 修改

阅读量370

点赞数 1

分类专栏：计算机基础文章标签： spring boot 数据库 mybatis java

于 2023-02-22 16:50:21 首次发布

本文链接：https://blog.csdn.net/weixin_43958683/article/details/129165896

版权

计算机基础专栏收录该内容

10 篇文章 0 订阅

订阅专栏

本文通过一个小型的示例展示了如何利用Sharding-JDBC将学生表和订单表进行分库分表操作。学生表按照sid除以4取余的方式分4个表，订单表前1000条数据存入t_order_0，其余存入t_order_1。配置包括POM文件、实体类、Mapper接口以及application.yaml中的Sharding配置。在配置完成后，查询、修改和插入操作会自动处理分表逻辑。

摘要由CSDN通过智能技术生成

前言

当一张表里的数据达到500万（根据每条数据的大小而定，如果每条数据较小时，可考虑达到千万）以上时，我们就要考虑分表的问题了，而sharding-jdbc正是一个应对分库分表的好工具。

正文

接下来以一个小的demo为例，我需要将学生表students分为4个表，分别是students_0，students_1，students_2，students_3，另外我还建一个订单表，并且分为两个表，分别是t_order_0，t_order_1，而且两个的分表规则不同，学生表是通过除以4取余，订单表，我想前1000条放在t_order_0中，其他的放在t_order_1中，接下来，一起看下如何实现。

pom

<?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.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.qfc</groupId>
    <artifactId>sharding</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding</name>
    <description>sharding</description>
    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-core</artifactId>
            <version>2.2.11</version>
        </dependency>
        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
            <version>2.2.11</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.jaxb</groupId>
            <artifactId>jaxb-runtime</artifactId>
            <version>2.2.10-b140310.1920</version>
        </dependency>
        <dependency>
            <groupId>javax.activation</groupId>
            <artifactId>activation</artifactId>
            <version>1.1.1</version>
        </dependency>
    </dependencies>

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

</project>

学生类：Students

@TableName("students")
public class Students {
    @TableId
    private Integer sid;
    @TableField("sname")
    private String sname;
    @TableField("address")
    private String address;

    public Students(Integer sid, String sname, String address) {
        this.sid = sid;
        this.sname = sname;
        this.address = address;
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

学生mapper接口

@Mapper
public interface StudentsMapper extends BaseMapper<Students> {
    @Select("select * from students where sid = #{sid}")
    Students selectStudents(@Param("sid")Integer sid);
}

订单类 Order

package com.qiaofc.sharding.vo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;

@TableName("t_order")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
    @TableId(type = IdType.ASSIGN_ID)
    private Integer orderId;
    private Long userId;
    private Long merchantId;
    private BigDecimal amount;
    private Integer orderStatusId;
}

订单mapper

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

application.yaml

上面的定义都很简单，主要看一下接下来的配置

#mybatis-plus的基本配置
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  config-location: classpath:mybatis-config.xml
#访问端口
server:
  port: 8080
spring:
  shardingsphere:
    datasource:
#name的m1是随便取的，保证与下面保持一致即可
      names: m1
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/qiaofc?useUnicode=true
        username: root
        password: ******
    sharding:
#在这里定义所有要分表的数据库表
      tables:
#配置students表
        students:
#表名从students_0到students_3
          actual-data-nodes: m1.students_$->{0..3}
#主键生成策略
          key-generator:
            column: sid
            type: SNOWFLAKE
#分表规则
          table-strategy:
            inline:
#根据sid分表
              sharding-column: sid
#通过sid除以4取余，余数为几就用哪个表，比如余数为1，就是students_1
              algorithm-expression: students_$->{sid % 4}
#配置t_order表
        t_order:
#包含t_order_0和t_order_1
          actual-data-nodes: m1.t_order_$->{0..1}
          key-generator:
            column: order_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              sharding-column: order_id
#大于1000为1，否则为0
              algorithm-expression: t_order_$->{order_id > 1000?1:0}
    props:
      sql:
#打印sql
        show: true
  main:
#相同bean覆盖，分表时需要配置
    allow-bean-definition-overriding: true