springboot 多数据源（如何连接两个数据库）

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

xueshenghu

6174人浏览 · 2022-09-29 16:04:24

xueshenghu · 2022-09-29 16:04:24 发布

由于项目需要，对原有项目新增别的数据库数据的交互。原有项目数据源为MySQL，新增数据源为Oracle。

第一步application.yml配置

spring:
  datasource:
    one:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/abc  #测试数据库
      username: root
      password: root

    two:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: oracle.jdbc.driver.OracleDriver
      jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521/def #测试数据库,该数据库连接可以是远程服务器 
      username: u23
      password: u23

 第二步：添加pom依赖，在pom文件中新增

<!-- Mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--oracle驱动-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>1.5.9.RELEASE</version>
        </dependency>

第三步 配置Oracle数据库依赖,该处理比较关键。

在项目工作空间有pom.xml 下在CMD中执行以下语句,红色为你本地安装oracle目录下的ojdbc6.jar包，DgroupId，DartifactId，Dversion与pom文件oracle驱动内容一致。

mvn install:install-file -Dfile="E:\app\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar" -DgroupId="com.oracle" -DartifactId="ojdbc6" -Dversion="11.2.0.4.0" -Dpackaging="jar"

执行完成后在你本地maven依赖库中你会发现oracle依赖包已经处理好了

（*注以上针对项目服务与数据库都在一个服务器上的情况，执行后都正常。

但测试连接远程服务器上的Oracle发现出现一下问题：SpringBoot 2.x HikariPool - Driver does not support get/set network timeout for connect 提示驱动不支持，找了很多发现

原因：ojdbc6没实现这个jdbc标准的函数而已

解决方案

使用高版本的Ojdbc即可，本次使用的是Ojdbc7，下载（连接：https://aerfazhe.lanzouw.com/ibhD4yo3r4h）
后安装到本地Mvn库中即可。方法一样。感谢https://www.cnblogs.com/aerfazhe/p/15791252.html博主）

更新POM中ojdbc

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.0.1.2</version>
</dependency>

第四步 application启动类增加注解，@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})意为：去掉springboot服务启动时对于数据源配置的自动导入。


@SpringBootApplication
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
public class TicketsApplication {

    public static void main(String[] args) {
        SpringApplication.run(TicketsApplication.class, args);
    }

}

第五步，该步就是具体的代码

新建两个数据源类，类中的

@MapperScan basePackages = "com.ticket.tickets.dao.test1" 该路径为你的Mapper Java类所在文件夹路径。

sqlSessionFactoryRef = "SqlSessionFactory1" 该值为该类中你自己命名的SqlSessionFactory的bean名字。

MySQL如下 MysqlConfig.java


import javax.sql.DataSource;

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;

@Configuration
@MapperScan(basePackages = "com.ticket.tickets.dao.test1",sqlSessionFactoryRef = "SqlSessionFactory1")

public class MysqlConfig {

    @Primary
    @Bean(name = "DataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.one")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

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

    @Primary
    @Bean(name = "TransactionManager1")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("DataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "SqlSessionTemplate1")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SqlSessionFactory1") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

ORACLE如下 OracleConfig.java


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.ticket.tickets.dao.test2",sqlSessionFactoryRef = "SqlSessionFactory2")

public class OracleConfig {

        @Bean(name = "DataSource2")
        @ConfigurationProperties(prefix = "spring.datasource.two")
        public DataSource testDataSource() {
            return DataSourceBuilder.create().build();
        }

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

        @Bean(name = "TransactionManager2")
        public DataSourceTransactionManager testTransactionManager(@Qualifier("DataSource2") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }

        @Bean(name = "SqlSessionTemplate2")
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SqlSessionFactory2") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
}

两个mapper类

StudentMapper.java

import com.ticket.tickets.pojo.Student;
import org.springframework.stereotype.Component;

@Component
public interface StudentMapper {
    Student selectByPrimaryKey(Integer id);
}

StuMapper.java

import com.ticket.tickets.pojo.Stu;
import org.springframework.stereotype.Component;

@Component
public interface StuMapper {
    Stu selectByPrimaryKey(int id);
}

两个实体类 Student Stu 根据你自己数据库字段创建。

工程的resources 下新建mybatis 文件夹，新建两个mapper.xml文件，注意核对namespace路径。

StudentMapper.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.ticket.tickets.dao.test1.StudentMapper">

    <select id="selectByPrimaryKey" resultType="com.ticket.tickets.pojo.Student" parameterType="java.lang.Integer" >
    select * from student
    where id = #{id,jdbcType=INTEGER}
  </select>

</mapper>

StuMapper.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.ticket.tickets.dao.test2.StuMapper">

    <select id="selectByPrimaryKey" resultType="com.ticket.tickets.pojo.Stu" parameterType="java.lang.Integer" >
       select *  from  stu  where id = #{id}
  </select>

</mapper>

编写controller 调用


import com.ticket.tickets.dao.test1.StudentMapper;
import com.ticket.tickets.dao.test2.StuMapper;
import com.ticket.tickets.pojo.Stu;
import com.ticket.tickets.pojo.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/stu")
public class JDBCController {

    @Autowired
    private StudentMapper studentMapper;

    @Autowired
    private StuMapper stuMapper;

    @GetMapping("/getStudent")
    public Student getStudentByPostgres(){
        return studentMapper.selectByPrimaryKey(2);
    }

    @GetMapping("/getStu")
    public Stu getStuByOracle(){
        return stuMapper.selectByPrimaryKey(2);
    }

}

开始调用

测试调用mysql数据库数据