Druid 可以说是国内使用最广泛的数据源连接池产品
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
application.yml配置
spring:
datasource:
pay:
username: root
password: root
host: 192.168.37.133:3306
jdbc-url: jdbc:mysql://${spring.datasource.pay.host}/${spring.datasource.pay.schema-name}?useUnicode=true&characterEncoding=utf8&useSSL=false
schema-name: dlsms
order:
username: root
password: root
host: 192.168.37.134:3306
jdbc-url: jdbc:mysql://${spring.datasource.order.host}/${spring.datasource.order.schema-name}?useUnicode=true&characterEncoding=utf8&useSSL=false
schema-name: dlsms
方案一:手动切换数据源
适合没有分模块的功能,后期又需要加入多数据源
配置数据源和事务管理
import net.test.project.constants.DataSourceKey;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
@MapperScan({"net.test.project.dao.mapper.*"})
@Configuration
public class DataSourceProxyConfig {
/**
* 配置默认数据源
* @return
*/
@Bean(name = "originOrder") // 声明其为Bean实例
@ConfigurationProperties(prefix = "spring.datasource.order")
public DataSource dataSourceOrder() {
return new DruidDataSource();
}
@Bean(name = "originStorage") // 声明其为Bean实例
@ConfigurationProperties(prefix = "spring.datasource.storage")
public DataSource dataSourceStorage() {
return new DruidDataSource();
}
@Bean(name = "originPay") // 声明其为Bean实例
@ConfigurationProperties(prefix = "spring.datasource.pay")
public DataSource dataSourcePay() {
return new DruidDataSource();
}
/**
* 设置多数据源,配置动态路由数据源
* @return
*/
@Bean("dynamicDataSource")
public DataSource dynamicDataSource(@Qualifier("originOrder") DataSource dataSourceOrder,
@Qualifier("originStorage") DataSource dataSourceStorage,
@Qualifier("originPay") DataSource dataSourcePay) {
Map<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put(DataSourceKey.ORDER.name(), dataSourceOrder);
dataSourceMap.put(DataSourceKey.STORAGE.name(), dataSourceStorage);
dataSourceMap.put(DataSourceKey.PAY.name(), dataSourcePay);
//设置默认
DynamicDataSource dynamicRoutingDataSource = new DynamicDataSource();
dynamicRoutingDataSource.setDefaultTargetDataSource(dataSourceOrder);
dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
return dynamicRoutingDataSource;
}
/**
* 设置会话工厂
*
* @return
* @throws Exception
*/
/*@Bean(name="sqlSessionFactory")
public SqlSessionFactoryBean sqlSessionFactoryBean(
@Qualifier("dynamicDataSource") DataSource dataSource) {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
return sqlSessionFactoryBean;
}*/
@Bean(name="sqlSessionFactory")
@Primary
public SqlSessionFactory mybatisSqlSessionFactoryBean (
@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
// 设置别名扫描包(实体类所在包)
sqlSessionFactoryBean.setTypeAliasesPackage("com.test.custom.entity");
// 设置 Mapper XML 文件位置(与 application.yml 中配置一致)
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource[] mapperLocations = resolver.getResources("classpath*:mapper/*.xml");
sqlSessionFactoryBean.setMapperLocations(mapperLocations);
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactoryBean.setConfiguration(configuration);
sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor());
return sqlSessionFactoryBean.getObject();
}
/**
* 添加分页插件(可选)
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
/**
* 配置事务管理器
*
* @return
*/
@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager(@Qualifier("originOrder") DataSource dataSourceOrder,
@Qualifier("originStorage") DataSource dataSourceStorage,
@Qualifier("originPay") DataSource dataSourcePay) {
return new DataSourceTransactionManager(dynamicDataSource(dataSourceOrder, dataSourceStorage, dataSourcePay));
}
}
由于是MyBatsi-Plus,所以配的是MybatisSqlSessionFactoryBean,如果是MyBatis,则应该是SqlSessionFactoryBean
实现选择目标数据源
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
/**
* 取得当前使用哪个数据源
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
log.info("当前数据源 [{}]", DynamicDataSourceContextHolder.getDataSourceType());
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
数据源字典
public enum DataSourceKey {
ORDER("order", "order数据库"),
// 定义数据源类型为BASIC,表示基础数据库
STORAGE("storage", "storage数据库"),
// 定义数据源类型为BILL,表示账单计费数据库
PAY("pay", "计费数据库"),
;
// 数据源的类型标识
private String type;
// 数据源的名称
private String name;
/**
* 构造函数,初始化数据源类型和名称
*
* @param type 数据源的类型标识
* @param name 数据源的名称
*/
DataSourceKey(String type, String name) {
this.type = type;
this.name = name;
}
/**
* 获取数据源的类型标识
*
* @return 数据源的类型标识
*/
public String getType() {
return type;
}
/**
* 设置数据源的类型标识
*
* @param type 数据源的类型标识
*/
public void setType(String type) {
this.type = type;
}
/**
* 获取数据源的名称
*
* @return 数据源的名称
*/
public String getName() {
return name;
}
/**
* 设置数据源的名称
*
* @param name 数据源的名称
*/
public void setName(String name) {
this.name = name;
}
/**
* 根据类型标识获取对应的枚举值
*
* @param type 数据源的类型标识
* @return 对应类型标识的枚举值,如果找不到匹配的,则返回NULL
*/
public static DataSourceKey getEnumByType(String type) {
// 默认返回值为NULL,即无数据源
DataSourceKey e = NULL;
// 如果传入的类型不为空,则遍历所有枚举值寻找匹配的类型
if (type != null) {
for (DataSourceKey o : values()) {
// 如果找到匹配的类型,则更新返回值并终止遍历
if (o.getType().equals(type)) {
e = o;
break;
}
}
}
// 返回找到的枚举值
return e;
}
}
切换数据源
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class DynamicDataSourceContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal<>();
private static final ThreadLocal afterContextHolder = new ThreadLocal<>();
public static void setAfterDbType(DataSourceKey dbTypeEnum) {
afterContextHolder.set(dbTypeEnum.getType());
}
public static String getAfterDbType() {
return (String) afterContextHolder.get();
}
public static void clearAfterDbType() {
afterContextHolder.remove();
}
/**
* 设置数据源
*
* @param dbTypeEnum
*/
public static void setDataSourceType(DataSourceKey dbTypeEnum) {
log.info("切换到{}数据源", dbTypeEnum.getName());
contextHolder.set(dbTypeEnum.getType());
}
/**
* 取得当前数据源
*
* @return
*/
public static String getDataSourceType() {
return (String) contextHolder.get();
}
/**
* 清除上下文数据
*/
public static void clearDataSourceType() {
contextHolder.remove();
}
}
使用方法:查前选库
DynamicDataSourceContextHolder.setDataSourceType(DataSourceKey.PAY);
多数据源下的事务问题
多数据源下使用事务时,需要在注解上明确是哪个数据源,类似下面这样,否则会报找不到事务管理实例的错误。
@Transactional(value = "transactionManager",rollbackFor = Exception.class)
方案二:
自动数据源切换
组织两个配置类。原理:mybatis多数据源的原理是根据不同包,调用不同的数据源,你只需要把你的mapper.java和mapper.xml写在某个package中,springboot自动帮你实现数据源切换。注意第二个配置类不需要@Primary
- @MapperScan注解中的basePackages指向的是指定的Dao层。
- @MapperScan注解中sqlSessionFactoryRef 用来指定使用某个SqlSessionFactory来操作数据源。
- setMapperLocations指向的是操作执行数据库的Mapper层。
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
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;
@Configuration
@MapperScan(basePackages = "com.example.dao.crm", sqlSessionTemplateRef = "crmSqlSessionTemplate")
public class CrmDataSourceConfig {
@Bean(name = "crmDataSource")
@ConfigurationProperties(prefix = "spring.datasource.crm")
public DataSource crmDataSourceConfig() {
return new DruidDataSource();
}
@Bean(name = "crmSqlSessionFactory")
@Primary
public SqlSessionFactory crmSqlSessionFactory(@Qualifier("crmDataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/crm/*.xml"));
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
sqlSessionFactory.setPlugins(mybatisPlusInterceptor());
return sqlSessionFactory.getObject();
}
/**
* 添加分页插件(可选)
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
@Bean(name = "crmTransactionManager")
@Primary
public DataSourceTransactionManager crmTransactionManager(@Qualifier("crmDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "crmSqlSessionTemplate")
@Primary
public SqlSessionTemplate crmSqlSessionTemplate(@Qualifier("crmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
切面自动切换
通过切面的包路径配置实现
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Component
@Aspect
@Order(-10000) // 这是为了保证AOP在事务注解之前生效,Order的值越小,优先级越高
@Slf4j
public class DataSourceSwitchAspect {
@Pointcut("execution(* net.est.test.api.service.order..*.*(..))")
private void db3Aspect() {
}
@Pointcut("execution(* net.est.test.api.service.pay..*.*(..))\"")
private void db5Aspect() {
}
@Before("db3Aspect()")
public void rpt() {
final String afterDbType = DynamicDataSourceContextHolder.getDataSourceType();
final DataSourceKey afterDB = DataSourceKey.getEnumByType(afterDbType);
DynamicDataSourceContextHolder.setAfterDbType(afterDB);
log.debug("切换到{}数据源...", DataSourceKey.ORDER.name());
DynamicDataSourceContextHolder.setDataSourceType(DataSourceKey.ORDER);
}
@Before("db5Aspect()")
public void smsConfig() {
final String afterDbType = DynamicDataSourceContextHolder.getDataSourceType();
final DataSourceKey afterDB = DataSourceKey.getEnumByType(afterDbType);
DynamicDataSourceContextHolder.setAfterDbType(afterDB);
log.debug("切换到{}数据源...", DataSourceKey.PAY.name());
DynamicDataSourceContextHolder.setDataSourceType(DataSourceKey.PAY);
}
@After("db3Aspect()||db5Aspect()")
public void back() {
final String afterDbType = DynamicDataSourceContextHolder.getAfterDbType();
log.debug("切回到{}数据源...", afterDbType);
if (StringUtils.isEmpty(afterDbType)) {
DynamicDataSourceContextHolder.setDataSourceType(DataSourceKey.PAY);
} else {
final DataSourceKey afterByType = DataSourceKey.getEnumByType(afterDbType);
DynamicDataSourceContextHolder.setDataSourceType(afterByType);
}
}
}
定义了一个基于 AOP 的数据源切换切面类 DataSourceSwitchAspect,该切面用于实现动态数据源切换,支持在不同业务模块访问不同数据库。其功能如下:
定义切点:分别针对 order 和 pay 包下的方法定义切点 db3Aspect 和 db5Aspect。
前置通知:
- 在执行 order 包方法前,将数据源切换为 ORDER。
- 在执行 pay 包方法前,将数据源切换为 PAY。
后置通知:在目标方法执行结束后,将数据源恢复为之前的状态。
以上两种方法都是通过不同的包来自动自动切换数据源
常见问题
druid抛出异常:javax.management.InstanceAlreadyExistsException: com.alibaba.druid:type=DruidDataSource,id=xxx
同一个domain里面的MBean要求name唯一,检查SpringBoot应用的application.properties等配置文件name配置