分库分表之动态数据源（springboot+mybatis+sharding-jdbc）

MAVEN依赖 

<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0</version>
</dependency>

 

数据源切换工具类

public class DatabaseContextHolder {
	
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    
    public static void setDatabaseType(String databaseType){
    	contextHolder.set(databaseType);
    }
    
    public static String getDatabaseType(){
    	return contextHolder.get();
    }
    
    public static void clearDataSource() {
    	contextHolder.remove();
    }
    
}

 

动态数据源类

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		return DatabaseContextHolder.getDatabaseType();
	}

}

枚举

public enum DatabaseType {
	
	SHARDING_DATA_SOURCE("shardingDataSource","sharding-jdbc数据源"),
        DRUID_DATA_SOURCE("druidDataSource","druid数据源");
	
	private String source;
	
	private String desc;
	
	DatabaseType(String source,String desc){
		this.source=source;
		this.desc=desc;
	}

	public String getSource() {
		return source;
	}

	public void setSource(String source) {
		this.source = source;
	}

	public String getDesc() {
		return desc;
	}

	public void setDesc(String desc) {
		this.desc = desc;
	}


}

切换数据源的注解 

@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UseDataSource {
	DatabaseType source();
}

切面处理数据源切换的操作 

@Component
@Aspect
@Order(Ordered.LOWEST_PRECEDENCE-1)
public class UseDataSourceAspect {
	
    @Pointcut("@annotation(UseDataSource)")
    public void useDataSource() {
    } 
    
    @Around("useDataSource() && @annotation(anno)")
    public Object dataSourceSwitcher(ProceedingJoinPoint joinPoint, UseDataSource anno) throws Throwable {
        DatabaseContextHolder.setDatabaseType(anno.source().getSource());
        try {
            //执行方法
            Object result = joinPoint.proceed();
            return result;
        }catch (Exception e){
            throw e;
        }finally {
            //切换回原来的数据源（重要）  
        	DatabaseContextHolder.clearDataSource();
        }
    }

}

 

配置类 

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.plugin.Interceptor;
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.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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.PageHelper;

import io.shardingsphere.api.config.ShardingRuleConfiguration;
import io.shardingsphere.api.config.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

/**
 * mybatis 配置数据源类
 * 
 */
@Configuration
@MapperScan("mapper")
public class MybatisConfiguration {
	
	@Value("${mybatis.basepackage}")
    private String basePackage;
    @Value("${mybatis.xmlLocation}")
    private String xmlLocation;
    @Value("${mybatis.typeAliasesPackage}")
    private String typeAliasesPackage;
    
    @Bean(name="shardingDataSource")
    @Qualifier("shardingDataSource")
	public DataSource getShardingDataSource() throws SQLException {
		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

		//  分库分表逻辑，在这里不做代码展示

		return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig,new ConcurrentHashMap(), properties);
	}
    
    @Bean(name="druidDataSource")
    @Qualifier("druidDataSource")
    public  DataSource getDataSource() {
        DruidDataSource druidDataSource  = new DruidDataSource();
        druidDataSource.setDriverClassName("数据库驱动");
        druidDataSource.setUrl("数据库链接");
        druidDataSource.setUsername("用户名");
        druidDataSource.setPassword("密码");
        return druidDataSource ;
    }
    
    @Bean
    @Primary
    public DynamicDataSource dataSource(
    		@Qualifier("shardingDataSource")DataSource shardingDataSource,
    		@Qualifier("druidDataSource")DataSource druidDataSource) {
    	Map<Object, Object> targetDataSource=new HashMap<Object, Object>();
    	targetDataSource.put(DatabaseType.SHARDING_DATA_SOURCE.getSource(),shardingDataSource);
    	targetDataSource.put(DatabaseType.DRUID_DATA_SOURCE.getSource(),druidDataSource);
    	
    	DynamicDataSource dynamicDataSource=new DynamicDataSource();
    	dynamicDataSource.setTargetDataSources(targetDataSource);
    	dynamicDataSource.setDefaultTargetDataSource(druidDataSource);
    	
		return dynamicDataSource;

	}
    
    @Bean(name = "sqlSessionFactory")
    @ConfigurationProperties(prefix = "mybatis") 
    public SqlSessionFactory sqlSessionFactoryBean(
    		@Qualifier("shardingDataSource")DataSource shardingDataSource,
    		@Qualifier("druidDataSource")DataSource druidDataSource) {
    	
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(this.dataSource(shardingDataSource, druidDataSource));
        if(StringUtils.isNotBlank(typeAliasesPackage)){
            bean.setTypeAliasesPackage(typeAliasesPackage);
        }
        // 插件代码可以忽略
        //   -----------mybatis插件start---------------
        //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "false");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        properties.setProperty("dialect", "mysql");
        pageHelper.setProperties(properties);
        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
		// 自定义的sql日志拦截器
		Interceptor sqlStatementLogInterceptor = new SqlStatementLogInterceptor();
		Interceptor sqlResultLogInterceptor = new SqlResultLogInterceptor();
		Interceptor[] plugins =  new Interceptor[]{pageHelper, sqlStatementLogInterceptor, sqlResultLogInterceptor};

        //   -----------mybatis插件end---------------
        bean.setPlugins(plugins);
        try {
            bean.setMapperLocations(resolver.getResources(xmlLocation));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    
    @Bean
    public PageHelper getPageHelper(){
    PageHelper pageHelper=new PageHelper();
    Properties properties=new Properties();
    properties.setProperty("helperDialect","mysql");
    properties.setProperty("reasonable","false");
    properties.setProperty("supportMethodsArguments","true");
    properties.setProperty("params","count=countSql");
    properties.setProperty("useSSL", "false");
    properties.setProperty("autoReconnect", "true");
    pageHelper.setProperties(properties);
    return pageHelper;
    }
    
    
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

   @Bean
    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
    }
   
   
   
   public  Map<String, DataSource> createDataSourceMap() {
	   Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>(4);
	   // 配置第一个数据源
       DruidDataSource data1 = new DruidDataSource();
       data1.setDriverClassName("数据源驱动");
       data1.setUrl("数据库链接1");
       data1.setUsername("用户名");
       data1.setPassword("密码");
       dataSourceMap.put("data1 ", data1 );

       // 配置第二个数据源
       DruidDataSource data2 = new DruidDataSource();
       data2.setDriverClassName("数据源驱动");
       data2.setUrl("数据库链接2");
       data2.setUsername("用户名");
       data2.setPassword("密码");
       dataSourceMap.put("data2 ", data2 );

       // 配置第三个数据源
       DruidDataSource data3 = new DruidDataSource();
       data3.setDriverClassName("数据源驱动");
       data3.setUrl("数据库链接3");
       data3.setUsername("用户名");
       data3.setPassword("密码");
       dataSourceMap.put("data3", data3);
       
       // 配置第四个数据源
       DruidDataSource data4 = new DruidDataSource();
       data4.setDriverClassName("数据源驱动");
       data4.setUrl("j数据库链接4");
       data4.setUsername("用户名");
       data4.setPassword("密码");
       dataSourceMap.put("data4", data4);
       
       return dataSourceMap;
   }

}

示例代码 

/**
 * 使用分库分表的数据源
 */
@UseDataSource(source=DatabaseType.SHARDING_DATA_SOURCE)
public List<Order> listByParams(Map<String, Object> map) {
    List<Order> orders = orderMapper.listByParams(map);
    return orders ;
}


/**
 * 使用默认数据源
 */
public List<Order> listByParams2(Map<String, Object> map) {
    List<Order> orders = orderMapper.listByParams(map);
    return orders ;
}


// 也可以不使用注解，手动设置
public List<Order> listByParams2(Map<String, Object> map) {
    // 切换数据源
    DatabaseContextHolder.setDatabaseType(DatabaseType.SHARDING_DATA_SOURCE.getSource());
    List<Order> orders = orderMapper.listByParams(map);
    // 切换回默认的数据源
    DatabaseContextHolder.clearDataSource();
    return orders ;
}