springboot配置多数据源 mybatis + postgresql + mysql

引言

最近公司要做多数据源查询，来自于两个不同的数据库，postgresql、 mysql、 我也是各种借鉴模仿，整理出来了一套可用的方案。

大致思路如下

1. 将mapper类和xml进行文件夹区分
   例如: mapper.mysql 和 mapper.postgresql
2. 编写数据库配置类，通过@MapperScan(“mapper.XXX”)对不同的包进行扫描，来实现查询不同的库
3. 用@Primary来区分主库与其他库

接下来直接上代码

引入数据库依赖，我的springboot是2.x的

	<!-- postgresql 驱动 -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        
    <!--mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

编写配置文件 yml 或者 properties

注意：数据库地址最后一个词是：jdbc-url 不是 url

#postgresql数据源配置
spring.datasource.primary.driverClassName=org.postgresql.Driver
spring.datasource.primary.jdbc-url=jdbc:postgresql://192.168.11.21:5432/operation_qdzfbz
spring.datasource.primary.username=postgres
spring.datasource.primary.password=postgres

#mysql数据源配置
spring.datasource.secondary.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.jdbc-url=jdbc:mysql://127.0.0.1:3308/sms?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root

主数据库配置类

注意:

1. 最好直接把包复制过来，自己引入特别容易出错，尤其是@MapperScan

2. 替换三处：
   @MapperSacn(“你要扫描的mapper包”)，getResources(“classpath:mapper/*.xml”))，setTypeAliasesPackage(“diit.microservice.operation.entity”);

package diit.microservice.operation.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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 org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.annotation.MapperScan;

import javax.sql.DataSource;

/**
 * @author lubing
 * @date 2020/12/22 22:06
 */
@Configuration
@MapperScan(basePackages = "diit.microservice.operation.mapper",sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryDataSourceConfig {

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

    @Bean("primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory primarySqlSessionFactory(
            @Qualifier("primaryDataSource") DataSource dataSource)
            throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // 设置xml文件存放位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));
        bean.setTypeAliasesPackage("diit.microservice.operation.entity");
        return bean.getObject();
    }

    @Bean("primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager primaryTransactionManager(
            @Qualifier("primaryDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate primarySqlSessionTemplate(
            @Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

第二个数据库的配置类

package diit.microservice.operation.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import tk.mybatis.spring.annotation.MapperScan;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author lubing
 * @date 2020/12/22 22:06
 */
@Configuration
@MapperScan(basePackages = "diit.microservice.operation.mapper2",sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryDataSourceConfig {

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("secondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(
            @Qualifier("secondaryDataSource") DataSource dataSource)
            throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // 设置xml文件存放位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper2/*.xml"));
        bean.setTypeAliasesPackage("diit.microservice.operation.entity");
        return bean.getObject();
    }

    @Bean("secondaryTransactionManager")
    public DataSourceTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("secondarySqlSessionTemplate")
    public SqlSessionTemplate secondarySqlSessionTemplate(
            @Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

目录结构

我的mapper是叫mapper和mapper1，不是很规范，你们可以起的规范一点

使用就不用多描述了吧，跟正常使用一样，唯一的一点是没有对事务进行研究，等其他人研究吧