SpringBoot同时连接两个数据库

前言

• 应用场景不同，操作主数据库db_1之外，还需要操作从数据库db_2
• 不管操作什么数据库，配置数据库连接时注意：driver-class-name 和 jdbc-url

代码实现

1. 修改 application.yml配置文件，添加db_2数据库连接

• 注意：配置连接两个数据库，url改为：jdbc-url

server:
  port: 8083
  servlet:
    context-path: /mes
spring:
  db1:
    datasource:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/lmes
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
  db2:
    datasource:
      driver-class-name: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@//172.20.10.101:1521/PROD
      username: apps
      password: apps
      type: com.alibaba.druid.pool.DruidDataSource

2. 新建config包，添加db1和db2的配置文件

• 主数据库与从数据库配置区别：主数据库有 @Primary注解，从数据库都没有
  

2.1 主数据库db1，项目启动默认连接此数据库：PrimaryDataSourceConfig

package com.bbzd.mes.common.datasources;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
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 javax.sql.DataSource;
import java.io.IOException;
import java.util.Optional;
import java.util.stream.Stream;

@Configuration
@EnableConfigurationProperties(MybatisProperties.class)
public class PrimaryDataSourceConfig {
    @Autowired
    private MybatisProperties properties;

    @Bean(name = "db1DataSource")
    @ConfigurationProperties(prefix = "spring.db1.datasource")
    @Primary
    public DataSource db1DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactory")
    @ConfigurationProperties(prefix = "mybatis")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        bean.setMapperLocations(resolveMapperLocations(properties.getMapperLocations()));
        properties.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
        bean.setConfiguration(properties.getConfiguration());
        return bean.getObject();
    }

    private Resource[] resolveMapperLocations(String[] mapperLocations) {
        PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();

        return Stream.of(Optional.ofNullable(mapperLocations).orElse(new String[0]))
                .flatMap(location -> Stream.of(getResources(pathMatchingResourcePatternResolver, location))).toArray(Resource[]::new);
    }

    private Resource[] getResources(PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver, String location) {
        try {
            return pathMatchingResourcePatternResolver.getResources(location);
        } catch (IOException e) {
            return new Resource[0];
        }
    }
    
    @Bean(name = "db1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


}

2.2 从数据库db2：DataSource2Config

package com.bbzd.mes.common.datasources;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.bbzd.mes.oracledao",sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource2Config {

    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.db3.datasource")
    public DataSource db3DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

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

    @Bean(name="db2transactionManager")
    public DataSourceTransactionManager transactionManagerOne(){
        return  new DataSourceTransactionManager(db3DataSource());
    }

    @Bean(name = "db2JdbcTemplate")
    public JdbcTemplate jdbcTemplate(
            @Qualifier("db2DataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

3. dao层

注意：主数据库与从数据库区别：从数据库需要使用@Transactional 注解指向db2数据源

3.1 主数据库db1，dao层写法

public interface UserMapper {
    boolean save(UserVo userVo);
}

3.2 从数据库db2，dao层写法

@Transactional(value = "db2transactionManager")
public interface UserMapper {
    UserDto findById(Inter id);
}

4. resources目录下xml注意事项

db1 和 db2 xml文件 namespace不同

• 数据库db1，User.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.bbzd.mes.dao.UserMapper">

</mapper>

• 数据库db2，User.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.bbzd.mes.oracledao.UserMapper">

</mapper>