最简单易懂的Spring boot，mybatis，sharding jdbc 分库分表教程

最新推荐文章于 2024-04-10 17:27:44 发布

倚楼听风夜听雨

最新推荐文章于 2024-04-10 17:27:44 发布

阅读量373

点赞数 1

分类专栏： java 分布式 Spring 文章标签：分布式数据库

本文链接：https://blog.csdn.net/qq_28163609/article/details/108316830

版权

java 同时被 3 个专栏收录

14 篇文章 0 订阅

订阅专栏

Spring

10 篇文章 0 订阅

订阅专栏

分布式

1 篇文章 0 订阅

订阅专栏

网上的基于配置文件的文章太多了且有些写的乱七八糟对新手极为不友好，本篇文章采用全部 Java 编码的方式，来实现数据的分库分表，并力求做到简单明了

工具准备

安装Spring boot，我这里是通过idea安装， java8，maven
这里采用mybatis逆向工程生成代码，生成插件选择Maven
连接池采用阿里巴巴的Druid

安装依赖

pom文件如下，主要依赖有，mybatis-spring-boot-starter，druid-spring-boot-starter，sharding-jdbc-core

<?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.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>


        <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>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-core -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.1</version>
        </dependency>

    </dependencies>

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

            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <configuration>
                    <overwrite>true</overwrite>
                </configuration>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.4.0</version>
            </plugin>
        </plugins>
    </build>

</project>

生成Model

具体教程在这篇文章有详细讲解 https://blog.csdn.net/qq_28163609/article/details/108235740

注意：记得最后在Mapper文件上加上@Mapper注解

配置数据源和定义分库分表策略

package com.example.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
public class DataSourceConfig {

    /**
     * 配置数据源
     * @return
     * @throws SQLException
     */
    @Bean
    public DataSource getDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 添加一张表的规则，如有多张表需添加多个，我们这里只有一张表
        shardingRuleConfig.getTableRuleConfigs().add(getStudentTableRuleConfiguration());
        // 定义分库策略，这里采用的是表达式的写法，意思为，根据user_id 分库，当sql语句中带有user_id字段的时候，会采用user_id 对2取模的算法得出结果，然后test拼接上运算后的结果，这里的结果也就是test0或者test1
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "test${user_id % 2}"));
        // 定义分表策略，为了演示更多的写法，这里采用另一种策略，更为灵活，这里也是根据user_id分表，具体策略是ModuloShardingTableAlgorithm实现的
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new ModuloShardingTableAlgorithm()));
        // 生成Sharding jdbc的数据源
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new Properties());
    }

    /**
     * 这里写每个数据库中真实存在的节点，这里的意思是有test0和test1库，每个库里存在3张student表
     * @return
     */
    TableRuleConfiguration getStudentTableRuleConfiguration() {
        return new TableRuleConfiguration("student", "test${0..1}.student${0..3}");
    }

    /**
     * 自定义的分表策略，preciseShardingValue就是分库分表字段的值，这里也就是user_id的值
     */
    static class ModuloShardingTableAlgorithm implements PreciseShardingAlgorithm<Integer>{
        @Override
        public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
            System.out.println(preciseShardingValue.getValue());
            // 返回表名
            return "student_0"+preciseShardingValue.getValue() % 3;
        }
    }

    /**
     * 配置Druid 数据源0
     * @return
     */
    private DataSource getDruidDataSource0(){
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test0?useSSL=false");
        return  dataSource;
    }

    /**
     * 配置Druid 数据源1
     * @return
     */
    private DataSource getDruidDataSource1(){
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test1?useSSL=false");
        return  dataSource;
    }

    /**
     * 组成一个Map
     * @return
     */
    Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        result.put("test0", getDruidDataSource0());
        result.put("test1", getDruidDataSource1());
        return result;
    }

}

代码逻辑已经详细写了注释

插入数据测试

package com.example.demo.controller;

import com.example.demo.mapper.StudentMapper;
import com.example.demo.model.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class TestController {

    @Autowired
    StudentMapper studentMapper;

    @RequestMapping("/insert")
    public String insert(){

        for(int i = 1; i <= 100; i++){
            Student student = new Student();
            student.setName("占三");
            student.setUserId(i);
            studentMapper.insert(student);
        }
        return "success";
    }
}

效果：test0库的student_00表，譬如user_id 为6 的对2（数据库的数量）取模为0，对3（表的数量）取模为0

student_01的数据如下：和预想中一样

至此完成，分库分表，利用Sharding-jdbc分库分表还是很容易实现的，且对代码的侵入性较小。

数据库结构为，建立两个数据库名字为test0,test1

以下是表结构，分别在两个数据库中执行即可

CREATE TABLE student_01 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL DEFAULT ‘’,
user_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

CREATE TABLE student_02 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL DEFAULT ‘’,
user_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

CREATE TABLE student_03 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL DEFAULT ‘’,
user_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

源码地址：
https://github.com/Rentu/sharding-jdbc-demo

倚楼听风夜听雨

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
最简单易懂的Spring boot，mybatis，sharding jdbc 分库分表教程

网上的基于配置文件的文章太多了且有些写的乱七八糟对新手极为不友好，本篇文章采用全部 Java 编码的方式，来实现数据的分库分表，并力求做到简单明了工具准备安装Spring boot，我这里是通过idea安装， java8，maven这里采用mybatis逆向工程生成代码，生成插件选择Maven连接池采用阿里巴巴的Druid安装依赖pom文件如下，主要依赖有，mybatis-spring-boot-starter，druid-spring-boot-starter，sharding-jdbc
复制链接

扫一扫

专栏目录