SpringBoot集成sharding-jdbc实现分库分表

最新推荐文章于 2024-08-07 17:17:53 发布

益朋

最新推荐文章于 2024-08-07 17:17:53 发布

阅读量643

点赞数 1

分类专栏： Spring MySQL 文章标签： mysql spring spring boot

本文链接：https://blog.csdn.net/longziling/article/details/118330450

版权

Spring 同时被 2 个专栏收录

6 篇文章 1 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

问题：

给出简单的分库分表的示例。

问题分析：

当前市面上至少有MyCat与sharding两种方式，实现分库分表。

MyCat属于中间件，需要另行下载。

sharding-jdbc-spring-boot-starter只需导入对应的jar即可。

这里使用sharding-jdbc-spring-boot-starter给出一各最简单的样例。（亲测有效哦）

直接上代码：

工程项目文件

maven

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.yipeng.goodlife</groupId>
    <artifactId>goodlife</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>cloud-transfer-7001</module>
        <module>cloud-check-7011</module>
        <module>cloud-shard-7021</module>
    </modules>

    <!--统一管理jar包和版本-->
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <junit.version>4.12</junit.version>
        <log4j.version>1.2.17</log4j.version>
        <lombok.version>1.16.18</lombok.version>
        <mysql.version>8.0.18</mysql.version>
        <druid.verison>1.2.6</druid.verison>
        <mybatis.spring.boot.verison>2.1.3</mybatis.spring.boot.verison>
        <mybatis.plus.boot.verison>3.0.3</mybatis.plus.boot.verison>
        <sharding.jdbc.spring.boot.verison>4.1.1</sharding.jdbc.spring.boot.verison>
    </properties>

    <dependencyManagement>
        <dependencies>
            <!--spring boot 2.5.0-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>2.5.0</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <!--spring cloud 2020.0.x aka Ilford-->
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>2020.0.x aka Ilford</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <!--spring cloud alibaba 2.2.0.RELEASE-->
            <dependency>
                <groupId>com.alibaba.cloud</groupId>
                <artifactId>spring-cloud-alibaba-dependencies</artifactId>
                <version>2.2.0.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <!-- MySql -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>
            <!-- Druid -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>${druid.verison}</version>
            </dependency>
            <!-- mybatis-springboot整合 -->
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>${mybatis.spring.boot.verison}</version>
            </dependency>
            <!-- sharding-jdbc整合 -->
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>${sharding.jdbc.spring.boot.verison}</version>
            </dependency>
            <!-- mybatis-plus整合 -->
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>${mybatis.plus.boot.verison}</version>
            </dependency>
            <!--lombok-->
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>${lombok.version}</version>
            </dependency>
            <!--junit-->
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>${junit.version}</version>
            </dependency>
            <!-- log4j -->
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>${log4j.version}</version>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <fork>true</fork>
                    <addResources>true</addResources>
                </configuration>
            </plugin>
        </plugins>
    </build>



</project>

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>goodlife</artifactId>
        <groupId>com.yipeng.goodlife</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>cloud-shard-7021</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</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-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

yaml

server:
  port: 7021

spring:
  application:
    name: cloud-shard-7021
  shardingsphere:
    datasource:
      #数据源名称，多数据源以逗号分隔
      names: ds0,ds1,ds2,ds3
      ds0:
        url: jdbc:mysql://localhost:3306/ds0?useSSl=ture&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
      ds1:
        url: jdbc:mysql://localhost:3306/ds1?useSSl=ture&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
      ds2:
        url: jdbc:mysql://localhost:3306/ds2?useSSl=ture&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
      ds3:
        url: jdbc:mysql://localhost:3306/ds3?useSSl=ture&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
    sharding:
      #未配置分片规则的表将通过默认数据源定位-适用于单库单表，该表无需配置分片规则
      default-data-source-name: ds0
      tables:
        t_order:
          # 分库键位user_id,每个库分为四库，所以取模4
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 4}
              sharding-column: user_id
          # 分表 分片键位order_id,每个库分为两表，所以取模2
          table-strategy:
            inline:
              algorithm-expression: t_order_000$->{order_id % 2}
              sharding-column: order_id
          actual-data-nodes: ds$->{0..3}.t_order_000$->{0..1}
    #是否开启SQL显示，默认值: false
    props:
      sql:
        show: true

mybatis:
  type-aliases-package: com.yipeng.goodlife.shard.entity
  mapper-locations: classpath:mybatis/mapper/*.xml
  configuration:
    mapUnderscoreToCamelCase: true

OrderController

package com.yipeng.goodlife.shard.controller;

import com.yipeng.goodlife.shard.dao.OrderDao;
import com.yipeng.goodlife.shard.entity.Order;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import lombok.extern.slf4j.Slf4j;

/**
 * @Author: yipeng
 * @Date: 2021/6/28 23:11
 */
@Slf4j
@RestController
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderDao orderDao;

    @GetMapping("/insert")
    public String insert() {
        for (int i = 0; i < 1000; i++) {
            long userId = i;
            long orderId = i + 1;
            Order order = new Order();
            order.setUserName("益朋");
            order.setUserId(userId);
            order.setOrderId(orderId);
            orderDao.addOrder(order);
        }
        return null;
    }
}

OrderConfigDao

package com.yipeng.goodlife.shard.dao;

import com.yipeng.goodlife.shard.entity.OrderConfig;

import java.util.List;

import org.springframework.stereotype.Repository;

/**
 * @Author: yipeng
 * @Date: 2021/6/28 21:19
 */
@Repository
public interface OrderConfigDao {
    
    List<OrderConfig> getOrderConfig();
    
}

OrderDao

package com.yipeng.goodlife.shard.dao;

import com.yipeng.goodlife.shard.entity.Order;

import java.util.List;

import org.springframework.stereotype.Repository;

/**
 * @Author: yipeng
 * @Date: 2021/6/28 21:19
 */
@Repository
public interface OrderDao {

    List<Order> getOrders(Order order);

    int addOrder(Order orderInfo);

}

Order

package com.yipeng.goodlife.shard.entity;

import java.io.Serializable;

import lombok.Data;

/**
 * @Author: yipeng
 * @Date: 2021/6/28 21:18
 */
@Data
public class Order implements Serializable {

    private static final long serialVersionUID = -466155134525695630L;
    
    private String id;
    
    private Long userId;
    
    private Long orderId;
    
    private String userName;

}

OrderConfig

package com.yipeng.goodlife.shard.entity;

import java.io.Serializable;

import lombok.Data;

/**
 * @Author: yipeng
 * @Date: 2021/6/28 21:18
 */
@Data
public class OrderConfig implements Serializable {

    private static final long serialVersionUID = -2536764091013758889L;

    private Integer id;

    private Integer payTimeout;

}

ShardingSphereApplication

package com.yipeng.goodlife.shard;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @Author: yipeng
 * @Date: 2021/6/28 21:24
 */

@MapperScan("com.yipeng.goodlife.shard.dao")
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
public class ShardingSphereApplication {

    public static void main(String[] args) {

        SpringApplication.run(ShardingSphereApplication.class, args);
    }

}

OrderConfigMapper.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.yipeng.goodlife.shard.dao.OrderConfigDao">


    <select id="getOrderConfig" parameterType="Integer" resultType="OrderConfig">
        SELECT * FROM order_config
    </select>

</mapper>

OrderMapper.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.yipeng.goodlife.shard.dao.OrderDao">


    <select id="getOrders" parameterType="Order"
            resultType="Order">
        select
            t.id ,
            t.user_id ,
            t.order_id ,
            t.user_name
        from t_order t
        where t.user_id=#{userId}
	</select>

    <insert id="addOrder" parameterType="Order" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO t_order (
            user_id,user_name,order_id
        ) VALUES (
            #{userId},#{userName},#{orderId}
        )
    </insert>

</mapper>

sql

CREATE DATABASE IF NOT EXISTS ds0;
CREATE DATABASE IF NOT EXISTS ds1;
CREATE DATABASE IF NOT EXISTS ds2;
CREATE DATABASE IF NOT EXISTS ds3;
USE ds0;
DROP TABLE IF EXISTS `t_order_0000`;
CREATE TABLE `t_order_0000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t_order_0001`;
CREATE TABLE `t_order_0001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `order_config`;
CREATE TABLE `order_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `pay_timeout` int(11) DEFAULT NULL COMMENT '支付超时时间;单位：分钟',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单配置表';

USE ds1;
DROP TABLE IF EXISTS `t_order_0000`;
CREATE TABLE `t_order_0000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t_order_0001`;
CREATE TABLE `t_order_0001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

USE ds2;
DROP TABLE IF EXISTS `t_order_0000`;
CREATE TABLE `t_order_0000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t_order_0001`;
CREATE TABLE `t_order_0001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

USE ds3;
DROP TABLE IF EXISTS `t_order_0000`;
CREATE TABLE `t_order_0000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t_order_0001`;
CREATE TABLE `t_order_0001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,
`user_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

数据样例

测试一下：

服务启动

执行过程

数据库结果

遇到的坑：

1. 启动类上的必须加上

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})

否则，程序在启动前，会报错找不到合适的数据源

2. 添加监控会报如下错误（不添加没问题）

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

原因由于sharding不支持Javax.sql，网上说要改sharding部分源码，具体是另一个课题了。

总结：

总体感觉会使得sql执行速度变慢，但完成了分库分表的功能。

益朋

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录