springboot+mybatis集成多数据源MySQL/Oracle/SqlServer

日常开发中可能时常会遇到一些这样的需求,业务数据库和第三方数据库,两个或多个数据库属于不同数据库厂商,这时候就需要通过配置来实现对数据库实现多源处理.大致说一下我的业务场景,框架本身是配置的springboot+mybatis+MySQL,业务数据库为mysql数据库,现在需要做一个统计平台,统计数据来自于第三方Oracle数据库.

闲话不多说,直接上代码:

1.maven配置,将涉及到的数据库配置到pom.xml文件中

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

        <!-- oracle jdbc -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.2</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/libs/ojdbc7-12.1.0.2.jar</systemPath>
        </dependency>
        <!-- SqlServer jdbc -->
        <dependency>
               <groupId>com.microsoft.sqlserver</groupId>
               <artifactId>sqljdbc4</artifactId>
               <version>4.0</version>

        </dependency>

2.application.properties配置,配置对应的数据库连接

#MySQL

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull

spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#oracle
oracle.datasource.url=jdbc:oracle:thin:@xxx.xxx.xxx:11521/ORCL
oracle.datasource.username=xxx
oracle.datasource.password=xxx
oracle.datasource.driverClassName=oracle.jdbc.OracleDriver

#SqlServer
sqlserver.datasource.url=jdbc:sqlserver://xxx.xxx.xxx:1433;DatabaseName=test
sqlserver.datasource.username=xxx
sqlserver.datasource.password=xxx
sqlserver.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

3.去除默认数据库配置,每个数据库单独写一个config.java文件配置

3.1 MySQL配置

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MysqlDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlDataSourceConfig {

// 精确到 mysql 目录，以便跟其他数据源隔离

    static final String PACKAGE = "com.xxx.xxx.mysqlmapper";//对应的mysql dao层包

    static final String MAPPER_LOCATION = "classpath:mysqlmybatis/*.xml";//对应mysql的mapper.xml文件

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String user;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "mysqlDataSource")
    @Primary
    public DataSource mysqlDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "mysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager mysqlTransactionManager() {
        return new DataSourceTransactionManager(mysqlDataSource());
    }

    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource mysqlDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(mysqlDataSource);
        sessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(MysqlDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

3.2 Oracle配置

@Configuration
//扫描 Mapper 接口并容器管理
@MapperScan(basePackages= OracleDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleDataSourceConfig {

    // 精确到 oracle 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.xxx.xxx.oraclemapper";
    static final String MAPPER_LOCATION = "classpath:oraclemybatis/*.xml";

    @Value("${oracle.datasource.url}")
    private String url;

    @Value("${oracle.datasource.username}")
    private String user;

    @Value("${oracle.datasource.password}")
    private String password;

    @Value("${oracle.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "oracleDataSource")
    public DataSource oracleDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "oracleTransactionManager")
    public DataSourceTransactionManager oracleTransactionManager() {
        return new DataSourceTransactionManager(oracleDataSource());
    }

    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource oracleDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(oracleDataSource);
        sessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(OracleDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

3.3 SqlServer配置

@Configuration
//扫描 Mapper 接口并容器管理

@MapperScan(basePackages=SqlServerDataSourceConfig.PACKAGE,sqlSessionFactoryRef="sqlserverSqlSessionFactory")

public class SqlServerDataSourceConfig {

    // 精确到 sqlserver 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.xxx.xxx.sqlservermapper";
    static final String MAPPER_LOCATION = "classpath:sqlservermybatis/*.xml";

    @Value("${sqlserver.datasource.url}")
    private String url;

    @Value("${sqlserver.datasource.username}")
    private String user;

    @Value("${sqlserver.datasource.password}")
    private String password;

    @Value("${sqlserver.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "sqlserverDataSource")
    public DataSource sqlserverDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "sqlserverTransactionManager")
    public DataSourceTransactionManager sqlserverTransactionManager() {
        return new DataSourceTransactionManager(sqlserverDataSource());
    }

    @Bean(name = "sqlserverSqlSessionFactory")
 public SqlSessionFactory sqlserverSqlSessionFactory(@Qualifier("sqlserverDataSource") DataSource sqlserverDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(sqlserverDataSource);
        sessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(SqlServerDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

如果还有其他数据库,配置类似....

通过以上配置,即可实现多数据源管理,具体配置按照自己的业务需求