springboot+Mybatis配置实现多数据源

简述

应公司需求，结合网上的许多资料和公司项目配置实现多数据源。在此记录一下配置过程，以供大家参考。

---

项目简介

框架用的是SpringBoot+Mybatis，初始数据库是MySQL，具体MySQL的整合在这里不做详细说明，要求是添加一个Oracle的数据源，实现可以同时查询MySQL和Oracle两个数据库的功能。项目所用数据源是阿里的Druid，没有用传统的配置文件，用的是disconf进行的配置管理。

---

配置数据库

pom文件就不写了，比较简单，Oracle的包添加下就可以了。主要是数据库这边的配置，我将MySQL和Oracle的数据库配置写在了一起。

package com.server.configs;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.core.mapper.JdbcMapper;
import com.core.mapper.OracleJdbcMapper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by jht on 2017-5-25.
 */
@SuppressWarnings("ALL")
@Configuration
public class DatabaseConfig {

    @Autowired
    private JdbcMapper jdbcMapper;
    @Autowired
    private OracleJdbcMapper oracleJdbcMapper;

    @Primary
    @Bean(name = "mysqlDataSource")
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setDriverClassName(jdbcMapper.getDriverClassName());
        datasource.setUrl(jdbcMapper.getUrl());
        datasource.setUsername(jdbcMapper.getUsername());
        datasource.setPassword(jdbcMapper.getPassword());
        datasource.setInitialSize(jdbcMapper.getInitialSize());
        datasource.setMaxActive(jdbcMapper.getMaxActive());
        datasource.setMaxWait(jdbcMapper.getMaxWait());
        datasource.setMinIdle(jdbcMapper.getMinIdle());
        datasource.setTimeBetweenEvictionRunsMillis(jdbcMapper.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(jdbcMapper.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(jdbcMapper.getValidationQuery());
        datasource.setTestWhileIdle(jdbcMapper.getTestWhileIdle());
        datasource.setTestOnBorrow(jdbcMapper.getTestOnBorrow());
        datasource.setTestOnReturn(jdbcMapper.getTestOnReturn());
        datasource.setMaxOpenPreparedStatements(jdbcMapper.getMaxOpenPreparedStatements());
        datasource.setRemoveAbandoned(jdbcMapper.getRemoveAbandoned());
        datasource.setRemoveAbandonedTimeout(jdbcMapper.getRemoveAbandonedTimeout());
        datasource.setLogAbandoned(jdbcMapper.getLogAbandoned());
        List<Filter> list = new ArrayList();
        list.add(new StatFilter());
        WallFilter wallFilter = new WallFilter();
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);
        list.add(wallFilter);
        datasource.setProxyFilters(list);

        return datasource;

    }

    @Bean(name = "oracleDataSource")
    public DataSource oracleDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setDriverClassName(oracleJdbcMapper.getDriverClassName());
        datasource.setUrl(oracleJdbcMapper.getUrl());
        datasource.setUsername(oracleJdbcMapper.getUsername());
        datasource.setPassword(oracleJdbcMapper.getPassword());
        datasource.setInitialSize(oracleJdbcMapper.getInitialSize());
        datasource.setMaxActive(oracleJdbcMapper.getMaxActive());
        datasource.setMaxWait(oracleJdbcMapper.getMaxWait());
        datasource.setMinIdle(oracleJdbcMapper.getMinIdle());
        datasource.setTimeBetweenEvictionRunsMillis(oracleJdbcMapper.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(oracleJdbcMapper.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(oracleJdbcMapper.getValidationQuery());
        datasource.setTestWhileIdle(oracleJdbcMapper.getTestWhileIdle());
        datasource.setTestOnBorrow(oracleJdbcMapper.getTestOnBorrow());
        datasource.setTestOnReturn(oracleJdbcMapper.getTestOnReturn());
        datasource.setMaxOpenPreparedStatements(oracleJdbcMapper.getMaxOpenPreparedStatements());
        datasource.setRemoveAbandoned(oracleJdbcMapper.getRemoveAbandoned());
        datasource.setRemoveAbandonedTimeout(oracleJdbcMapper.getRemoveAbandonedTimeout());
        datasource.setLogAbandoned(oracleJdbcMapper.getLogAbandoned());
        List<Filter> list = new ArrayList();
        list.add(new StatFilter());
        WallFilter wallFilter = new WallFilter();
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);
        list.add(wallFilter);
        datasource.setProxyFilters(list);

        return datasource;

    }

}

其中JdbcMapper和OracleJdbcMapper是disconf配置文件对应的配置类。
@Primary 用来指定一个主数据源，这里是将MySQL的数据库设为主数据源。

---

配置数据源

多添加一个Oracle库，mapper包和xml包还有PO都跟MySQL的分开，所以写了两套mybatis配置数据源的配置类。先贴上主数据库MySQL的配置类。

package com.server.configs;

import com.github.pagehelper.PageHelper;
import java.util.Properties;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mapstruct.Mapper;
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.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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

/**
 * Created by jht on 2017/5/30.
 */
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "com.server.dao.mapper", sqlSessionTemplateRef = "mysqlSessionTemplate")
public class MyBatisConfig {

    @Resource
    DataSource mysqlDataSource;

    @Primary
    @Bean(name = "mysqlSessionFactory")
    public SqlSessionFactory mysqlSessionFactory() throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(mysqlDataSource);
        sessionFactory.setTypeAliasesPackage("com.ydx.server.model.mysql");
        //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);

        //添加插件
        sessionFactory.setPlugins(new Interceptor[]{pageHelper});

        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            sessionFactory.setMapperLocations(resolver.getResources("classpath:mapper/**/*.xml"));
            return sessionFactory.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Primary
    @Bean(name = "mysqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("mysqlSessionFactory") SqlSessionFactory mysqlSessionFactory) {
        return new SqlSessionTemplate(mysqlSessionFactory);
    }

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

}

需要注意的是因为是主数据库，所以需要加@Primary；
@Bean的名字要需要写清楚；
@MapperScan的包名要写对；

下面是Oracle的配置类，差别不大。

package com.server.configs;

import com.github.pagehelper.PageHelper;
import org.apache.ibatis.plugin.Interceptor;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Properties;

/**
 * Created by jht on 2017/5/30.
 */
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "com.server.dao.oracle", sqlSessionTemplateRef = "oracleSessionTemplate")
public class OracleMyBatisConfig {

    @Resource
    DataSource oracleDataSource;

    @Bean(name = "oracleSessionFactory")
    public SqlSessionFactory mysqlSessionFactory() throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(oracleDataSource);
        sessionFactory.setTypeAliasesPackage("com.ydx.server.model.mysql");
        //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);

        //添加插件
        sessionFactory.setPlugins(new Interceptor[]{pageHelper});

        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            sessionFactory.setMapperLocations(resolver.getResources("classpath:oraclemapper/*.xml"));
            return sessionFactory.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "oracleSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("oracleSessionFactory") SqlSessionFactory oracleSessionFactory) {
        return new SqlSessionTemplate(oracleSessionFactory);
    }

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

}

跟MySQL的配置差不多,到此为止，多数据源配置就算完成啦。接下来的测试我就不贴了。

---

总结

其实还有一种方式是动态配置数据源的，用到了AOP，有时间在研究吧。这个方式比较死板，维护起来比较麻烦，适合那种只是想找一个简单的多数据源支持的项目。最重要的其实就是一步步的注入，先创建DataSource，然后通过注入DataSource创建SessionFactory，再创建事务，最后包装到SqlSessionTemplate中。其中DAO包，PO类，xml所在位置都需要准确指定。所有@Bean都需要按照命名指定正确。注意这几点，配置起来就会简单许多了。