SpringBoot配置多数据源

目的

需求背景

      需统计区域拉新人员的业绩，即统计通过该途径注册的会员的消费情况。需获取 两个数据库实例下数据库表数据，然后通过关联字段将两张表的数据整合一起。

---

a表的数据

会员id	区域	活动名称
1	华南	乳饮推广001
2	华北	面膜推广002

b表的数据

会员id	订单id	发货时间	销售金额
1	00001	2020-11-06 15:05:36	15.90
1	00002	2020-11-02 15:05:36	49.90
2	00003	2020-11-04 15:05:36	59.90
3	00004	2020-11-03 15:05:36	39.90

最终要获取的数据

会员id	区域	活动名称	订单id	发货时间	销售金额
1	华南	乳饮推广001	00001	2020-11-06 15:05:36	15.90
1	华南	乳饮推广001	00002	2020-11-02 15:05:36	49.90
2	华北	面膜推广002	00003	2020-11-04 15:05:36	59.90

SpringBoot配置多数据源

PS:两个实例都是mysql数据库。

创建SpringBoot项目

项目创建完后导入依赖

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

配置数据源1

package com.pec.reportdata.config;

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.boot.jdbc.DataSourceBuilder;
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;

/**
 * 配置crm数据源
 */
@Configuration
@MapperScan(basePackages = "com.pec.reportdata.mapper.crm", sqlSessionTemplateRef = "crmSqlSessionTemplate")

public class DataSourceCrm {
    @Bean(name = "crmDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.crm")
    @Primary
    public DataSource crmDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "crmSqlSessionFactory")
    @Primary
    public SqlSessionFactory crmSqlSessionFactory(@Qualifier("crmDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/crm/*.xml"));
        return bean.getObject();
    }

    @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) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

配置数据源2

package com.pec.reportdata.config;

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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.pec.reportdata.mapper.jfsc", sqlSessionTemplateRef = "jfscSqlSessionTemplate")
public class DataSourceJfsc {
    @Bean(name = "jfscDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.jfsc")
    public DataSource jfscDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "jfscSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("jfscDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/jfsc/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "jfscTransactionManager")
    public DataSourceTransactionManager jfscTransactionManager(@Qualifier("jfscDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "jfscSqlSessionTemplate")
    public SqlSessionTemplate jfscSqlSessionTemplate(@Qualifier("jfscSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

application.yml配置

ps:此处url必须使用 jdbc-url

server:
  port: 8092
spring:
  datasource:
    crm:
      jdbc-url: jdbc:mysql://xxx:3306/xxx?serverTimezone=GMT
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: xxx
      password: xxx
    jfsc:
      jdbc-url: jdbc:mysql://xxx:3306/xxx?serverTimezone=GMT
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: xxx
      password: xxx

项目结构目录

整合数据逻辑

		//会员id 订单id 发货时间 实付金额 集合
		List<Order> orderList = jfscMapper.getOrderListEvery();
        //会员id，区域,活动名 集合
        List<Order> reportData = crmMapper.getReportData(orderList);
        // 通过id去重(其实没重复的，因为已经通过id,区域,活动名分组了)
        Map<Integer, Order> map = reportData.stream()
                .collect(Collectors.toMap(Order::getMemberId, Function.identity(), (key1, key2) -> key2));
  	   //合并,将两个list集合根据id进行合并
        orderList.forEach(x->{
            if(map.containsKey(x.getMemberId())){
                Order order = map.get(x.getMemberId());
                x.setTag(order.getTag());
                x.setActivityName(order.getActivityName());
            }
        });
        //做一轮判断，如果集合对象中tag,activity_name为null则说明不是区域注册人员的订单，去掉这些。
        for(int i=0;i<orderList.size();i++){
            if(orderList.get(i).getTag()==null || orderList.get(i).getActivityName()==null){
                orderList.remove(i--);
            }
        }
       log.info("最终清洗出来的数据有"+orderList.size()+"条");