SpringBoot配置连接两个或多个数据库

你的名字 2022-09-10 00:23 303阅读 0赞

# 一、背景 #

项目中需要从两个不同的数据库查询数据，之前实现方法是：SpringBoot配置连接一个数据源，另一个使用jdbc代码连接。  
比如jdbc代码连接

public List<String> getSecurityInstalled(String computerName){ 
            List<String> result = new ArrayList<String>();
            String sql = "select * from(\n" +
                    "Select t0.Name0,T1.Process_Name,T1.Last_CheckTime from v_R_System T0 right join\n" +
                    "(Select ResourceID,Caption0 as Process_Name,MAX(TimeStamp) as Last_CheckTime from v_GS_PROCESS where Caption0 in ('edpa.exe','PGPtray.exe','ccSvcHst.exe') group by ResourceID,Caption0) T1\n" +
                    "on T0.ResourceID=t1.ResourceID \n" +
                    ") a where Name0='" + computerName + "'";
    
            DriverManagerDataSource dataSource=new DriverManagerDataSource();
            setupDataSource2(dataSource);
    
            JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
            jdbcTemplate.query(sql, new RowCallbackHandler() { 
                public void processRow(ResultSet resultSet) throws SQLException { 
                    result.add(resultSet.getString("Process_Name"));
                }
            });
    
            return result;
        }
    
    public void setupDataSource2(DriverManagerDataSource dataSource){ 
            String[] params = getSAPConfig("IT00022serviceconfig").getString("ashost").split("#");
            dataSource.setUrl(params[4]);
            dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            dataSource.setUsername(params[5]);
            dataSource.setPassword(params[6]);
        }

或者通过配置文件切换数据源  
在resources文件夹下，创建

application.properties
    application-prd.properties
    application-qas.properties
    application-dev.properties

在主文件，修改spring.profiles.active切换数据源

spring.profiles.active=qas
    server.port=8770

![在这里插入图片描述][5eea297070c44d7589066d1f71a91191.png]  
为了改进，现在使用SpringBoot配置连接两个数据源。

# 二、实现效果 #

一个SpringBoot项目，同时连接两个数据库：比如一个是pgsql数据库，一个是oracle数据库

（啥数据库都一样，连接两个同为oracle的数据库，或两个不同的数据库，只需要更改对应的driver-class-name和jdbc-url等即可）

注意：连接什么数据库，要引入对应数据库的包。

# 三、实现步骤 #

## 1、修改application.yml，添加一个数据库连接配置 ##

server:
      port: 7101
    spring:
      jpa:
        show-sql: true
      datasource:
        test1:
          driver-class-name: org.postgresql.Driver
          jdbc-url: jdbc:postgresql://127.0.0.1:5432/test  #测试数据库
          username: root
          password: root
     
        test2:
          driver-class-name: oracle.jdbc.driver.OracleDriver
          jdbc-url: jdbc:oracle:thin:@127.0.0.1:8888:orcl  #测试数据库
          username: root
          password: root

**特别注意**：

**（1）使用test1、test2区分两个数据库连接  
（2）url改为：jdbc-url**

## 2、使用代码进行数据源注入，和扫描dao层路径（以前是在yml文件里配置mybatis扫描dao的路径） ##

新建config包，包含数据库1和数据库2的配置文件  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oeS6Jmr6Jmrfg_size_17_color_FFFFFF_t_70_g_se_x_16]  
（1）**第一个数据库作为主数据库**，项目启动**默认连接此数据库**  
DataSource1Config.java

package com.test.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;
     
    @Configuration
    @MapperScan(basePackages = "com.test.dao.test1", sqlSessionTemplateRef  = "test1SqlSessionTemplate")
    public class DataSource1Config { 
     
        @Bean(name = "test1DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test1")
        @Primary
        public DataSource testDataSource() { 
            return DataSourceBuilder.create().build();
        }
     
        @Bean(name = "test1SqlSessionFactory")
        @Primary
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:test1/*.xml"));
            return bean.getObject();
        }
     
        @Bean(name = "test1TransactionManager")
        @Primary
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
     
        @Bean(name = "test1SqlSessionTemplate")
        @Primary
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

特别注意：  
（1）**主数据库DataSource1Config都有 @Primary注解**，从数据库DataSource2Config都没有  
（2）第二个数据库DataSource2Config作为从数据库  
DataSource2Config.java

package com.test.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.test.dao.test2", sqlSessionTemplateRef  = "test2SqlSessionTemplate")
    public class DataSource2Config { 
     
        @Bean(name = "test2DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test2")
        public DataSource testDataSource() { 
            return DataSourceBuilder.create().build();
        }
     
        @Bean(name = "test2SqlSessionFactory")
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:test2/*.xml"));
            return bean.getObject();
        }
     
        @Bean(name = "test2TransactionManager")
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
     
        @Bean(name = "test2SqlSessionTemplate")
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

## 3、 在dao文件夹下，新建test1和test2两个包，分别放两个不同数据库的dao层文件 ##

（1）TestDao1.java

@Component
    public interface TestDao1 { 
     
        List<DailyActivityDataMiddle> selectDailyActivity();
     
    }

（2）TestDao2.java

@Component
    public interface TestDao2 { 
     
        List<MovieShowTest> selectDailyActivity();
     
    }

## 4、 在resource下新建test1和test2两个文件夹，分别放入对应dao层的xml文件 ##

（我原来项目的dao的xml文件在resource目录下，你们在自己的项目对应目录下即可）

注意dao的java文件和dao的xml文件名字要一致  
（1）TestDao1.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.test.dao.test1.TestDao1">
     
        <select id="selectDailyActivity" resultType="com.test.pojo.DailyActivityDataMiddle">
     
            SELECT * FROM daily_activity_data_middle
     
        </select>
     
    </mapper>

（2）TestDao2.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.test.dao.test2.TestDao2">
     
        <select id="selectDailyActivity" resultType="com.test.pojo.MovieShowTest">
     
            SELECT * FROM movieshowtest
     
        </select>
     
    </mapper>

## 5、测试 ##

在controller文件里，注入两个数据库的dao，分别查询数据

@RestController
    public class TestController extends BaseController{ 
     
        @Autowired
        private PropertiesUtils propertiesUtils;
     
        @Autowired
        private TestDao1 testDao1;
     
        @Autowired
        private TestDao2 testDao2;
     
        @RequestMapping(value = { "/test/test1"},method = RequestMethod.POST)
        public Result<JSONObject> DataStatistics (@RequestBody JSONObject body) throws Exception { 
            Result<JSONObject> result = new Result<>(ICommon.SUCCESS, propertiesUtils.get(ICommon.SUCCESS));
     
            JSONObject object = new JSONObject();
            object.put("data",testDao1.selectDailyActivity());
            result.setResult(object);
            return result;
        }
     
        @RequestMapping(value = { "/test/test2"},method = RequestMethod.POST)
        public Result<JSONObject> DataStatisticsaa (@RequestBody JSONObject body) throws Exception { 
            Result<JSONObject> result = new Result<>(ICommon.SUCCESS, propertiesUtils.get(ICommon.SUCCESS));
     
            JSONObject object = new JSONObject();
            object.put("data",testDao2.selectDailyActivity());
            result.setResult(object);
            return result;
        }
    }

原文链接  
https://blog.csdn.net/qq\_18432653/article/details/107715127

[5eea297070c44d7589066d1f71a91191.png]: /images/20220829/fe4a9c975a1e4d9ebd5bd7a0a002c8a2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oeS6Jmr6Jmrfg_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/6b0f005dcbc74c0aaa4d0a33a5c038ae.png