springBoot+mybatis+多数据源（oracle+mysql）

小咪咪 2022-11-18 13:44 169阅读 0赞

**目录**

一、说明

二、具体实现

1、制造数据源数据

1）oracle数据库

2）mysql数据库

2、整体项目位置

3、配置文件—application.properties

4、FirstDataSourceConfig

5、SecondDataSourceConfig

6、po类

7、xml

8、mapper接口

9、service层

10、controller层

三、测试

四、经验收获

--------------------

# 一、说明 #

本篇文章主要解决springBoot+mybatis读取多数据源问题，这里配置了两个数据源（oracle、mysql），分别从这个两个数据库中读取想要得到数据；这里需要在配置文件配置两个数据源的连接信息；

1、需要将springboot自动配置数据源的功能给去掉：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

2、不需要在配置文件这样配置mapper位置了，需要在代码中指定：

# mybatis配置
    mybatis.mapper-locations=classpath:com/*/*/mapping/*.xml

# 二、具体实现 #

## 1、制造数据源数据 ##

### 1）oracle数据库 ###

create table SYSTEM.STUDENT
    (
      name VARCHAR2(12),
      age  NUMBER,
      sex  VARCHAR2(2)
    );
    
    insert into student(name,age,sex) values('lisi',20,'男');
    insert into student(name,age,sex) values('wangli',34,'女');

### 2）mysql数据库 ###

CREATE TABLE `lesson`(
      `id` VARCHAR(12) PRIMARY KEY,
      `name` VARCHAR(36) NOT NULL ,
      `credit` FLOAT  DEFAULT 0,
      `teacher` VARCHAR(36) DEFAULT 0
    );
    
    INSERT INTO lesson (id, name, credit, teacher) VALUES ('1', 'chiness', 5, 'zhangsan');
    INSERT INTO lesson (id, name, credit, teacher) VALUES ('2', 'math', 5, 'lisi');

## 2、整体项目位置 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5al9raW5n_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5al9raW5n_size_16_color_FFFFFF_t_70 1][]

## 3、配置文件—application.properties ##

#设置静态资源路径
    spring.thymeleaf.prefix=classpath:/templates/
    spring.resources.static-locations=classpath:static/,file:static/
    server.port=8093
    
    spring.message.encoding=UTF-8
    
    spring.datasource.first.url= jdbc:oracle:thin:@127.0.0.1:1521:orcl
    spring.datasource.first.username= system
    spring.datasource.first.password= 123456
    spring.datasource.first.driverClassName=oracle.jdbc.driver.OracleDriver
    
    spring.datasource.second.url=jdbc:mysql://192.168.230.21:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    spring.datasource.second.username= root
    spring.datasource.second.password=123456
    spring.datasource.second.driverClassName=com.mysql.jdbc.Driver

## 4、FirstDataSourceConfig ##

package com.springboot.manydata.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.beans.factory.annotation.Value;
    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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = "com.springboot.manydata.mapper.first",sqlSessionTemplateRef ="firstSqlSessionTemplate")
    public class FirstDataSourceConfig {
        @Value("${spring.datasource.first.url}")
        private String url;
        @Value("${spring.datasource.first.username}")
        private String username;
        @Value("${spring.datasource.first.password}")
        private String password;
        @Value("${spring.datasource.first.driverClassName}")
        private String driverClassName;
        /**本数据源扫描的mapper路径*/
        static final String MAPPER_LOCATION = "classpath:mapping/first/*.xml";
    
    
        /**创建数据源*/
        @Bean(name = "firstDS")
        @Primary
        public DataSource getFirstDataSource() {
            DataSource build =  DataSourceBuilder.create()
                    .driverClassName(driverClassName)
                    .url(url)
                    .username(username)
                    .password(password)
                    .build();
            return build;
        }
    
    
        /**创建SessionFactory*/
        @Bean(name = "firstSqlSessionFactory")
        @Primary
        public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDS") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            //设置mapper配置文件
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
            return bean.getObject();
        }
    
        /**创建事务管理器*/
        @Bean("firstTransactionManger")
        @Primary
        public DataSourceTransactionManager firstTransactionManger(@Qualifier("firstDS") DataSource dataSource){
            return new DataSourceTransactionManager(dataSource);
        }
    
        /**创建SqlSessionTemplate*/
        @Bean(name = "firstSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

## 5、SecondDataSourceConfig ##

package com.springboot.manydata.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.beans.factory.annotation.Value;
    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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = "com.springboot.manydata.mapper.second",sqlSessionTemplateRef ="secondSqlSessionTemplate")
    public class SecondDataSourceConfig  {
        @Value("${spring.datasource.second.url}")
        private String url;
        @Value("${spring.datasource.second.username}")
        private String username;
        @Value("${spring.datasource.second.password}")
        private String password;
        @Value("${spring.datasource.second.driverClassName}")
        private String driverClassName;
        /**本数据源扫描的mapper路径*/
        static final String MAPPER_LOCATION = "classpath:mapping/second/*.xml";
    
        /**创建数据源*/
        @Bean(name = "secondDS")
        public DataSource getSecondDataSource() {
            DataSource build =  DataSourceBuilder.create()
                    .driverClassName(driverClassName)
                    .url(url)
                    .username(username)
                    .password(password)
                    .build();
            return build;
        }
    
    
        /**创建SessionFactory*/
        @Bean(name = "secondSqlSessionFactory")
        public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDS") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            //设置mapper配置文件
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
            return bean.getObject();
        }
    
        /**创建事务管理器*/
        @Bean("secondTransactionManger")
        public DataSourceTransactionManager secondTransactionManger(@Qualifier("secondDS") DataSource dataSource){
            return new DataSourceTransactionManager(dataSource);
        }
    
        /**创建SqlSessionTemplate*/
        @Bean(name = "secondSqlSessionTemplate")
        public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

## 6、po类 ##

package com.springboot.manydata.po;
    
    import lombok.Data;
    import lombok.experimental.Accessors;
    
    @Data
    @Accessors(chain = true)
    public class Lesson {
        private String id;
        private String name;
        private String teacher;
        private  float credit;
    }

package com.springboot.manydata.po;
    
    import lombok.Data;
    import lombok.experimental.Accessors;
    
    @Data
    @Accessors(chain = true)
    public class Student {
        private String name;
        private int age;
        private String sex;
    }

## 7、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.springboot.manydata.mapper.first.StudentMapper">
        <resultMap id="studentMap" type="com.springboot.manydata.po.Student">
            <result column="name" property="name" />
            <result column="age" property="age" />
            <result column="sex" property="sex" />
        </resultMap>
    
        <select id="getStudentInfo" resultMap="studentMap">
          select * from student
        </select>
    
    </mapper>

<?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.springboot.manydata.mapper.second.LessonMapper">
        <resultMap id="lessonMap" type="com.springboot.manydata.po.Lesson">
            <result column="id" property="id" />
            <result column="name" property="name" />
            <result column="credit" property="credit" />
            <result column="teacher" property="teacher" />
        </resultMap>
    
        <select id="getLessonInfo" resultMap="lessonMap">
          select * from lesson;
        </select>
    
    </mapper>

## 8、mapper接口 ##

public interface StudentMapper {
        List<Student> getStudentInfo();
    }

public interface LessonMapper {
        List<Lesson> getLessonInfo();
    }

## 9、service层 ##

@Service
    public class StudentService implements StudentMapper {
        @Autowired
        StudentMapper studentMapper;
        @Override
        public List<Student> getStudentInfo() {
            return studentMapper.getStudentInfo();
        }
    }

@Service
    public class LessonService implements LessonMapper {
        @Autowired
        LessonMapper lessonMapper;
        @Override
        public List<Lesson> getLessonInfo() {
            return lessonMapper.getLessonInfo();
        }
    }

## 10、controller层 ##

@Controller
    @RequestMapping("/student")
    public class StudentController {
        @Autowired
        StudentService studentService;
        @RequestMapping("/getInfo")
        @ResponseBody
        public List<Student> getStudentInfo(){
            List<Student> studentInfo = studentService.getStudentInfo();
            return studentInfo;
        }
    }

@Controller
    @RequestMapping("/lesson")
    public class LessonController {
        @Autowired
        LessonService lessonService;
        @RequestMapping("/getInfo")
        @ResponseBody
        public List<Lesson>  getStudentInfo(){
            List<Lesson> lessonInfo = lessonService.getLessonInfo();
            return lessonInfo;
        }
    }

# 三、测试 #

启动程序，在页面输入路径测试成功！

![20210413143552712.png][]

![20210413143624140.png][]

# 四、经验收获 #

mybatis+oracle在xml中书写的sql不能带最后的分号，否则会报 ORA-00911: 无效字符；

mybatis+mysql在xml中书写的sql可以带最后的分号；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5al9raW5n_size_16_color_FFFFFF_t_70]: /images/20221022/bde3f304465d4dd3b0b8e123a3c0a6d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5al9raW5n_size_16_color_FFFFFF_t_70 1]: /images/20221022/ed312a2af5964591bfa1df154a0367b7.png
[20210413143552712.png]: /images/20221022/c00269a449454f0e86a0e198489b6106.png
[20210413143624140.png]: /images/20221022/32817c56619d4db8b5aad0bd9f27f48f.png