SpringBoot: spring boot+MyBatis+ShardingSphere实现分表分库

一。准备3个数据库(ds0,ds1,ds2)，其中ds0和ds1用来存储分表分库的数据，ds2用来存储不进行分表分库的数据.在ds0和ds1数据库分别建立3张数据表

create table tab_user2(id int  auto_increment primary key,name varchar(5),gender varchar(5),age int);
create table tab_user1(id int  auto_increment primary key,name varchar(5),gender varchar(5),age int);
create table tab_user0(id int  auto_increment primary key,name varchar(5),gender varchar(5),age int);

将根据tab_user表的age列进行分库，根据tab_user表的id列进行分表
二、创建spring boot项目，加入shardingsphere相关依赖，完整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>cn.edu.tju</groupId>
<artifactId>springbootmybatisshardingsphere2022</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.0.RELEASE</version>
</parent>

<dependencies>

    <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-dependencies -->

    <!--
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-dependencies</artifactId>
        <version>Hoxton.SR12</version>
        <type>pom</type>
        <scope>runtime</scope>
    </dependency>

    -->

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


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

    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.7</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>3.8.1</version>
        <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-netflix-eureka-client -->

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

    <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson -->
    <dependency>
        <groupId>com.google.code.gson</groupId>
        <artifactId>gson</artifactId>
        <version>2.8.6</version>
    </dependency>

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.1.1</version>
    </dependency>

    <dependency>
        <groupId>com.dangdang</groupId>
        <artifactId>sharding-jdbc-config-spring</artifactId>
        <version>1.5.4.1</version>
    </dependency>


    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
        <version>2.3.0.RELEASE</version>
    </dependency>

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


    <!--mysql驱动包-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.35</version>
    </dependency>

    <dependency>
        <groupId>com.graphql-java</groupId>
        <artifactId>graphql-java</artifactId>
        <version>16.2</version>
    </dependency>

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


    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.1.1</version>
    </dependency>


</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
        <!-- 跳过单元测试 -->
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-surefire-plugin</artifactId>
            <configuration>
                <skipTests>true</skipTests>
            </configuration>
        </plugin>
    </plugins>
</build>


</project>

三、在application.properties中配置3个数据库，和步骤一中提到的3个数据库对应

server.port=8089
spring.datasource.master.jdbc-url=jdbc:mysql://139.xxx.xxx.xxx:3307/ds0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.datasource.master.username=root
spring.datasource.master.password=xxxx
spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.slave1.jdbc-url=jdbc:mysql://139.xxx.xxx.xxx:3307/ds1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.datasource.slave1.username=root
spring.datasource.slave1.password=xxxx
spring.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.slave2.jdbc-url=jdbc:mysql://139.xxx.xxx.xxx:3307/ds2?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.datasource.slave2.username=root
spring.datasource.slave2.password=xxxx
spring.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver

四、创建分表策略类，对列的值对3求模，得到的值作为表的序号，也就是数据要写到第几个表：

package cn.edu.tju.config;


import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Service;

import java.util.Collection;

//分表策略，精确分表，用对3求模的策略
@Service
public final class ShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Long> shardingValue) {
        //System.out.println("hello,sharding......");
        for (String each : tableNames) {
            //System.out.println(shardingValue.getValue());
            if (each.endsWith(shardingValue.getValue() % 3 + "")) {
                return each;
            }
        }


        throw new UnsupportedOperationException();
    }
}

五、定义配置类，主要是最终构建出SqlSessionFactory,具体见注释：

package cn.edu.tju.config;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
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.shardingjdbc.api.ShardingDataSourceFactory;


@Configuration
@ComponentScan(basePackageClasses = MySQLConfig.class)
//扫描cn.edu.tju.mapper下的MyBatis mapper类文件
@MapperScan(basePackages = "cn.edu.tju.mapper")
public class MySQLConfig {

    //从application.properties中读取配置，构建名为getMasterDataSource的bean
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource getMasterDataSource() {
        return DataSourceBuilder.create().build();
    }

    //从application.properties中读取配置，构建名为getSlave1DataSource的bean
    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource getSlave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    //从application.properties中读取配置，构建名为getSlave2DataSource的bean
    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource getSlave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    //构建名为dataSourceMap的bean
    @Bean("dataSourceMap")
    Map<String, DataSource> buildDataSourceMap(@Qualifier("getMasterDataSource") DataSource masterDataSource,
                                               @Qualifier("getSlave1DataSource") DataSource slave1DataSource)  {
        Map<String, DataSource> result = new HashMap<>();

        result.put("ds0", masterDataSource);
        result.put("ds1", slave1DataSource);
        result.put("ds2", getSlave2DataSource());
        return result;
    }

    //主键生成器，雪花算法,如果不需要自动生成id,可不用定义
    private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
        KeyGeneratorConfiguration keyGeneratorConfiguration = new KeyGeneratorConfiguration("SNOWFLAKE", "id");
        return keyGeneratorConfiguration;
    }


    //user 表的逻辑表与物理表映射关系配置
    TableRuleConfiguration getUserTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("tab_user", "ds${0..1}.tab_user${0..2}");
        result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
        return result;
    }



    //构建名为finalDataSource的bean,作为最终的数据源传给SqlSessionFactory
    @Bean("finalDataSource")
    public DataSource dataSource(@Qualifier("dataSourceMap") Map<String, DataSource> finalDataSource) throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());

        //将tab_user表加入分表分库的逻辑中
        shardingRuleConfig.getBindingTableGroups().add("tab_user");

      

        //配置不分表分库的数据库,不分表分库的数据将写入这个库
        shardingRuleConfig.setDefaultDataSourceName("ds2");

        //配置分库策略,根据age分库，对2取模
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("age", "ds${age % 2}"));

        //配置分表策略,根据id进行分表，策略在ShardingAlgorithm类中进行了定义
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id", new ShardingAlgorithm()));

        return ShardingDataSourceFactory.createDataSource(finalDataSource, shardingRuleConfig, new Properties());
    }




    //注入数据源，创建SqlSessionFactory
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("finalDataSource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setFailFast(true);
        return sessionFactory.getObject();
    }



}

六、定义实体类、mapper、controller、主类

package cn.edu.tju.domain;


import java.util.Date;

public class User {

    private Long id;
    private String name;
    private String gender;
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public User(Long id,String name, String gender, Integer age) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.age = age;
    }

}

package cn.edu.tju.mapper;

import cn.edu.tju.domain.People;
import cn.edu.tju.domain.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserMapper {

    @Insert("<script>" +
            "insert into tab_user values " +
            "<foreach collection='userList' item='item' separator=','>" +
            "(#{item.id},#{item.name},#{item.gender},#{item.age})" +
            "</foreach>" +
            "</script>")
    int batchAddUser(@Param("userList") List<User> userList);

    @Select("select count(*) from tab_user")
    int getUserCount();


}

package cn.edu.tju.controller;

import cn.edu.tju.domain.People;
import cn.edu.tju.domain.User;
import cn.edu.tju.mapper.UserMapper;
import com.google.common.collect.Lists;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;
    @RequestMapping("/addUserList")
    public int addUserList(){
        List<User> userList = Lists.newArrayList();

        userList.add(new User(51L,"ser11", "女", 15));
        userList.add(new User(52L,"ser12", "男", 34));
        userList.add(new User(53L,"ser13", "女", 72));
        userList.add(new User(54L,"ser14", "男", 94));
        userList.add(new User(55L,"ser15", "女", 32));
        userList.add(new User(56L,"ser16", "女", 81));
        userMapper.batchAddUser(userList);

        return 1;
    }

    @RequestMapping("/getUserCount")
    public int getUserCount(){
        int result=userMapper.getUserCount();
        return result;
    }


}

package cn.edu.tju;

import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication(exclude = SpringBootConfiguration.class)
@MapperScan("cn.edu.tju.mapper")
public class Start {
    public static void main(String[] args) {
        SpringApplication.run(Start.class,args);
    }
}

七、启动程序，访问/addUserList 和 /getUserCount接口，
可以看到数据写如数据库

和查询数据总量