Mysql 多数据源设置与使用

公司项目开发遇到要配置多个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;
    }

}