公司项目开发遇到要配置多个Mysql数据源的情况,请教大佬研究之后总结如下:
- 部分pom依赖如下:
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
- application.yml配置文件添加如下配置
dm:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://${dm.url:192.168.40.148:3306/DM}?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
username: ${dw.name:***}
password: ${dw.pd:123456}
dw:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://${dw.url:192.168.40.148:3306/DW1}?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
username: ${dw.name:***}
password: ${dw.pd:123456}
- 项目中添加如下的文件,具体看下面介绍
DataSource.java
package com.ps.jury.stream.processorind.annotation;
import com.ps.jury.stream.processorind.enums.DataSourceEnum;
import java.lang.annotation.*;
/**
* @author yds
* @date 2020/1/21 16:44
*/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
//默认为DW数据源
DataSourceEnum value() default DataSourceEnum.DW;
}
DataSourceAspect.java
package com.ps.jury.stream.processorind.aop;
import com.ps.jury.stream.processorind.annotation.DataSource;
import com.ps.jury.stream.processorind.config.DataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
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;
/**
* @author yds
* @date 2020/1/21 16:45
*/
@Component
@Slf4j
@Aspect
@Order(-1)
public class DataSourceAspect {
@Pointcut(
"@within(com.ps.jury.stream.processorind.annotation.DataSource) || @annotation(com.ps.jury.stream.processorind.annotation.DataSource)")
public void pointCut() {
}
@Before("pointCut() && @annotation(dataSource)")
public void doBefore(DataSource dataSource) {
log.info("choose datasource:{}", dataSource.value().getValue());
DataSourceContextHolder.setDataSource(dataSource.value().getValue());
}
@After("pointCut()")
public void doAfter() {
DataSourceContextHolder.clear();
}
}
DataSourceConfig.java
package com.ps.jury.stream.processorind.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.ps.jury.stream.processorind.enums.DataSourceEnum;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @author 10102
*/
@Configuration
@MapperScan(basePackages = {"com.ps.jury.stream.dao.mapper"})
public class DataSourceConfig {
@Value("${dw.datasource.url}")
private String url1;
@Value("${dw.datasource.username}")
private String user1;
@Value("${dw.datasource.password}")
private String password1;
@Value("${dw.datasource.driverClassName}")
private String driverClass1;
@Value("${dm.datasource.url}")
private String url2;
@Value("${dm.datasource.username}")
private String user2;
@Value("${dm.datasource.password}")
private String password2;
@Value("${dm.datasource.driverClassName}")
private String driverClass2;
@Bean(name = "dwDataSource")
public DataSource dwDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass1);
dataSource.setUrl(url1);
dataSource.setUsername(user1);
dataSource.setPassword(password1);
return dataSource;
}
@Bean(name = "dmDataSource")
public DataSource dmDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass2);
dataSource.setUrl(url2);
dataSource.setUsername(user2);
dataSource.setPassword(password2);
return dataSource;
}
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
@Bean
@Primary
public DataSource multipleDataSource(
@Qualifier("dwDataSource") DataSource dwDataSource,
@Qualifier("dmDataSource") DataSource dmDataSource) {
MultipleDataSource multipleDataSource = new MultipleDataSource();
Map<Object, Object> targetDataSources = new HashMap<>(4);
targetDataSources.put(DataSourceEnum.DW.getValue(), dwDataSource);
targetDataSources.put(DataSourceEnum.DM.getValue(), dmDataSource);
// 添加数据源
multipleDataSource.setTargetDataSources(targetDataSources);
// 设置默认数据源
multipleDataSource.setDefaultTargetDataSource(dwDataSource);
return multipleDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(multipleDataSource(dwDataSource(), dmDataSource()));
//若使用xml文件书写sql文件,加上下面一行,配置正确的路径
//sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*/*.xml"));
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
return sqlSessionFactory.getObject();
}
}
DataSourceContextHolder.java
package com.ps.jury.stream.processorind.config;
/**
* @author yds
* @date 2020/1/21 16:40
*/
public class DataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<>();
/**
* 设置数据源
*
* @param db
*/
public static void setDataSource(String db) {
contextHolder.set(db);
}
/**
* 取得当前数据源
*
* @return
*/
public static String getDataSource() {
return contextHolder.get();
}
/**
* 清除上下文数据
*/
public static void clear() {
contextHolder.remove();
}
}
MultipleDataSource.java
package com.ps.jury.stream.processorind.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author yds
*/
public class MultipleDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSource();
}
}
DataSourceEnum.java
package com.ps.jury.stream.processorind.enums;
/**
* @author 10102
*/
public enum DataSourceEnum {
/**
* DW
*/
DW("DW"),
/**
* DM
*/
DM("DM");
private String value;
DataSourceEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
}
- 使用:业务代码里面使用方法
dao层代码示例如下:
package com.ps.jury.stream.dao.mapper;
import com.ps.jury.stream.dao.dto.var.BankCardRelated;
import com.ps.jury.stream.dao.dto.var.DeviceRelated;
import com.ps.jury.stream.dao.dto.var.IdCardRelated;
import com.ps.jury.stream.dao.dto.var.PhoneRelated;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
/**
* @author yds
* @version 1.0
* @description:
* @date 2020/6/8 0008 下午 17:51
*/
@Mapper
public interface BullishMapper {
/**
* 查询多头信息-电话号码相关
*
* @param mobile :
* @return :
*/
@Select(" select p1,p2,p3,p4,p5,p6 from ind_phone_ralated where phone = #{mobile} ORDER BY update_time desc limit 1 ")
PhoneRelated getPhoneRelatedInfo(@Param("mobile") String mobile);
}
Service层数据在方法上加上 @DataSource() 注解,指明要使用的数据源即可。
@Service
public class BullishVar {
@Autowired
private BullishMapper bullishMapper;
/**
* 处理多头信息
*
* @param applyRequest :申请信息
* @return : BusinessInfo
*/
@DataSource(DataSourceEnum.DM)
public BullishInfo businessInfoProcess(ApplyRequest applyRequest) {
BullishInfo bullishInfo = new BullishInfo();
if (applyRequest != null) {
PhoneRelated phoneRelatedInfo = bullishMapper.getPhoneRelatedInfo(applyRequest.getMobile());
if (phoneRelatedInfo != null) {
bullishInfo.setMobileRegisteredOnMultiplePlatformsWithin30Days(phoneRelatedInfo.getP1());
bullishInfo.setMobileRegisteredOnMultiplePlatformsWithin90Days(phoneRelatedInfo.getP2());
bullishInfo.setMobileApplyLoanOnMultiplePlatformsWithin30Days(phoneRelatedInfo.getP3());
bullishInfo.setMobileApplyLoanOnMultiplePlatformsWithin90Days(phoneRelatedInfo.getP4());
bullishInfo.setMobileApplyDeviceNumWithin30Days(phoneRelatedInfo.getP5());
bullishInfo.setMobileRelatedMultipleIdCardWithin90Days(phoneRelatedInfo.getP6());
}
}
return bullishInfo;
}
}