springboot多数据源配置-通过SqlSessionFactory指定的数据源来操作指定目录的XML文件的方式（附带源码）

r囧r小猫 2023-09-27 10:24 134阅读 0赞

## 前言： ##

本文使用application或yml+配置类的方式，进行多数据源配置，与传统的xml方式相比，原理一致，但该方式更简便，上手难度低，避免的繁琐的xml配置，企业级开发一般使用该方式

[springboot传统的xml方式配置多数据源\_链接][springboot_xml]

以MySQL和Oracle两个数据库为例

## 1、准备工作 ##

### 1.1、项目中使用的基本清单 ###

*  springboot 2.3.7.RELEASE
 *  MySQL 5.7.38
 *  Oracle 19
 *  mybatis-plus

### 1.2、两种数据库分别新建两个测试表 ###

建表如下：

#### 1.2.1、MySQL建表及初始化数据语句 ####

CREATE TABLE `user`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `age` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    
    INSERT INTO `user` VALUES (1, '张三', 18);
    INSERT INTO `user` VALUES (2, '李四', 19);
    INSERT INTO `user` VALUES (3, '王五', 20);
    INSERT INTO `user` VALUES (4, '赵六', 19);
    INSERT INTO `user` VALUES (5, '老七', 17);
    INSERT INTO `user` VALUES (6, '芈八子', 18);
    INSERT INTO `user` VALUES (7, '久久', 99);

#### 1.2.2、Oracle建表及初始化数据语句 ####

--Oracle测试表
    CREATE TABLE city (
      id number(10)   not null,
      name varchar2(40),
      parent varchar2(40)
    );
    
    insert into city(id,name,parent) values(1,'广州','广东');
    insert into city(id,name,parent) values(1,'深圳','广东');
    insert into city(id,name,parent) values(1,'厦门','福建');
    insert into city(id,name,parent) values(1,'长沙','湖南');
    commit;

### 1.3、数据库基本信息如下 ###

<table> 
 <thead> 
  <tr> 
   <th>数据库类型</th> 
   <th>数据库名称</th> 
   <th>账号</th> 
   <th>密码</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MySQL</td> 
   <td>demo</td> 
   <td>root</td> 
   <td>root</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>demo2</td> 
   <td>root</td> 
   <td>123456</td> 
  </tr> 
 </tbody> 
</table>

## 2、搭建springboot项目 ##

这里我就略过了，我使用的版本为2.3.7.RELEASE

这里附上项目所需的maven依赖，如下（注意Druid与mybatis的版本）：

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.0</version>
    </dependency>
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.21</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.72</version>
    </dependency>
    <dependency>
        <groupId>com.google.code.gson</groupId>
        <artifactId>gson</artifactId>
        <version>2.8.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.1</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.48</version>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.4.0</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

## 3、配置开始 ##

### 3.1、配置两个数据源的链接信息 ###

在resource目录下，新建application.yml文件，并配置两个数据源的链接信息：

如下：

spring:
      datasource:
        #使用Druid数据源连接池
        type: com.alibaba.druid.pool.DruidDataSource
        #数据源01的连接信息
        db1:
         #存在多个 url时，必须使用jdbc-url来定义，否则连接不到数据库
         jdbc-url: jdbc:mysql://127.0.0.1:3306/demo
         username: root
         password: root
         driver-class-name: com.mysql.jdbc.Driver
        #数据源02的连接信息
        db2:
         jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:salescdh
         username: root
         password: 123456
         driver-class-name: oracle.jdbc.OracleDriver

### 3.2、生成配置类 ###

在与controller同层中，新建一个包，存放两个数据源对应的配置类

如下：

![2de7925119e148e2bc1c3839423ad0e2.png][]

内容如下：

####    3.2.1、DataSource01配置类： ####

package com.shuizhu.config;
    
    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.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;
    
    /**
     * 数据源01的配置类
     *
     * @author 睡竹
     * @date 2022/9/19
     */
    @Configuration
    @MapperScan(basePackages = "com.shuizhu.dao.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
    public class DataSource01 {
        @Primary
        @Bean(name = "db1DataSource")
        @ConfigurationProperties("spring.datasource.db1")
        public DataSource db1DataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "db1SqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            //bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResource("classpath:mapper/db1/Demo.xml"));
            PathMatchingResourcePatternResolver resource = new PathMatchingResourcePatternResolver();
            bean.setMapperLocations(resource.getResources("classpath:mapper/db1/*.xml"));
            return bean.getObject();
        }
    }

#### 3.2.2、DataSource02配置类： ####

package com.shuizhu.config;
    
    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.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;
    
    /**
     * 数据源02的配置类
     *
     * @author 睡竹
     * @date 2022/9/19
     */
    @Configuration
    @MapperScan(basePackages = "com.shuizhu.dao.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
    public class DataSource02 {
        @Primary
        @Bean(name = "db2DataSource")
        @ConfigurationProperties("spring.datasource.db2")
        public DataSource db2DataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "db2SqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db2/*.xml"));
            return bean.getObject();
        }
    }

### 3.3、生成xml映射文件 ###

如图所示：

db1文件夹表示数据源1的映射xml文件存放地；

db2文件夹表示数据源2的映射xml文件存放地。

分别在文件夹下新建xml映射文件：

![1ef1050c493048c5b91e89432ff0b921.png][]

手写测试的SQL代码：

Demo.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.shuizhu.dao.db1.DemoDao">
        <select id="getUsers" resultType="com.shuizhu.domain.User">
            select id,name,age from user
        </select>
    </mapper>

Demo2.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.shuizhu.dao.db2.DemoOracleDao">
        <select id="getCitys" resultType="com.shuizhu.domain.City">
            select id,name,parent from city
        </select>
    </mapper>

### 3.4、生成DAO接口层代码 ###

如图所示：

![8886814e56b941c790352299293353e0.png][]

测试代码如下：

DemoDao代码：

package com.shuizhu.dao.db1;
    
    import com.shuizhu.domain.User;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /**
     * MySQL数据源的dao
     * @author 睡竹
     * @date 2022/9/15
     */
    @Repository
    public interface DemoDao {
        //@Select("select id,name,age from user")
        List<User> getUsers();
    }

DemoOracleDao代码：

package com.shuizhu.dao.db2;
    
    import com.shuizhu.domain.City;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /**
     * Oracle数据源的dao
     * @author 睡竹
     * @date 2022/9/15
     */
    @Repository
    public interface DemoOracleDao {
        List<City> getCitys();
    }

### 3.5、在启动类上，添加注解属性 ###

如图所示：

![3c8d22af31d14ed381bfaf3c98158641.png][]

## 4、测试 ##

#### 4.1、Controller层： ####

package com.shuizhu.controller;
    
    import com.shuizhu.domain.City;
    import com.shuizhu.domain.User;
    import com.shuizhu.service.IDemoService;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.annotation.Resource;
    import java.util.List;
    
    /**
     * @author 睡竹
     * @date 2022/9/15
     */
    @RestController
    public class DemoController {
    
        @Resource
        private IDemoService service;
    
        /**
         * 测试MySQL数据源是否生效
         * @return
         */
        @RequestMapping("testMySQL")
        public Object testMySQL(){
            List<User> users = service.getUsers();
            return users;
        }
    
        /**
         * 测试Oracle数据源是否生效
         * @return
         */
        @RequestMapping("testOracle")
        public Object testOracle(){
            List<City> users = service.getCitys();
            return users;
        }
    }

#### 4.2、Service接口层： ####

package com.shuizhu.service;
    
    import com.shuizhu.domain.City;
    import com.shuizhu.domain.User;
    
    import java.util.List;
    
    /**
     * @author 睡竹
     * @date 2022/9/15
     */
    public interface IDemoService {
        List<User> getUsers();
        List<City> getCitys();
    }

#### 4.3、Service接口实现层： ####

package com.shuizhu.service.impl;
    
    import com.shuizhu.dao.mysql.DemoDao;
    import com.shuizhu.dao.oracle.DemoOracleDao;
    import com.shuizhu.domain.City;
    import com.shuizhu.domain.User;
    import com.shuizhu.service.IDemoService;
    import lombok.RequiredArgsConstructor;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * 偷懒了，直接写入到同一个service
     * @author 睡竹
     * @date 2022/9/15
     */
    @Service
    @RequiredArgsConstructor
    public class DemoService implements IDemoService {
    
        //MySQL的Dao构造器注入
        final DemoDao dao1;
        //Oracle的Dao构造器注入
        final DemoOracleDao dao2;
    
        @Override
        public List<User> getUsers() {
            List<User> users = dao1.getUsers();
            return users;
        }
    
        @Override
        public List<City> getCitys() {
            List<City> citys = dao2.getCitys();
            return citys;
        }
    }

#### 4.4、Domain实体类层： ####

**User实体类：**

package com.shuizhu.domain;
    
    import lombok.Data;
    import java.io.Serializable;
    /**
     * MySQL库user表实体类
     * @author 睡竹
     * @date 2022/9/15
     */
    @Data
    public class User implements Serializable {
        private static final long serialVersionUID = 1L;
    
        private Integer id;
        private String name;
        private Integer age;
    }

**City实体类：**

package com.shuizhu.domain;
    
    import lombok.Data;
    import java.io.Serializable;
    /**
     * Oracle库city表实体类
     * @author 睡竹
     * @date 2022/9/16
     */
    @Data
    public class City implements Serializable {
        private static final long serialVersionUID = 1L;
        private Integer id;
        private String name;
        private String parent;
    }

**至此，测试流程及代码都已实现**

**附上整个springboot项目完整目录结构如：**

![72cd31bb33144e95b8abba551fbd9181.png][]

## 5、使用api工具测试接口 ##

使用api工具对接口进行测试，检验数据源是否生效

### 5.1、测试MySQL数据的接口 ###

请求路径：http://127.0.0.1:9080/testMySQL

![391a97c91af94aaf8390572c245e601e.png][]

### 5.2、 测试Oracle数据的接口 ###

请求路径：http://127.0.0.1:9080/testOracle

![bb2aa6c3ac434e1fa3d2e49fdbf03f80.png][]

**测试成功，多数据源配置生效**

案例代码：[shuizhu-multiple-ds: 多数据源配置-通过SqlSessionFactory指定的数据源来操作指定目录的XML文件的方式![favicon-9007bd527d8a7851c8330e783151df58.ico][]https://gitee.com/ct668/shuizhu-multiple-ds][shuizhu-multiple-ds_ _-_SqlSessionFactory_XML_favicon-9007bd527d8a7851c8330e783151df58.ico_https_gitee.com_ct668_shuizhu-multiple-ds]

[springboot_xml]: https://blog.csdn.net/weixin_42675423/article/details/126888886
[2de7925119e148e2bc1c3839423ad0e2.png]: https://img-blog.csdnimg.cn/2de7925119e148e2bc1c3839423ad0e2.png
[1ef1050c493048c5b91e89432ff0b921.png]: https://img-blog.csdnimg.cn/1ef1050c493048c5b91e89432ff0b921.png
[8886814e56b941c790352299293353e0.png]: https://img-blog.csdnimg.cn/8886814e56b941c790352299293353e0.png
[3c8d22af31d14ed381bfaf3c98158641.png]: https://img-blog.csdnimg.cn/3c8d22af31d14ed381bfaf3c98158641.png
[72cd31bb33144e95b8abba551fbd9181.png]: https://img-blog.csdnimg.cn/72cd31bb33144e95b8abba551fbd9181.png
[391a97c91af94aaf8390572c245e601e.png]: https://img-blog.csdnimg.cn/391a97c91af94aaf8390572c245e601e.png
[bb2aa6c3ac434e1fa3d2e49fdbf03f80.png]: https://img-blog.csdnimg.cn/bb2aa6c3ac434e1fa3d2e49fdbf03f80.png
[favicon-9007bd527d8a7851c8330e783151df58.ico]: https://cn-assets.gitee.com/assets/favicon-9007bd527d8a7851c8330e783151df58.ico
[shuizhu-multiple-ds_ _-_SqlSessionFactory_XML_favicon-9007bd527d8a7851c8330e783151df58.ico_https_gitee.com_ct668_shuizhu-multiple-ds]: https://gitee.com/ct668/shuizhu-multiple-ds