SpringBoot +MyBatis + Sharding-jdbc单建单库分表的操作

SpringBoot +MyBatis + Sharding-jdbc单建单库分表的操作

项目在启动的过程中，报出了数据源循环依赖，解决方案需要排除数据源本身的自动配置：@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})

第一步：新建一个项目 singledata-mutipletable-sharding-jdbc:可以借鉴这一篇

第二步：导入依赖：如下所示：

<?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>davidtao</groupId>
    <artifactId>singledata-mutipletable-sharding-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

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


    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outEncoding>UTF-8</project.reporting.outEncoding>
        <java.verison>1.8</java.verison>
        <aop.version>1.5.14.RELEASE</aop.version>
    </properties>

    <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>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.6.1</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.6.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.6</version>
        </dependency>
        <!--yinrushading-jdbc-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.0.0</version>
        </dependency>
       
    </dependencies>

    <build>
        <!--build 的目标，默认为 install-->
        <defaultGoal>compile</defaultGoal>
        <!--directory：build 目标文件的存放目录，默认在 ${basedir}/target 目录-->
        <directory>${project.basedir}/target</directory>
        <!--finalName：build 目标文件的文件名，默认情况下为 ${artifactId}-${version}-->
        <finalName>${project.artifactId}</finalName>

        <plugins>
            <!--注释该插件，mvn package 时不会进行 repackage 生成可以直接运行的 jar-->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <!--热部署需要开启该选项-->
                <configuration>
                    <fork>true</fork>
                </configuration>
            </plugin>

            <!--maven-surefire-plugin 是 maven 里执行测试用例的插件，不显式配置就会用默认配置-->
            <!--一般我们执行 maven 打包命令 mvn package 前 maven 会默认执行 mvn test 命令.-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <!--在打包时会跳过单元测试-->
                    <skip>true</skip>
                    <!--<skipTests>true</skipTests>-->
                    <!--测试失败时忽略，不停止打包-->
                    <!--<testFailureIgnore>true</testFailureIgnore>-->
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

第三步：进行数据源的配置(里面包含了sharding的配置)

package david.soft.com.config;

import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import com.baomidou.mybatisplus.extension.plugins.SqlExplainInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.zaxxer.hikari.HikariDataSource;
import david.soft.com.algorithm.ModuloTableShardingAlgorithm;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

@Configuration
public class DataSourceConfig {

    @Autowired
    private Environment environment;

    private String url;

    private String username;

    private  String password;

    private String driverClassName;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    /**
     *
     * peizhishujuyuan
     */
    @Bean(value="sharding")
    public DataSource getDataSource(){
        HikariDataSource hikariDataSource = DataSourceBuilder
                .create()
                .url(url)
                .driverClassName(driverClassName)
                .username(username)
                .password(password)
                .type(HikariDataSource.class)
                .build();
        return hikariDataSource;
    }

    /**
     * 配置数据源规则，即将单个数据源交给sharding-jdbc管理，并且可以设置默认的数据源，
     * 当表没有配置分库规则时会使用默认的数据源
     * @param dataSource0
     * @return
     */
    @Bean
    public DataSourceRule dataSourceRule(@Qualifier("sharding") DataSource dataSource0){
        Map<String, DataSource> dataSourceMap = new HashMap<>(); //设置分库映射
        dataSourceMap.put("dataSource0", dataSource0);
        //设置默认库，两个库以上时必须设置默认库。默认库的数据源名称必须是dataSourceMap的key之一
        //这里我修改成了一个库了，我准备的是单库分表
        return new DataSourceRule(dataSourceMap,"dataSource0");
    }

    /**
     * 配置数据源策略和表策略，具体策略需要自己实现
     * @param dataSourceRule
     * @return
     */
    @Bean
    public ShardingRule shardingRule(DataSourceRule dataSourceRule){
        //具体分库分表策略
        TableRule orderTableRule = TableRule.builder("t_order")
                .actualTables(Arrays.asList("t_order_0", "t_order_1"))
                .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
                .dataSourceRule(dataSourceRule)
                .build();

        //绑定表策略，在查询时会使用主表策略计算路由的数据源，因此需要约定绑定表策略的表的规则需要一致，可以一定程度提高效率
        List<BindingTableRule> bindingTableRules = new ArrayList<BindingTableRule>();
        bindingTableRules.add(new BindingTableRule(Arrays.asList(orderTableRule)));
        return ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(orderTableRule))
                .bindingTableRules(bindingTableRules)
                .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
                .build();
    }

    /**
     * 创建sharding-jdbc的数据源DataSource，MybatisAutoConfiguration会使用此数据源
     * @param shardingRule
     * @return
     * @throws SQLException
     */
    @Bean(name="dataSource")
    public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException {
        return ShardingDataSourceFactory.createDataSource(shardingRule);
    }

    /**
     * 需要手动配置事务管理器
     * @param dataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "shardingSqlSessionFactory")
    @Primary
    public SqlSessionFactory shardingSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/*.xml"));
        bean.setPlugins(new Interceptor[]{
                //这个插件干啥用的，注册分页插件
                new PaginationInterceptor(),
                 //这个插件干啥用的，注册性能分析插件
                new PerformanceInterceptor(),
                 //这个插件干啥用的，注册SQL 执行分析拦截器
                new SqlExplainInterceptor()
        });
        //这里一般设置包的实体类的位置 <property name="typeAliasesPackage" value="com.xiaomu.**.model"/>
        bean.setTypeAliasesPackage("david.soft.com");
        GlobalConfig globalConfig = new GlobalConfig();
        //LogicSqlInjector -> 逻辑sql处理器, (逻辑删除)
        globalConfig.setSqlInjector(new LogicSqlInjector());
        //全局配置
        globalConfig.setDbConfig(new GlobalConfig.DbConfig());
        bean.setGlobalConfig(globalConfig);
        return bean.getObject();
    }

    @Bean(name = "shardingSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate shardingSqlSessionTemplate(@Qualifier("shardingSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }  
}

第四步：最后的controller层，service层，mapper层，算法的编写，看我的github代码连接:需要如果需要密码，请留言。