SpringBoot + Mybatis 多数据源（MySQL、Oracle）完整版（注解）

一、创建SpringBoot项目

使用IDEA创建SpringBoot项目，勾选如下：

二、pom.xml

      1

<?xml version="1.0" encoding="UTF-8"?>

2

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

3

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4

    <modelVersion>4.0.0modelVersion>

5

    <parent>

6

        <groupId>org.springframework.bootgroupId>

7

        <artifactId>spring-boot-starter-parentartifactId>

8

        <version>2.4.5version>

9

        <relativePath/> 

10

    parent>

11

    <groupId>com.llmgroupId>

12

    <artifactId>dynamicartifactId>

13

    <version>0.0.1-SNAPSHOTversion>

14

    <name>dynamicname>

15

    <description>Demo project for Spring Bootdescription>

16

    <properties>

17

        <java.version>1.8java.version>

18

    properties>

19

    <dependencies>

20

        <dependency>

21

            <groupId>org.springframework.bootgroupId>

22

            <artifactId>spring-boot-starter-webartifactId>

23

        dependency>

24

        <dependency>

25

            <groupId>org.mybatis.spring.bootgroupId>

26

            <artifactId>mybatis-spring-boot-starterartifactId>

27

            <version>2.1.1version>

28

        dependency>

29

?

30

        <dependency>

31

            <groupId>com.oracle.database.jdbcgroupId>

32

            <artifactId>ojdbc8artifactId>

33

            <scope>runtimescope>

34

        dependency>

35

        <dependency>

36

            <groupId>mysqlgroupId>

37

            <artifactId>mysql-connector-javaartifactId>

38

            <scope>runtimescope>

39

        dependency>

40

        <dependency>

41

            <groupId>org.springframework.bootgroupId>

42

            <artifactId>spring-boot-starter-testartifactId>

43

            <scope>testscope>

44

        dependency>

45

?

46

        <dependency>

47

            <groupId>com.alibabagroupId>

48

            <artifactId>druidartifactId>

49

            <version>1.1.10version>

50

        dependency>

51

        <dependency>

52

            <groupId>com.alibabagroupId>

53

            <artifactId>fastjsonartifactId>

54

            <version>1.2.40version>

55

        dependency>

56

        <dependency>

57

            <groupId>com.vaadin.external.googlegroupId>

58

            <artifactId>android-jsonartifactId>

59

            <version>0.0.20131108.vaadin1version>

60

            <scope>compilescope>

61

        dependency>

62

63

64

        <dependency>

65

            <groupId>cn.easyprojectgroupId>

66

            <artifactId>orai18nartifactId>

67

            <version>12.1.0.2.0version>

68

        dependency>

69

    dependencies>

70

?

71

    <build>

72

        <plugins>

73

            <plugin>

74

                <groupId>org.springframework.bootgroupId>

75

                <artifactId>spring-boot-maven-pluginartifactId>

76

            plugin>

77

        plugins>

78

    build>

79

?

80

project>

   

三、项目配置文件

四、application.yml

spring.datasource.url数据库的JDBC URL

spring.datasource.jdbc-url用来重写自定义连接池

Hikari没有url属性，但是有jdbcUrl属性，在这中情况下必须使用jdbc_url

      1

server:                                                     

2

  port: 8081                                                

3

4

spring:                                                     

5

  datasource:                                               

6

    primary:                                                

7

      jdbc-url: jdbc:mysql://localhost:3306/dynamic_data    

8

      username: root                                        

9

      password: 123456                                      

10

      driver-class-name: com.mysql.cj.jdbc.Driver           

11

12

    secondary:                                              

13

      jdbc-url: jdbc:oracle:thin:@localhost:1521/ORCL       

14

      username: c##luliming                                 

15

      password: 123456                                      

16

      driver-class-name: oracle.jdbc.driver.OracleDriver    

   

五、代码实现

1. MySQL配置类实现

   使用注解@Primary配置默认数据源：

      1

package com.llm.config.mysqlconfig;

2

?

3

import org.apache.ibatis.session.SqlSessionFactory;

4

import org.mybatis.spring.SqlSessionFactoryBean;

5

import org.mybatis.spring.annotation.MapperScan;

6

import org.springframework.beans.factory.annotation.Qualifier;

7

import org.springframework.boot.context.properties.ConfigurationProperties;

8

import org.springframework.boot.jdbc.DataSourceBuilder;

9

import org.springframework.context.annotation.Bean;

10

import org.springframework.context.annotation.Configuration;

11

import org.springframework.context.annotation.Primary;

12

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

13

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

14

?

15

import javax.sql.DataSource;

16

?

17

@Configuration

18

@MapperScan(basePackages = MysqlDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")

19

public class MysqlDataSourceConfig {

20

    static final String PACKAGE = "com.llm.mapper.mysqlMapper";

21

?

22

    static final String MAPPER_LOCATION = "classpath*:mapper/mysqlmapper/*.xml";

23

?

24

    @Primary

25

    @Bean(name = "mysqlDataSource")

26

    @ConfigurationProperties(prefix = "spring.datasource.primary")

27

    public DataSource mysqlDataSource() {

28

        return DataSourceBuilder.create().build();

29

    }

30

?

31

    @Primary

32

    @Bean(name = "mysqlTransactionManager")

33

    public DataSourceTransactionManager mysqlTransactionManager() {

34

        return new DataSourceTransactionManager((mysqlDataSource()));

35

    }

36

?

37

    @Primary

38

    @Bean(name = "mysqlSqlSessionFactory")

39

    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource mysqlDatasource) throws Exception {

40

        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

41

        sessionFactory.setDataSource(mysqlDatasource);

42

        sessionFactory.setMapperLocations(

43

                new PathMatchingResourcePatternResolver().getResources(MysqlDataSourceConfig.MAPPER_LOCATION)

44

        );

45

        return sessionFactory.getObject();

46

    }

47

}

   

2. Oracle配置类实现

      1

package com.llm.config.oracleconfig;

2

?

3

import org.apache.ibatis.session.SqlSessionFactory;

4

import org.mybatis.spring.SqlSessionFactoryBean;

5

import org.mybatis.spring.annotation.MapperScan;

6

import org.springframework.beans.factory.annotation.Qualifier;

7

import org.springframework.boot.context.properties.ConfigurationProperties;

8

import org.springframework.boot.jdbc.DataSourceBuilder;

9

import org.springframework.context.annotation.Bean;

10

import org.springframework.context.annotation.Configuration;

11

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

12

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

13

?

14

import javax.sql.DataSource;

15

?

16

@Configuration

17

@MapperScan(basePackages = OracleDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "oracleSqlSessionFactory")

18

public class OracleDataSourceConfig {

19

?

20

    static final String PACKAGE = "com.llm.mapper.oracleMapper";

21

    static final String MAPPER_LOCATION = "classpath*:mapper/oraclemapper/*.xml";

22

?

23

    @Bean(name = "oracleDataSource")

24

    @ConfigurationProperties(prefix = "spring.datasource.secondary")

25

    public DataSource oracleDataSource() {

26

        return DataSourceBuilder.create().build();

27

    }

28

?

29

    @Bean(name = "oracleTransactionManager")

30

    public DataSourceTransactionManager oracleTransactionManager() {

31

        return new DataSourceTransactionManager(oracleDataSource());

32

    }

33

?

34

    @Bean(name = "oracleSqlSessionFactory")

35

    public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource oracleDataSource) throws Exception {

36

        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

37

        sessionFactory.setDataSource(oracleDataSource);

38

        sessionFactory.setMapperLocations(

39

                new PathMatchingResourcePatternResolver().getResources(OracleDataSourceConfig.MAPPER_LOCATION)

40

        );

41

        return sessionFactory.getObject();

42

    }

43

}

   

六、测试

1. MySQL和Oracle中各建一张表，这里都为 USER

   Oracle：

　　MySQL：

 　2. UserMysqlMapper

      1

package com.llm.mapper.mysqlMapper;

2

?

3

import com.llm.entity.User;

4

import org.apache.ibatis.annotations.*;

5

import org.springframework.stereotype.Service;

6

?

7

import java.util.List;

8

?

9

@Service

10

@Mapper

11

public interface UserMysqlMapper {

12

?

13

    @Select("SELECT * FROM USER")

14

    List<User> findAll();

15

?

16

    @Insert("INSERT INTO USER(username, age) VALUES(#{username}, #{age})")

17

    void addOne(User user);

18

?

19

    @Select("SELECT * FROM USER WHERE id = #{id}")

20

    User findOne(Integer id);

21

?

22

    @Update("UPDATE USER SET username = #{username}, age = #{age}")

23

    void updateOne(User user);

24

?

25

    @Delete("DELETE FROM USER WHERE id = #{id}")

26

    void delOne(Integer id);

27

}

28

?

   

3. UserOracleMapper

      1

package com.llm.mapper.oracleMapper;

2

?

3

import com.llm.entity.User;

4

import org.apache.ibatis.annotations.*;

5

import org.springframework.stereotype.Service;

6

?

7

import java.util.List;

8

?

9

@Service

10

@Mapper

11

public interface UserOracleMapper {

12

?

13

    @Select("SELECT * FROM \"USER\"")

14

    List<User> findAll();

15

?

16

    @Insert("INSERT INTO \"USER\" (username, age) VALUES (#{username}, #{age})")

17

    void addOne(User user);

18

?

19

    @Select("SELECT * FROM \"USER\" WHERE id = #{id}")

20

    User findOne(Integer id);

21

?

22

    @Update("UPDATE \"USER\" SET username = #{username}, age = #{age}")

23

    void updateOne(User user);

24

?

25

    @Delete("DELETE FROM \"USER\" WHERE id = #{id}")

26

    void delOne(Integer id);

27

}

   

4. 处理器Controller

      1

package com.llm.controller;

2

?

3

import com.alibaba.fastjson.JSONObject;

4

import com.llm.entity.User;

5

import com.llm.mapper.mysqlMapper.UserMysqlMapper;

6

import com.llm.mapper.oracleMapper.UserOracleMapper;

7

import org.springframework.beans.factory.annotation.Autowired;

8

import org.springframework.web.bind.annotation.GetMapping;

9

import org.springframework.web.bind.annotation.RestController;

10

?

11

import java.io.IOException;

12

import java.util.List;

13

?

14

@RestController

15

public class MainController {

16

?

17

    @Autowired

18

    private UserMysqlMapper userMysqlMapper;

19

?

20

    @Autowired

21

    private UserOracleMapper userOracleMapper;

22

?

23

    @GetMapping("/all")

24

    public Object findAll() throws IOException {

25

        List<User> usersMysql = userMysqlMapper.findAll();

26

        List<User> usersOracle = userOracleMapper.findAll();

27

        usersMysql.addAll(usersOracle);

28

        JSONObject jsonObject = new JSONObject();

29

        jsonObject.put("data", usersMysql);

30

        return usersMysql;

31

    }

32

}

   

七、访问

如下所示，访问成功！