Springboot项目的多数据源配置

spring boot项目配置多个数据源很常见！

话不多说，上代码。

首先先在system账号下创建了一个用户test1,并授予权限

create user test1 identified by 123456;
grant connect,resource to test1; 

接下来登录test1用户，创建一个表student

create table student
(
  id number primary key,
  name varchar2(30),
  address varchar2(100)
);

项目目录如下：

修改之前的配置文件

spring:
  datasource:
    driver-class-name: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521:ORCL
    username: test
    password: 123456

调整后多个数据源后的配置

spring:
  datasource:
    main:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      jdbc-url: jdbc:oracle:thin:@localhost:1521:ORCL
      username: test
      password: 123456
    ext:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      jdbc-url: jdbc:oracle:thin:@localhost:1521:ORCL
      username: test1
      password: 123456

主数据源配置：

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 javax.sql.DataSource;

/**
 * 主数据源配置
 */
@Configuration
public class MainDataSource {

    @Bean(name = "mainDataSources")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.main")
    public DataSource mainDataSource() {
        return DataSourceBuilder.create().build();
    }
}

主数据源的mybatis配置

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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;

/**
 * 主数据源的mybatis配置
 */
@Configuration
@MapperScan(basePackages = "com.example.demo.mapper", sqlSessionFactoryRef = "mainSqlSessionFactory", sqlSessionTemplateRef = "mainSqlSessionTemplate")
public class MainMybatisConfig {

    @Autowired
    private DataSource mainDataSources;

    @Bean(name = "mainSqlSessionFactory")
    @Primary
    public SqlSessionFactory mainSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(mainDataSources);
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }

    //配置声明式事务管理器
    @Bean(name = "mainTransactionManager")
    @Primary
    public PlatformTransactionManager mainTransactionManager() {
        return new DataSourceTransactionManager(mainDataSources);
    }

    @Bean(name = "mainSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate mainSqlSessionTemplate(
            @Qualifier("mainSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

副数据源配置：

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 javax.sql.DataSource;

/**
 * 副数据源配置
 */
@Configuration
public class ExtDataSource {

    @Bean(name = "extDataSources")
    @Qualifier(value = "extDataSources")
    @ConfigurationProperties(prefix = "spring.datasource.ext")
    public DataSource extDataSource() {
        return DataSourceBuilder.create().build();
    }
}

副数据源的mybatis配置：

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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;


/**
 * 副数据源的mybatis配置
 */
@Configuration
@MapperScan(basePackages = "com.example.demo.extMapper", sqlSessionFactoryRef = "extSqlSessionFactory", sqlSessionTemplateRef = "extSqlSessionTemplate")
public class ExtMybatisConfig {

    @Autowired
    @Qualifier(value = "extDataSources")
    private DataSource extDataSource;

    @Bean(name = "extSqlSessionFactory")
    public SqlSessionFactory extSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(extDataSource);
//        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:extMapper/*.xml"));
        return bean.getObject();
    }

    //配置声明式事务管理器
    @Bean(name = "extTransactionManager")
    public PlatformTransactionManager extTransactionManager() {
        return new DataSourceTransactionManager(extDataSource);
    }

    @Bean(name = "extSqlSessionTemplate")
    public SqlSessionTemplate extSqlSessionTemplate(
            @Qualifier("extSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

StudentMapper类

import com.example.demo.entity.Student;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface StudentMapper {

    List<Student> queryList();
}

StudentMapper.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.example.demo.extMapper.StudentMapper">
    <resultMap type="com.example.demo.entity.Student" id="Result">
        <result property="id" 			column="id"/>
        <result property="name" 		column="name"/>
        <result property="address"		column="address"/>
    </resultMap>
    <select id="queryList" resultMap="Result">
        select  id,name,address  from  student
    </select>
</mapper>

UserMapper文件

import com.example.demo.entity.User;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper {

    List<User> queryList();
}

UserMapper.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.example.demo.mapper.UserMapper">
    <resultMap type="com.example.demo.entity.User" id="userResult">
        <result property="id" 			column="id"/>
        <result property="name" 		column="name"/>
        <result property="age"		column="age"/>
    </resultMap>

    <select id="queryList" resultMap="userResult">
        select  id,name,age  from  "USER"
    </select>
</mapper>

controller类：

import com.example.demo.entity.Student;
import com.example.demo.entity.User;
import com.example.demo.extMapper.StudentMapper;
import com.example.demo.mapper.UserMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    private static final Logger logger = LoggerFactory.getLogger(UserController.class);

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private StudentMapper studentMapper;

    @PostMapping("/queryUserList")
    public List<User> queryUserList() {
        return userMapper.queryList();
    }

    @PostMapping("/queryStudentList")
    public List<Student> queryStudentList() {
        return studentMapper.queryList();
    }

}

此时查询结果：

副数据源：

    主数据源：

 若将副数据源的xml文件放到resources目录下

 此时 需要将副数据源的mybatis配置修改下：