SpringBoot+Sharding-jdbc+mybatis-plus实现水平分表

 一：先准备数据库表

CREATE TABLE car_info_1(
    id BIGINT PRIMARY KEY COMMENT '主键',
    brand VARCHAR(50) DEFAULT '' COMMENT '汽车品牌',
    model VARCHAR(50) DEFAULT '' COMMENT '汽车型号',
    production_year INT(4) DEFAULT NULL COMMENT '生产年份',
    price DECIMAL(10, 2) DEFAULT NULL COMMENT '汽车价格'
) ENGINE = Innodb DEFAULT CHARSET = utf8 COMMENT '汽车信息表1';

CREATE TABLE car_info_2(
    id BIGINT PRIMARY KEY COMMENT '主键',
    brand VARCHAR(50) DEFAULT '' COMMENT '汽车品牌',
    model VARCHAR(50) DEFAULT '' COMMENT '汽车型号',
    production_year INT(4) DEFAULT NULL COMMENT '生产年份',
    price DECIMAL(10, 2) DEFAULT NULL COMMENT '汽车价格'
) ENGINE = Innodb DEFAULT CHARSET = utf8 COMMENT '汽车信息表2';

CREATE TABLE products_1(
    id BIGINT PRIMARY KEY COMMENT '主键',
    name VARCHAR(50) DEFAULT '' COMMENT '商品名称',
    price DECIMAL(10, 2) DEFAULT NULL COMMENT '商品价格',
    description VARCHAR(100) DEFAULT '' COMMENT '商品描述',
    category VARCHAR(20) DEFAULT '' COMMENT '商品类别'
) ENGINE = Innodb DEFAULT CHARSET = utf8 COMMENT '商品信息表1';

CREATE TABLE products_2(
    id BIGINT PRIMARY KEY COMMENT '主键',
    name VARCHAR(50) DEFAULT '' COMMENT '商品名称',
    price DECIMAL(10, 2) DEFAULT NULL COMMENT '商品价格',
    description VARCHAR(100) DEFAULT '' COMMENT '商品描述',
    category VARCHAR(20) DEFAULT '' COMMENT '商品类别'
) ENGINE = Innodb DEFAULT CHARSET = utf8 COMMENT '商品信息表2';

二：创建项目并编写相关代码

1: 创建springboot项目，引入相应依赖

<?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.14</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xyz</groupId>
    <artifactId>springboot-mp-fk-fb</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-mp-fk-fb</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>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.11</version>
        </dependency>

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

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.20</version>
        </dependency>

        <!--分库分表-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

2: 编写配置文件

# 服务端口号
server.port=9999

# Mybatis-plus相关的配置
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#---------------------------------水平分表的配置---------------------------------
# 配置数据源别名
spring.shardingsphere.datasource.names=m1

# 配置具体的数据源(连接池，驱动，数据库地址，用户名，密码)
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/xxxx?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=xxxx
spring.shardingsphere.datasource.m1.password=xxxx

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true
# 配置sharding-jdbc分片策略
# 指定数据库car_info表的主键以及主键的生成策略
spring.shardingsphere.sharding.tables.car_info.key-generator.column=id
spring.shardingsphere.sharding.tables.car_info.key-generator.type=SNOWFLAKE
# 指定数据库products表的主键以及主键的生成策略
spring.shardingsphere.sharding.tables.products.key-generator.column=id
spring.shardingsphere.sharding.tables.products.key-generator.type=SNOWFLAKE

# 指定car_info表分布情况，配置表在那个数据库里面，表名称是什么  m1.car_info_1, m1.car_info_2
spring.shardingsphere.sharding.tables.car_info.actual-data-nodes=m1.car_info_$->{1..2}
# 指定products表分布情况
spring.shardingsphere.sharding.tables.products.actual-data-nodes=m1.products_$->{1..2}

# 指定表的分片策略：约束主键id为偶数时添加到car_info_1表中，主键id为奇数时添加到car_info_2表中
spring.shardingsphere.sharding.tables.car_info.table-strategy.inline.sharding-column=id
# 选择数据库表的规则
spring.shardingsphere.sharding.tables.car_info.table-strategy.inline.algorithm-expression=car_info_$->{id % 2 + 1}
# 指定表的分片策略：约束主键id为偶数时添加到products_1表中，主键id为奇数时添加到products_2表中
spring.shardingsphere.sharding.tables.products.table-strategy.inline.sharding-column=id
# 选择数据库表的规则
spring.shardingsphere.sharding.tables.products.table-strategy.inline.algorithm-expression=products_$->{id % 2 + 1}

# 打开SQL输出日志
spring.shardingsphere.props.sql.show=true

3: 编写相关业务代码

@Data
public class CarInfo {

    private Long id;

    /**
     * 汽车品牌
     */
    private String brand;

    /**
     * 汽车型号
     */
    private String model;

    /**
     * 生产年份
     */
    private Integer productionYear;

    /**
     * 汽车价格
     */
    private BigDecimal price;

}
@Data
public class Products {

    private Long id;

    /**
     * 商品名称
     */
    private String name;

    /**
     * 商品价格
     */
    private BigDecimal price;

    /**
     * 商品描述
     */
    private String description;

    /**
     * 商品类别
     */
    private String category;

}

@Mapper
public interface CarInfoMapper extends BaseMapper<CarInfo> {
}
@Mapper
public interface ProductsMapper extends BaseMapper<Products> {
}

@RestController
@RequestMapping(value = "/test")
public class TestController {


    @Resource
    private CarInfoMapper carInfoMapper;

    @Resource
    private ProductsMapper productsMapper;


    /**
     * 同一个数据库-水平分表添加数据
     * @author
     * @date 2023/8/18 00:37:20
     */
    @PostMapping(value = "/saveCarInfo")
    public String saveCarInfo(@RequestBody CarInfo carInfo) {
        carInfoMapper.insert(carInfo);
        return "汽车信息添加成功";
    }


    /**
     * 同一个数据库-水平分表添加数据
     * @author
     * @date 2023/8/18 00:37:20
     */
    @PostMapping(value = "/saveProducts")
    public String saveProducts(@RequestBody Products products) {
        productsMapper.insert(products);
        return "商品信息添加成功";
    }


    /**
     * 同一个数据库-水平分表更新数据
     * @author
     * @date 2023/8/18 00:37:20
     */
    @PostMapping(value = "/updateCarInfo")
    public String updateCarInfo(@RequestBody CarInfo carInfo) {
        carInfoMapper.updateById(carInfo);
        return "汽车信息更新成功";
    }


    /**
     * 同一个数据库-水平分表查询数据
     * @author
     * @date 2023/8/18 00:37:20
     */
    @GetMapping(value = "/selectCarInfo")
    public List<CarInfo> selectCarInfo() {
        QueryWrapper<CarInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByAsc("id");
        return carInfoMapper.selectList(queryWrapper);
    }


    /**
     * 同一个数据库-水平分表删除数据
     * @author
     * @date 2023/8/18 00:37:20
     */
    @DeleteMapping(value = "/deleteCarInfo")
    public String deleteCarInfo(@RequestParam Long id) {
        carInfoMapper.deleteById(id);
        return "汽车信息删除成功";
    }

4: 测试相关接口

这块我就不演示了文章来源地址https://www.toymoban.com/news/detail-656602.html

到了这里，关于SpringBoot+Sharding-jdbc+mybatis-plus实现水平分表的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！