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

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.0</modelVersion>
5
    <parent>
6
        <groupId>org.springframework.boot</groupId>
7
        <artifactId>spring-boot-starter-parent</artifactId>
8
        <version>2.4.5</version>
9
        <relativePath/> <!-- lookup parent from repository -->
10
    </parent>
11
    <groupId>com.llm</groupId>
12
    <artifactId>dynamic</artifactId>
13
    <version>0.0.1-SNAPSHOT</version>
14
    <name>dynamic</name>
15
    <description>Demo project for Spring Boot</description>
16
    <properties>
17
        <java.version>1.8</java.version>
18
    </properties>
19
    <dependencies>
20
        <dependency>
21
            <groupId>org.springframework.boot</groupId>
22
            <artifactId>spring-boot-starter-web</artifactId>
23
        </dependency>
24
        <dependency>
25
            <groupId>org.mybatis.spring.boot</groupId>
26
            <artifactId>mybatis-spring-boot-starter</artifactId>
27
            <version>2.1.1</version>
28
        </dependency>
29
​
30
        <dependency>
31
            <groupId>com.oracle.database.jdbc</groupId>
32
            <artifactId>ojdbc8</artifactId>
33
            <scope>runtime</scope>
34
        </dependency>
35
        <dependency>
36
            <groupId>mysql</groupId>
37
            <artifactId>mysql-connector-java</artifactId>
38
            <scope>runtime</scope>
39
        </dependency>
40
        <dependency>
41
            <groupId>org.springframework.boot</groupId>
42
            <artifactId>spring-boot-starter-test</artifactId>
43
            <scope>test</scope>
44
        </dependency>
45
​
46
        <dependency>
47
            <groupId>com.alibaba</groupId>
48
            <artifactId>druid</artifactId>
49
            <version>1.1.10</version>
50
        </dependency>
51
        <dependency>
52
            <groupId>com.alibaba</groupId>
53
            <artifactId>fastjson</artifactId>
54
            <version>1.2.40</version>
55
        </dependency>
56
        <dependency>
57
            <groupId>com.vaadin.external.google</groupId>
58
            <artifactId>android-json</artifactId>
59
            <version>0.0.20131108.vaadin1</version>
60
            <scope>compile</scope>
61
        </dependency>
62
        
63
        <!-- 报错：java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK -->
64
        <dependency>
65
            <groupId>cn.easyproject</groupId>
66
            <artifactId>orai18n</artifactId>
67
            <version>12.1.0.2.0</version>
68
        </dependency>
69
    </dependencies>
70
​
71
    <build>
72
        <plugins>
73
            <plugin>
74
                <groupId>org.springframework.boot</groupId>
75
                <artifactId>spring-boot-maven-plugin</artifactId>
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
}
 
 

 

  1. 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
​
 
 
  1. 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
}
 
 
  1. 处理器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
}
 
 

 

七、访问

如下所示，访问成功！

 

posted on 2021-05-09 19:12  Windy心梦无痕  阅读(2588)  评论(0)  编辑  收藏  举报