Spring Boot 访问多个数据库

有的业务需要后台服务读取多个数据库的数据，进行汇总保存，特此记录一下如何访问多个数据库

application.properties

#第一个数据库
spring.datasource.one.jdbc-url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useSSL=true
spring.datasource.one.username=root
spring.datasource.one.password=123456
spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.one.max-lifetime=1200000
spring.datasource.one.maximum-pool-size=6
# 第二个数据库
spring.datasource.two.jdbc-url=jdbc:mysql://127.0.0.1:3306/test2?characterEncoding=UTF-8&useSSL=true
spring.datasource.two.username=root
spring.datasource.two.password=123456
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.two.max-lifetime=1200000
spring.datasource.two.maximum-pool-size=6

代码配置 DataSourceOneConfig

注意 basePackages 扫描的包名要和第二个数据库的区分开

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.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.sweet.mybatisdemo.mapper.test", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DataSourceOneConfig {
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource.one")
  @Primary
  public DataSource db1DataSource() {
    DataSource build = DataSourceBuilder.create().build();
    return build;
  }

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

  @Bean
  @Primary
  public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }

  @Bean
  @Primary
  public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

代码配置 DataSourceTwoConfig

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.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.sweet.mybatisdemo.mapper.test2", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DataSourceTwoConfig {
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource.two")
  @Primary
  public DataSource db2DataSource() {
    return DataSourceBuilder.create().build();
  }

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

  @Bean
  @Primary
  public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }

  @Bean
  @Primary
  public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

注意mapper文件的存放位置要和 MapperScan 扫描的包一致

事务配置

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
public class TransactionManagerConfig {

  @Bean(name = "transactionManager1")
  public PlatformTransactionManager transactionManager1(@Qualifier("db1DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }

  @Bean(name = "transactionManager2")
  public PlatformTransactionManager transactionManager2(@Qualifier("db2DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
}

用法

import com.sweet.mybatisdemo.mapper.test2.UserMapper2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService {
  @Autowired
  private UserMapper2 userMapper2;

  @Transactional(transactionManager = "transactionManager2")
  public void txTests() {
    userMapper2.insert("hall3");
//    System.out.println(1/0);
    userMapper2.insert("hall4");
  }
}