基于spring boot mybatis连接多个数据库实现方式

背景

最近接到一个需求：需要对不同数据做接口，其中主要的问题在于所有的数据不在同一平台上，其中数据源可能涉及MySQL、Orcal、sqlServer等其他数据库。于是正有了我自身封装的一个小项目，流程很简单，大佬请忽略………哈哈哈！！

1.pom.xml引入

其中分别引入了mysql、Orcal驱动

<properties>     
 <java.version>1.8</java.version>     
  <fastjson.version>1.2.47</fastjson.version>     
  <pagehelper.spring.boot.starter.version>1.2.5</pagehelper.spring.boot.starter.version>  
     <commons.io.version>2.5</commons.io.version>  
 </properties>   
<dependencies>      
 <dependency>        
   <groupId>org.springframework.boot</groupId>    
       <artifactId>spring-boot-starter-web</artifactId>  
     </dependency>    
   <dependency>    
       <groupId>org.springframework.boot</groupId>    
       <artifactId>spring-boot-starter-test</artifactId>     
      <scope>test</scope>       
</dependency>       
<!--jpa是利用Hibernate生成各种自动化的sql--><!--    
   <dependency>--><!--           
<groupId>org.springframework.boot</groupId>--><!--        
   <artifactId>spring-boot-starter-data-jpa</artifactId>-->
<!--        </dependency>-->       
<!--mysql数据库-->       
<dependency>          
 <groupId>org.mybatis.spring.boot</groupId>      
     <artifactId>mybatis-spring-boot-starter</artifactId>      
     <version>1.3.2</version>       
</dependency>       
<dependency>        
   <groupId>mysql</groupId>       
    <artifactId>mysql-connector-java</artifactId>       
</dependency>       
<!-- https://mvnrepository.com/artifact/oracle/oracle-jdbc -->    
   <dependency>          
 <groupId>com.oracle</groupId>        
   <artifactId>ojdbc6</artifactId>         
  <version>11.2.0.1.0</version>       
</dependency>         
 <dependency>       
    <groupId>org.springframework.boot</groupId>     
      <artifactId>spring-boot-starter-tomcat</artifactId>   
        <scope>provided</scope>        
</dependency>      
 <dependency>         
  <groupId>com.alibaba</groupId>    
       <artifactId>fastjson</artifactId>     
      <version>${fastjson.version}</version> 
      </dependency>      
 <!-- pagehelper 分页插件 -->    
   <dependency>        
   <groupId>com.github.pagehelper</groupId>    
       <artifactId>pagehelper-spring-boot-starter</artifactId>      
     <version>${pagehelper.spring.boot.starter.version}</version>     
  </dependency>      
 <!--io常用工具类 -->     
  <dependency>          
 <groupId>commons-io</groupId>      
     <artifactId>commons-io</artifactId>    
       <version>${commons.io.version}</version>     
  </dependency>      
 <!--常用工具类 -->       
<dependency>           
<groupId>org.apache.commons</groupId>   
        <artifactId>commons-lang3</artifactId>       
</dependency>   
</dependencies>  
 <build>      
<plugins>         
  <plugin>           
    <groupId>org.springframework.boot</groupId>          
     <artifactId>spring-boot-maven-plugin</artifactId>         
  </plugin>       
</plugins>  
 </build>
 

2.在application.properties配置数据源

#配置主数据库spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.primary.username=rootspring.datasource.primary.password=123456
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver##配置次数据库
spring.datasource.secondary.jdbc-url=jdbc:oracle:thin:@localhost:1521:ORCL
spring.datasource.secondary.username=studyapp
spring.datasource.secondary.password=studyapppwd
spring.datasource.secondary.driver-class-name=oracle.jdbc.driver.OracleDriver

3.获取第一个数据库配置连接

我们都知道，mybatis不重写**@Configuration** 注解，mybatis默认会加载第一个数据源，在这里，需要让它读取application.properties里面第一个配置里面的数据。

1. 新建PrimaryDataSourceConfig

package com.example.demo.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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.one", sqlSessionFactoryRef = "PrimarySqlSessionFactory")//basePackages:接口文件的包路径
public class PrimaryDataSourceConfig {
//    @Bean
//    @ConfigurationProperties(prefix = "mybatis.configuration")
//    public  org.apache.ibatis.session.Configuration configuration(){
//
//        return new  org.apache.ibatis.session.Configuration();
//    }
    @Bean(name = "PrimaryDataSource")
    // 表示这个数据源是默认数据源
    @Primary//这个一定要加，如果两个数据源都没有@Primary会报错
    @ConfigurationProperties(prefix = "spring.datasource.primary")//我们配置文件中的前缀
    public DataSource getPrimaryDateSource() {
        return DataSourceBuilder.create().build();
    }


    @Bean(name = "PrimarySqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("PrimaryDataSource")
                                                                  DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/one/*.xml"));
        //开启sql转实体驼峰命名方式
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();// 设置mybatis的xml所在位置
    }


    @Bean("PrimarySqlSessionTemplate")
    // 表示这个数据源是默认数据源
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(
            @Qualifier("PrimarySqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }

}

说明: @MapperScan(basePackages = "com.example.demo.mapper.one" 设置mapper扫描包类 其中:**bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);**开启驼峰命名规则。经过测试发现application.properties配置：mybatis.configuration.map-underscore-to-camel-case=true并不生效。

  @Bean(name = "PrimarySqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("PrimaryDataSource")
                                                                  DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/one/*.xml"));
        //开启sql转实体驼峰命名方式
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();// 设置mybatis的xml所在位置
    }

2.新建第一个mapper.one

package com.example.demo.mapper.one;

import com.example.demo.bean.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.util.List;
@Component
@Mapper
public interface PrimaryUserMapper {
 List<User> findAll();
}

3.新建sql映射文件xxx.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.example.demo.mapper.one.PrimaryUserMapper">

    <select id="findAll" resultType="com.example.demo.bean.User">
        SELECT  * from sys_test
    </select>
</mapper>

4.获取第二个配置数据库连接信息

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.two", sqlSessionFactoryRef = "SecondarySqlSessionFactory")
public class SecondaryDataSourceConfig {

    @Bean(name = "SecondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource getSecondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "SecondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(
            @Qualifier("SecondaryDataSource") DataSource datasource
            )
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        // Configuration 设置配置属性
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/two/*.xml"));
        //开启sql转实体驼峰命名方式
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();// 设置mybatis的xml所在位置
    }

    @Bean("SecondarySqlSessionTemplate")
    public SqlSessionTemplate secondarySqlSessionTemplate(
            @Qualifier("SecondarySqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }


}

5.配置第二个mapper文件

package com.example.demo.mapper.two;


import com.example.demo.bean.User;
import com.example.demo.bean.UserVo;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@Mapper
public interface SecondaryUserMapper {
    List<UserVo> findAll();
}

6.配置第二个sql映射文件

<select id="findAll" resultType="com.example.demo.bean.UserVo">
            select  *  from TEST
</select>

7.新建Controller类

RestController
public class UserController extends BaseController {
    @Autowired
    private PrimaryUserMapper  primaryUserMapper;
    @Autowired
    private SecondaryUserMapper secondaryUserMapper;
    /**
     *    获取第一个数据库数据
     */
    @RequestMapping("primary")
    public TableDataInfo primary(){
        startPage();
        List<User> list = primaryUserMapper.findAll();
        return getDataTable(list);
    }
    /**
     *    获取第二个数据库数据
     */
    @RequestMapping("secondary")
    public Object secondary  (){
        List<UserVo> list = secondaryUserMapper.findAll();
        return list;
    }

}

end： 感谢其他大佬提供思路，本篇博客仅作为记录知识！！！！