【Database】基于Mybatis的多数据源连接实现方式

场景

出于种种原因有些时候我们的项目中会用到同时连接多个数据源的情况 ，比如数据太大了分了多个库，又比如统计任务只能读原始数据的数据库，写只能在统计结果的库中。

设计思路

基于mybatis我们的方案是这样的，需要自己实现一个SqlSessionTemplate，里面维护了一个dbId到SqlSessionFactory的映射，SqlSessionFactory在初始化时需要指定数据源信息，在执行sql前会调用SqlSessionTemplate的getSqlSessionFactory方法，我们通过重写这个方法，使其通过dbId映射到对应的SqlSessionFactory即可，dbId的储存就需要一个ThreadLocal来存储，保证线程之间的隔离，这样每个线程就能按需去连对应的DB了。

DynamicSqlSessionTemplate

public class DynamicSqlSessionTemplate extends SqlSessionTemplate {
    private final SqlSessionFactory sqlSessionFactory;
    private final ExecutorType executorType;
    private final SqlSession sqlSessionProxy;
    private final PersistenceExceptionTranslator exceptionTranslator;

    // 可以动态增加减少sqlSession
    private Map<String, SqlSessionFactory> targetSqlSessionFactorys;
    private SqlSessionFactory defaultTargetSqlSessionFactory;

    public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());
    }

    public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType) {
        this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration()
                .getEnvironment().getDataSource(), true));
    }

    public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
                                     PersistenceExceptionTranslator exceptionTranslator) {

        super(sqlSessionFactory, executorType, exceptionTranslator);

        this.sqlSessionFactory = sqlSessionFactory;
        this.executorType = executorType;
        this.exceptionTranslator = exceptionTranslator;

        this.sqlSessionProxy = (SqlSession) newProxyInstance(
                SqlSessionFactory.class.getClassLoader(),
                new Class[]{SqlSession.class},
                new SqlSessionInterceptor());

        this.defaultTargetSqlSessionFactory = sqlSessionFactory;
    }

    public Map<String, SqlSessionFactory> getTargetSqlSessionFactorys() {
        return targetSqlSessionFactorys;
    }

    public void setTargetSqlSessionFactorys(Map<String, SqlSessionFactory> targetSqlSessionFactorys) {
        this.targetSqlSessionFactorys = targetSqlSessionFactorys;
    }

    public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory) {
        this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;
    }

    @Override
    public SqlSessionFactory getSqlSessionFactory() {
        SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactorys.get(SqlSessionContext.getDatasourceName());
        if (targetSqlSessionFactory != null) {
            return targetSqlSessionFactory;
        } else if (defaultTargetSqlSessionFactory != null) {
            return defaultTargetSqlSessionFactory;
        } else {
            Assert.notNull(targetSqlSessionFactorys, "Property 'targetSqlSessionFactorys' or 'defaultTargetSqlSessionFactory' are required");
            Assert.notNull(defaultTargetSqlSessionFactory, "Property 'defaultTargetSqlSessionFactory' or 'targetSqlSessionFactorys' are required");
        }
        return this.sqlSessionFactory;
    }

    @Override
    public Configuration getConfiguration() {
        return this.getSqlSessionFactory().getConfiguration();
    }

    public ExecutorType getExecutorType() {
        return this.executorType;
    }

    public PersistenceExceptionTranslator getPersistenceExceptionTranslator() {
        return this.exceptionTranslator;
    }

    /**
     * {@inheritDoc}
     */
    public <T> T selectOne(String statement) {
        return this.sqlSessionProxy.<T>selectOne(statement);
    }

    /**
     * {@inheritDoc}
     */
    public <T> T selectOne(String statement, Object parameter) {
        return this.sqlSessionProxy.<T>selectOne(statement, parameter);
    }

    /**
     * {@inheritDoc}
     */
    public <K, V> Map<K, V> selectMap(String statement, String mapKey) {
        return this.sqlSessionProxy.<K, V>selectMap(statement, mapKey);
    }

    /**
     * {@inheritDoc}
     */
    public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey) {
        return this.sqlSessionProxy.<K, V>selectMap(statement, parameter, mapKey);
    }

    /**
     * {@inheritDoc}
     */
    public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds) {
        return this.sqlSessionProxy.<K, V>selectMap(statement, parameter, mapKey, rowBounds);
    }

    /**
     * {@inheritDoc}
     */
    public <E> List<E> selectList(String statement) {
        return this.sqlSessionProxy.<E>selectList(statement);
    }

    /**
     * {@inheritDoc}
     */
    public <E> List<E> selectList(String statement, Object parameter) {
        return this.sqlSessionProxy.<E>selectList(statement, parameter);
    }

    /**
     * {@inheritDoc}
     */
    public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
        return this.sqlSessionProxy.<E>selectList(statement, parameter, rowBounds);
    }

    /**
     * {@inheritDoc}
     */
    public void select(String statement, ResultHandler handler) {
        this.sqlSessionProxy.select(statement, handler);
    }

    /**
     * {@inheritDoc}
     */
    public void select(String statement, Object parameter, ResultHandler handler) {
        this.sqlSessionProxy.select(statement, parameter, handler);
    }

    /**
     * {@inheritDoc}
     */
    public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {
        this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);
    }

    /**
     * {@inheritDoc}
     */
    public int insert(String statement) {
        return this.sqlSessionProxy.insert(statement);
    }

    /**
     * {@inheritDoc}
     */
    public int insert(String statement, Object parameter) {
        return this.sqlSessionProxy.insert(statement, parameter);
    }

    /**
     * {@inheritDoc}
     */
    public int update(String statement) {
        return this.sqlSessionProxy.update(statement);
    }

    /**
     * {@inheritDoc}
     */
    public int update(String statement, Object parameter) {
        return this.sqlSessionProxy.update(statement, parameter);
    }

    /**
     * {@inheritDoc}
     */
    public int delete(String statement) {
        return this.sqlSessionProxy.delete(statement);
    }

    /**
     * {@inheritDoc}
     */
    public int delete(String statement, Object parameter) {
        return this.sqlSessionProxy.delete(statement, parameter);
    }

    /**
     * {@inheritDoc}
     */
    public <T> T getMapper(Class<T> type) {
        return getConfiguration().getMapper(type, this);
    }

    /**
     * {@inheritDoc}
     */
    public void commit() {
        throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
    }

    /**
     * {@inheritDoc}
     */
    public void commit(boolean force) {
        throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
    }

    /**
     * {@inheritDoc}
     */
    public void rollback() {
        throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
    }

    /**
     * {@inheritDoc}
     */
    public void rollback(boolean force) {
        throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
    }

    /**
     * {@inheritDoc}
     */
    public void close() {
        throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");
    }

    /**
     * {@inheritDoc}
     */
    public void clearCache() {
        this.sqlSessionProxy.clearCache();
    }

    /**
     * {@inheritDoc}
     */
    public Connection getConnection() {
        return this.sqlSessionProxy.getConnection();
    }

    /**
     * {@inheritDoc}
     *
     * @since 1.0.2
     */
    public List<BatchResult> flushStatements() {
        return this.sqlSessionProxy.flushStatements();
    }

    /**
     * Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also
     * unwraps exceptions thrown by {@code Method#invoke(Object, Object...)} to pass a {@code PersistenceException} to
     * the {@code PersistenceExceptionTranslator}.
     */
    private class SqlSessionInterceptor implements InvocationHandler {
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            final SqlSession sqlSession = getSqlSession(
                    DynamicSqlSessionTemplate.this.getSqlSessionFactory(),
                    DynamicSqlSessionTemplate.this.executorType,
                    DynamicSqlSessionTemplate.this.exceptionTranslator);
            try {
                Object result = method.invoke(sqlSession, args);
                if (!isSqlSessionTransactional(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory())) {
                    // force commit even on non-dirty sessions because some databases require
                    // a commit/rollback before calling close()
                    sqlSession.commit(true);
                }
                return result;
            } catch (Throwable t) {
                Throwable unwrapped = unwrapThrowable(t);
                if (DynamicSqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
                    Throwable translated = DynamicSqlSessionTemplate.this.exceptionTranslator
                            .translateExceptionIfPossible((PersistenceException) unwrapped);
                    if (translated != null) {
                        unwrapped = translated;
                    }
                }
                throw unwrapped;
            } finally {
                closeSqlSession(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory());
            }
        }
    }

}

SqlSessionContext

public abstract class SqlSessionContext {

    // 解决子线程不共享数据问题
    private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<String>();

    public static String getDatasourceName() {
        return contextHolder.get();
    }

    public static void switchDatasource(String datasourceId) {
        contextHolder.set(datasourceId);
    }

    public static void clearContextType() {
        contextHolder.remove();
    }

    private static void print(String datasourceName) {
        DynamicSqlSessionTemplate dynamicSqlSessionTemplate = SpringUtil.getBean(DynamicSqlSessionTemplate.class);
        Map<String, SqlSessionFactory> targetSqlSessionFactorys = dynamicSqlSessionTemplate.getTargetSqlSessionFactorys();
        SqlSessionFactory sqlSessionFactory = targetSqlSessionFactorys.getOrDefault(datasourceName, null);
        if (sqlSessionFactory == null) {
            return;
        }
        HikariDataSource dataSource = (HikariDataSource) sqlSessionFactory.getConfiguration()
                .getEnvironment()
                .getDataSource();
        if (dataSource == null) {
            return;
        }
        log.info("datasource switch to {}", datasourceName);
    }
}

DynamicDatasourceConfig

@Configuration
@MapperScan(basePackages = {"cn.witsky.cpc.dao"}, sqlSessionTemplateRef = "dynamicSqlSessionTemplate")
public class DynamicDatasourceConfig {
    @Resource(name = "cpcConfig")
    private CpcConfig config;

    @Bean(name = "dynamicSqlSessionTemplate")
    public DynamicSqlSessionTemplate getDynamicSqlSessionTemplate() {
        if (ArrayUtil.isEmpty(config.getDatasource())) {
            log.error("config at least one datasource!");
        }
        HashSet<String> names = new HashSet<>();
        HashMap<String, SqlSessionFactory> map = new HashMap<>();
        for (Datasource datasource : config.getDatasource()) {
            log.info("loading datasource|id ={}", datasource.getDatabaseId());
            if (!names.add(datasource.getDatabaseId())) {
                log.info("databaseId duplicate,skip!");
                continue;
            }
            SqlSessionFactory sqlSessionFactory = buildSqlSessionFactory(datasource);
            if (ObjUtil.isNull(sqlSessionFactory)) {
                continue;
            }
            map.put(datasource.getDatabaseId(), sqlSessionFactory);
        }
        // 取第一个数据源为默认数据源
        String first = map.keySet().stream().findFirst().orElse("");
        DynamicSqlSessionTemplate dynamicSqlSessionTemplate = new DynamicSqlSessionTemplate(map.get(first));
        dynamicSqlSessionTemplate.setTargetSqlSessionFactorys(map);
        return dynamicSqlSessionTemplate;
    }

    private SqlSessionFactory buildSqlSessionFactory(Datasource datasource) {
        try {
            HikariDataSource hikariDataSource = new HikariDataSource();
            // 数据库配置
            hikariDataSource.setJdbcUrl(datasource.getUrl());
            hikariDataSource.setUsername(datasource.getUsername());
            hikariDataSource.setPassword(datasource.getPassword());
            hikariDataSource.setPoolName(datasource.getDatabaseId());
            // 数据源连接池hikari公共配置
            hikariDataSource.setDriverClassName(config.getDatasourceHikari().getDriverClassName());
            hikariDataSource.setMinimumIdle(config.getDatasourceHikari().getMinimumIdle());
            hikariDataSource.setMaximumPoolSize(config.getDatasourceHikari().getMaximumPoolSize());
            hikariDataSource.setConnectionTimeout(config.getDatasourceHikari().getConnectionTimeout());
            hikariDataSource.setIdleTimeout(config.getDatasourceHikari().getIdleTimeout());
            hikariDataSource.setMaxLifetime(config.getDatasourceHikari().getMaxLifetime());
            // 连接mapper文件组装SqlSessionFactory
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(hikariDataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(config.getMybatisMapperLocations()));
            return bean.getObject();
        } catch (Exception e) {
            log.info(e.getMessage(), e);
            return null;
        }
    }
}

Datasource

public class Datasource {
    private String databaseId; // 数据源id
    private String url; // 数据库连接地址
    private String username; // 数据库用户名
    private String password; // 数据库密码
    private String driverClassName; // jdbc驱动
    private String mapperLocations; // mapper文件地址
}

DatasourceHikari

public class DatasourceHikari {
    // 数据库驱动类
    private String driverClassName;
    // 连接池核心线程数。默认值10。小于0或大于maximum-pool-size，都会重置为maximum-pool-size。
    private int minimumIdle = 10;
    // 连接池总最大允许的连接数，超过此值的并发请求会被阻塞。默认值10。小于等于0会被重置为默认值10。
    private int maximumPoolSize = 20;
    // 连接超时时间。默认值30秒。小于250ms会被重置为最小值250ms。
    private long connectionTimeout = 300L;
    // 线程空闲超时时间，当大于核心线程小于最大线程时才生效。默认值10分钟。
    private Long idleTimeout = 6000000L;
    // 线程的最大存活时间，从线程创建开始计时，到此时间会自动关闭，应小于数据库配置的单个连接的最大时长。默认值30分钟。
    private Long maxLifetime = 60000L;
    // 数据库验证SQL
    private String connectionTestQuery = "select 1 from dual";
}

注意，需要关闭自动配置否则启动报错

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})