SpringBoot同时连接两个数据库

快来打我* 2023-10-05 17:57 2阅读 0赞

## 前言 ##

*  应用场景不同，操作主数据库`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`注解，从数据库都没有  
    ![在这里插入图片描述][20210118174640642.png_pc_left]

#### 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

[20210118174640642.png_pc_left]: https://img-blog.csdnimg.cn/20210118174640642.png#pc_left