springboot整合mybatis配置多数据源（mysql/oracle）_tk.mybatis springboot3

前言

springboot整合mybatis配置多数据源，可以都是mysql数据源，也可以都是oracle数据源，也可以mysql/oracle数据源都有，下面是配置多数据源的步骤（不局限与某一类数据库），之前配过都是oracle数据源的配置，下面是验证这个想法的实现，实现效果后，好像也可以同时配MongoDB、Redis等其他的数据源配置，但是好像也没碰到有人在一个项目里面这么干过…要么都是mysql数据源要么都是oracle数据源。

导入依赖坐标

<dependencies>
    <!--springboot起步依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <version>${spring-boot.version}</version>
    </dependency>
    <!-- spring-boot-starter-web  -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!--单元测试 包含junit-jupiter-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <!--springboot整合mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>${mybatis.version}</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter-test</artifactId>
        <version>${mybatis.version}</version>
        <scope>test</scope>
    </dependency>
    <!--添加tk.mybatis用于对单表进行处理-->
    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>${tk.mybatis.version}</version>
    </dependency>
    <!-- oracle驱动 -->
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>${oracle.version}</version>
    </dependency>
    <!-- mysql驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.version}</version>
    </dependency>
    <!-- alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>${druid.version}</version>
    </dependency>
    <!--c3p0连接池-->
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>${c3p0.version}</version>
    </dependency>
</dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

创建mysql/oracle数据源配置类

MySQLDataSourceConfig

@Configuration
@MapperScan(basePackages = "com.example.dao.mysql", sqlSessionFactoryRef = "mysqlSessionFactory")
public class MySQLDataSourceConfig {
    @Primary // 表示这个数据源是默认数据源, 这个注解必须要加，因为不加的话spring将分不清楚那个为主数据源（默认数据源）
    @Bean("mysqlDataSource")
    //方式一：@ConfigurationProperties(prefix = "spring.datasource.mysql")
    @ConfigurationProperties(prefix = "spring.datasource.mysql") //读取application.yml中的配置参数映射成为一个对象
    public DataSource getMysqlDataSource() {
        return DataSourceBuilder.create().build();
    }
    //方式二：@Value注解方法 set方法注入yml数据源属性
    /*@Value("${spring.datasource.mysql.driver-class-name}")
    String driverClass;
    @Value("${spring.datasource.mysql.jdbc-url}")
    String url;
    @Value("${spring.datasource.mysql.username}")
    String userName;
    @Value("${spring.datasource.mysql.password}")
    String passWord;
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties("spring.datasource.mysql")
    public DataSource masterDataSource(){
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClass(driverClass);
        dataSource.setJdbcUrl(url);
        dataSource.setUser(userName);
        dataSource.setPassword(passWord);
        return dataSource;
    }*/


    @Primary
    @Bean("mysqlSessionFactory")
    public SqlSessionFactory mysqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/*.xml")); // 持久化.xml文件的地址
        return bean.getObject();
    }

    @Primary
    @Bean("mysqlSessionTemplate")
    public SqlSessionTemplate mysqlSessionTemplate(@Qualifier("mysqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

OracleDataSourceConfig

@Configuration
@MapperScan(basePackages = "com.example.dao.oracle", sqlSessionFactoryRef = "oracleSessionFactory")
public class OracleDataSourceConfig {
    @Bean("oracleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.oracle") //读取application.yml中的配置参数映射成为一个对象
    public DataSource getOracleDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("oracleSessionFactory")
    public SqlSessionFactory oracleSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/oracle/*.xml")); // 持久化.xml文件的地址
        return bean.getObject();
    }

    @Bean("oracleSessionTemplate")
    public SqlSessionTemplate oracleSessionTemplate(@Qualifier("oracleSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

application.yml配置文件配置mysql/oracle数据源

spring:
  # 配置数据源
  datasource:
    #单一数据源配置
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://rhel:3306/note_boot
    username: root
    password: Redhat1

    #多数据源配置
    mysql:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://rhel:3306/note_boot
      username: root
      password: Redhat1
    oracle:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@//rhel:1521/orcl
      username: testdb
      password: oraclepass
      hikari:
        connection-timeout: 3000
        idle-timeout: 60000
        maximum-pool-size: 50
        minimum-idle: 5
        connection-test-query: select 1 from dual
        validation-timeout: 2000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

编写Mapper接口

//BookDao里面都是两个简单查询测试方法，注解实现...
//mysql数据库测试
public Book getById(Integer id);
//oracle数据库测试
public Book findById(Integer id);
1
2
3
4
5

编写Book实体类

public class Book {
    private Integer id;
    private String name;
    private String type;
    private String description;
    //get/set方法省略...
}
1
2
3
4
5
6
7

编写测试类

public class MybatisDemo {
    @Autowired
    private BookDao bookDao;
    @Autowired
    private BookDao1 bookDao1;
    @Autowired
    private BookDao2 bookDao2;

    @Test
    void test() {
        //原始配置，单一数据库查询
        Book book = bookDao.getById(1);
        System.out.println(book);
        //多数据源配置 mysql查询
        Book book1 = bookDao1.getById(1);
        System.out.println(book1);
        //多数据源配置 oracle查询
        Book book2 = bookDao2.findById(1);
        System.out.println(book2);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

效果图：