SpringBoot+Mybatis+多数据源应用

应用场景

大多数时候，项目的架构是SpringBoot+Mybatis+Mysql，或者SpringBoot+Mybatis+Pgsql。有时我们我应用场景是：在一项目中同时连接Mysql和Pgsql等多个数据源，这种情况如何实现呢？

实现原理

通过Mybatis的注解@MapperScan，配置basePackages和sqlSessionFactoryRef，basePackages值为mapping的扫描路径，sqlSessionFactoryRef值为数据源的bean。只有一个数据源时不需要配置，有多个数据源时需要配置，当调用basePackages1下的mapping接口时，会根据此配置找到sqlSessionFactoryRef对应的数据源1，而调用basePackages2下的mapping接口时，会找到sqlSessionFactoryRef对应的数据源2。这样就实现了不同数据源的数据查询

举例验证

实现两个数据源mysql、pgsql的应用

工程结构：

pom引入

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>

application.yml配置

mysql和pgsql的数据源配置

spring:
  datasource:
    mysql:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: password
      jdbc-url: jdbc:mysql://hostIp:port/database
      connection-test-query: select 1
    pg:
      driver-class-name: org.postgresql.Driver
      username: postgres
      password: postgres
      jdbc-url: jdbc:postgresql://hostIp:port/database

多个数据源配置文件

定义不同数据源bean，指定调用mapper时获取哪个数据源

//mysql数据源配置
@Configuration
@MapperScan(basePackages = "com.test.mapper.mysql",sqlSessionFactoryRef="mysqlSqlSessionFactory")
public class MysqlConfig {

    //数据源bean,@ConfigurationProperties指定数据源属性注入的配置
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties("spring.datasource.mysql")
    public DataSource masterDataSource(){
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        return  dataSourceBuilder.build();
    }

    //sqlSessionFactory的bean,指定数据源bean
    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        //设置mybatis下mysql的xml文件路径
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/mysql/*.xml"));

        //配置是否控制台打印sql语句
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setLogImpl(StdOutImpl.class);
        return sessionFactoryBean.getObject();
    }
}

//pgsql数据源配置
@Configuration
@MapperScan(basePackages = "com.test.mapper.pg",sqlSessionFactoryRef="pgSqlSessionFactory")
public class PgConfig {

    @Primary
    @Bean(name = "pgDataSource")
    @ConfigurationProperties("spring.datasource.pg")
    public DataSource masterDataSource(){
        HikariDataSource dataSource = (HikariDataSource) DataSourceBuilder.create().build();
        return  dataSource;
    }

    @Primary
    @Bean(name = "pgSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("pgDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/pg/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

mapper接口

//mysql数据源mapper接口
@Repository
public interface SecurityProjectInterMapper extends BaseMapper<SecurityProjectInter> {
    List<SecurityProjectInter> selectAll();
}

//pgsql数据源mapper接口
@Repository
public interface DicDimMapper extends BaseMapper<DicDim> {
    public List<DicDim> list();
}

mapper.xml配置文件

//mysql数据源配置
<?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.test.mapper.mysql.SecurityProjectInterMapper">
  <resultMap id="BaseResultMap" type="com.test.entity.SecurityProjectInter">
    <id column="inter_id" jdbcType="CHAR" property="interId" />
    <result column="project_id" jdbcType="CHAR" property="projectId" />
    <result column="uri_type" jdbcType="VARCHAR" property="uriType" />
    <result column="uri_key" jdbcType="VARCHAR" property="uriKey" />
  </resultMap>

  <select id="selectAll" resultMap="BaseResultMap">
    select * from security_project_inter
  </select>
</mapper>

//pgsql数据源配置
<?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.test.mapper.pg.DicDimMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.test.entity.DicDim">
        <id column="code" property="code" />
        <result column="label" property="label" />
        <result column="sort" property="sort" />
    </resultMap>
    <select id="list" resultMap="BaseResultMap">
        select * from dic_dim
    </select>

</mapper>

调用验证

public class BasicService{
    @Autowired
    DicDimMapper dicDimMapper;
    @Autowired
    SecurityProjectInterMapper securityProjectInterMapper;

    public List<DicDim> list(){
        //pg数据源
        return dicDimMapper.list();
    }

    public List<SecurityProjectInter> interAll(){
        //mysql数据源
        return securityProjectInterMapper.selectAll();
    }
}

调用mysql数据源接口方法时，查询mysql库，调用pgsql数据源接口方法时，查询pgsql库

注：此实例应用mybatis plus