shardingJDBC配置-java多数据源分表

直接上代码

pom文件配置

<!-- jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0</version>
        </dependency>
        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

数据分表配置代码

package com.XXX.config.mybatis;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
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.NoneShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

/**
 * 分表数据源配置
 */
@Configuration
@MapperScan(value = "com.XXX.mapper.fund",sqlSessionFactoryRef = "fundSqlSessionFactory", sqlSessionTemplateRef = "fundSqlSessionTemplate")
@Slf4j
public class FundDbConfig {

    @Value("${shardingJdbc.showSql:false}")
    private Boolean showSql;

//创建连接池
    @Bean(name = "fundDbDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.fund")
    public DataSource fundDbDataSource() {
        DataSource dataSource = new HikariDataSource();
        //DataSourceBuilder.create().type(HikariDataSource.class).build();
        return dataSource;
    }
//事务管理
    @Bean(name = "fundTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("fundDbShardingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "fundSqlSessionFactory")
    public SqlSessionFactory fundSqlSessionFactory(@Qualifier("fundDbShardingDataSource") DataSource fundDbDataSource) throws IOException {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(fundDbDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/fund/*.xml"));
        try {
            return bean.getObject();
        } catch (Exception e) {
            log.error("fundSqlSessionFactory 异常",e);
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "fundSqlSessionTemplate")
    public SqlSessionTemplate fundSqlSessionTemplate(@Qualifier("fundSqlSessionFactory")SqlSessionFactory fundSqlSessionFactory) throws Exception {
        SqlSessionTemplate sqlSessionTemplate=new SqlSessionTemplate(fundSqlSessionFactory);
        return sqlSessionTemplate;
    }

    @Bean(name="fundDbShardingDataSource")
    DataSource getShardingDataSource(@Qualifier("fundDbDataSource") DataSource fundDbDataSource) throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getUserFundTableRuleConfiguration());//表分片规则配置
        shardingRuleConfig.getBindingTableGroups().add("t_user_prop");//绑定表关系
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new NoneShardingStrategyConfiguration());//默认库规则
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new NoneShardingStrategyConfiguration());//默认表规则
        Properties properties = new Properties();
        properties.put("sql.show", showSql);//是否展示执行sql，会详细打印出执行的所有详细的替换后的查询sql
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(fundDbDataSource), shardingRuleConfig, properties);
    }
//分片规则配置
    TableRuleConfiguration getUserFundTableRuleConfiguration () {
        //逻辑表名（映射文件中的表名）+表范围（库名.实际的表范围）
        TableRuleConfiguration result = new TableRuleConfiguration("t_user_prop", "fund_db.t_user_prop_${0..63}");
        result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("UserID","t_user_prop_${UserID%64}"));//表路由规则
        return result;
    }

    Map<String, DataSource> createDataSourceMap(DataSource fundDbDataSource) {
        Map<String, DataSource> result = new HashMap<>();
        result.put("fund_db", fundDbDataSource);
        return result;
    }
}

无数据分表配置

package com.XXX.config.mybatis;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;

/**
 * 业务库配置
 */
@Configuration
@MapperScan(value = "com.XXX.mapper.work",sqlSessionFactoryRef = "workSqlSessionFactory", sqlSessionTemplateRef = "workSqlSessionTemplate")
@Slf4j
public class WorkDbConfig {

    @Bean(name = "workDbDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.work")
    public DataSource workDbDataSource() {
        DataSource dataSource = new HikariDataSource();
        return dataSource;
    }

    @Bean(name = "workTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("workDbDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "workSqlSessionFactory")
    public SqlSessionFactory workSqlSessionFactory(@Qualifier("workDbDataSource") DataSource workDbDataSource) throws IOException {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(workDbDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/work/*.xml"));
        try {
            return bean.getObject();
        } catch (Exception e) {
            log.error("qptreasureDbSqlSessionFactory 异常",e);
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "workSqlSessionTemplate")
    public SqlSessionTemplate workSqlSessionTemplate(@Qualifier("workSqlSessionFactory")SqlSessionFactory workSqlSessionFactory) throws Exception {
        SqlSessionTemplate sqlSessionTemplate=new SqlSessionTemplate(workSqlSessionFactory);
        return sqlSessionTemplate;
    }
}