SpringBoot实战：SpringBoot+Mybatis配置Druid多数据源

关注我，带了熟悉Java趣事，Java进阶攻略等着你

SpringBoot+Mybatis配置Druid多数据源
开篇之前，说一句题外话。多数据源和动态数据源的区别。

1.多数据源，一般用于对接多个业务上独立的数据库（可能异构数据库）。
2.动态数据源，一般用于大型应用对数据切分。

配置参考
如何配置多数据源，网上教程一大堆。可参考 SpringBoot+MyBatis多数据源最简解决方案。

问题描述
在实际开发配置中发现，如果要启用Druid的防火墙监控（WallFilter）和统计监控（StatFilter），多个异构数据源就会出错，错误信息如下：

com.alibaba.druid.sql.parser.ParserException: syntax error, error in....

跟踪Druid的源码，发现了问题。

// com.alibaba.druid.wall.WallFilter
  private WallCheckResult checkInternal(String sql) throws SQLException {
    WallCheckResult checkResult = provider.check(sql);
    List<Violation> violations = checkResult.getViolations();

    // ... 下面省略了 ...
  }

所有的检查sql工作，都在checkInternal方法中完成，而provider对象在执行init初始化之后就再也没有改变了。这也就导致异构数据库的sql检查

StatFilter也是类似问题。

// com.alibaba.druid.filter.stat.StatFilter#createSqlStat(StatementProxy, String)
  public JdbcSqlStat createSqlStat(StatementProxy statement, String sql) {
    // ...省略
    String dbType = this.dbType;
    if (dbType == null) {
      dbType = dataSource.getDbType();
    }
    // ...省略//
  }

解决方案

重写WallFilter

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.proxy.jdbc.CallableStatementProxy;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;
import com.alibaba.druid.proxy.jdbc.DataSourceProxy;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.alibaba.druid.wall.WallProvider;
import com.alibaba.druid.wall.spi.DB2WallProvider;
import com.alibaba.druid.wall.spi.MySqlWallProvider;
import com.alibaba.druid.wall.spi.OracleWallProvider;
import com.alibaba.druid.wall.spi.PGWallProvider;
import com.alibaba.druid.wall.spi.SQLServerWallProvider;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 自定义Druid防火墙过滤器
 * <p>使用多类型数据源时，因共用WallProvider解析器，导致判断数据源类型出错</p>
 * @author BBF
 * @see com.alibaba.druid.wall.WallFilter
 */
public class FrameWallFilter extends WallFilter {

  /**
   * 用线程安全的ConcurrentHashMap存储WallProvider对象
   */
  private final Map<String, WallProvider> providerMap = new ConcurrentHashMap<>(8);

  /**
   * 获取WallProvider
   * @param dataSource 数据源
   * @return WallProvider
   */
  private WallProvider getProvider(DataSourceProxy dataSource) {
    String dbType;
    if (dataSource.getDbType() != null) {
      dbType = dataSource.getDbType();
    } else {
      dbType = JdbcUtils.getDbType(dataSource.getRawJdbcUrl(), "");
    }
    WallProvider provider;
    if (JdbcUtils.MYSQL.equals(dbType) || JdbcUtils.MARIADB.equals(dbType) || JdbcUtils.H2.equals(dbType)) {
      provider = providerMap.get(JdbcUtils.MYSQL);
      if (provider == null) {
        provider = new MySqlWallProvider(new WallConfig(MySqlWallProvider.DEFAULT_CONFIG_DIR));
        provider.setName(dataSource.getName());
        providerMap.put(JdbcUtils.MYSQL, provider);
      }
    } else if (JdbcUtils.ORACLE.equals(dbType) || JdbcUtils.ALI_ORACLE.equals(dbType)) {
      provider = providerMap.get(JdbcUtils.ORACLE);
      if (provider == null) {
        provider = new OracleWallProvider(new WallConfig(OracleWallProvider.DEFAULT_CONFIG_DIR));
        provider.setName(dataSource.getName());
        providerMap.put(JdbcUtils.ORACLE, provider);
      }
    } else if (JdbcUtils.SQL_SERVER.equals(dbType) || JdbcUtils.JTDS.equals(dbType)) {
      provider = providerMap.get(JdbcUtils.SQL_SERVER);
      if (provider == null) {
        provider = new SQLServerWallProvider(new WallConfig(SQLServerWallProvider.DEFAULT_CONFIG_DIR));
        provider.setName(dataSource.getName());
        providerMap.put(JdbcUtils.SQL_SERVER, provider);
      }
    } else if (JdbcUtils.POSTGRESQL.equals(dbType) || JdbcUtils.ENTERPRISEDB.equals(dbType)) {
      provider = providerMap.get(JdbcUtils.POSTGRESQL);
      if (provider == null) {
        provider = new PGWallProvider(new WallConfig(PGWallProvider.DEFAULT_CONFIG_DIR));
        provider.setName(dataSource.getName());
        providerMap.put(JdbcUtils.POSTGRESQL, provider);
      }
    } else if (JdbcUtils.DB2.equals(dbType)) {
      provider = providerMap.get(JdbcUtils.DB2);
      if (provider == null) {
        provider = new DB2WallProvider(new WallConfig(DB2WallProvider.DEFAULT_CONFIG_DIR));
        provider.setName(dataSource.getName());
        providerMap.put(JdbcUtils.DB2, provider);
      }
    } else {
      throw new IllegalStateException("dbType not support : " + dbType);
    }
    return provider;
  }

  /**
   * 利用反射来更新父类私有变量provider
   * @param connection ConnectionProxy
   */
  private void setProvider(ConnectionProxy connection) {
    for (Class<?> cls = this.getClass(); cls != Object.class; cls = cls.getSuperclass()) {
      try {
        Field field = cls.getDeclaredField("provider");
        field.setAccessible(true);
        field.set(this, getProvider(connection.getDirectDataSource()));
      } catch (Exception e) {
        // Field不在当前类定义,继续向上转型
      }
    }
  }

  @Override
  public PreparedStatementProxy connection_prepareStatement(FilterChain chain,
                                                            ConnectionProxy
                                                                connection,
                                                            String sql) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareStatement(chain, connection, sql);
  }

  @Override
  public PreparedStatementProxy connection_prepareStatement(FilterChain chain,
                                                            ConnectionProxy connection,
                                                            String sql,
                                                            int autoGeneratedKeys) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareStatement(chain, connection, sql, autoGeneratedKeys);
  }

  @Override
  public PreparedStatementProxy connection_prepareStatement(FilterChain chain,
                                                            ConnectionProxy connection,
                                                            String sql,
                                                            int resultSetType,
                                                            int resultSetConcurrency)
      throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareStatement(chain, connection, sql,
        resultSetType, resultSetConcurrency);
  }

  @Override
  public PreparedStatementProxy connection_prepareStatement(FilterChain chain,
                                                            ConnectionProxy connection,
                                                            String sql,
                                                            int resultSetType,
                                                            int resultSetConcurrency,
                                                            int resultSetHoldability) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareStatement(chain, connection, sql,
        resultSetType, resultSetConcurrency, resultSetHoldability);
  }

  @Override
  public PreparedStatementProxy connection_prepareStatement(FilterChain chain,
                                                            ConnectionProxy connection,
                                                            String sql,
                                                            int[] columnIndexes) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareStatement(chain, connection, sql, columnIndexes);
  }

  @Override
  public PreparedStatementProxy connection_prepareStatement(FilterChain chain,
                                                            ConnectionProxy connection,
                                                            String sql,
                                                            String[] columnNames) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareStatement(chain, connection, sql, columnNames);
  }

  @Override
  public CallableStatementProxy connection_prepareCall(FilterChain chain,
                                                       ConnectionProxy connection,
                                                       String sql) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareCall(chain, connection, sql);
  }

  @Override
  public CallableStatementProxy connection_prepareCall(FilterChain chain,
                                                       ConnectionProxy connection,
                                                       String sql,
                                                       int resultSetType,
                                                       int resultSetConcurrency) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareCall(chain, connection, sql,
        resultSetType, resultSetConcurrency);
  }

  @Override
  public CallableStatementProxy connection_prepareCall(FilterChain chain,
                                                       ConnectionProxy connection,
                                                       String sql,
                                                       int resultSetType,
                                                       int resultSetConcurrency,
                                                       int resultSetHoldability) throws SQLException {
    this.setProvider(connection);
    return super.connection_prepareCall(chain, connection, sql,
        resultSetType, resultSetConcurrency, resultSetHoldability);
  }

}

重写StatFilter

import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.proxy.jdbc.StatementProxy;
import com.alibaba.druid.stat.JdbcSqlStat;

/**
 * 自定义Druid统计监控过滤器
 * <p>使用多类型数据源时，因没有及时清空dbType，导致判断数据源类型出错</p>
 * @author BBF
 * @see com.alibaba.druid.filter.stat.StatFilter#createSqlStat(StatementProxy, String)
 */
public class FrameStatFilter extends StatFilter {
  @Override
  public JdbcSqlStat createSqlStat(StatementProxy statement, String sql) {
    super.setDbType(null);
    return super.createSqlStat(statement, sql);
  }
}

配置过滤器的Bean

如果存在多个同类Bean候选时，被@Primary标志的Bean优先。
另外两个注解@ConfigurationProperties和@ConditionalOnProperty是配置文件的前缀和有特定属性值时生效

/**
   * 自定义Druid防火墙过滤器Bean
   * @param wallConfig 防火墙过滤器配置Bean
   * @return WallFilter
   * @see com.alibaba.druid.spring.boot.autoconfigure.stat.DruidFilterConfiguration#wallFilter
   */
  @Bean("wallFilter")
  @ConfigurationProperties("spring.datasource.druid.filter.wall")
  @ConditionalOnProperty(prefix = "spring.datasource.druid.filter.wall", name = {"enabled"})
  @Primary
  public WallFilter wallFilter(@Qualifier("wallConfig") WallConfig wallConfig) {
    WallFilter filter = new FrameWallFilter();
    filter.setConfig(wallConfig);
    return filter;
  }

  /**
   * 自定义Druid统计监控过滤器Bean
   * @return StatFilter
   * @see com.alibaba.druid.spring.boot.autoconfigure.stat.DruidFilterConfiguration#statFilter
   */
  @Bean("statFilter")
  @ConfigurationProperties("spring.datasource.druid.filter.stat")
  @ConditionalOnProperty(prefix = "spring.datasource.druid.filter.stat", name = {"enabled"}
  )
  @Primary
  public StatFilter statFilter() {
    return new FrameStatFilter();
  }

附录

数据源配置类

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.util.JdbcUtils;
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.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;

/**
 * 配置从数据源
 * @author BBF
 */
@Configuration
@MapperScan(basePackages = MysqlDataSourceConfig.PACKAGE,
    sqlSessionTemplateRef = MysqlDataSourceConfig.SESSION_NAME)
public class MysqlDataSourceConfig {
  /**
   * Dao类所在的包
   */
  public static final String PACKAGE = "com.bbf.frame.service.dao";

  /**
   * mapper.xml所在目录
   */
  private static final String MAPPER_LOCATION = "classpath:/mapperMysql/*Mapper.xml";

  /**
   * mybatis的配置文件路径
   */
  private static final String CONFIG_LOCATION = "classpath:/config/mybatis-config.xml";

  /**
   * bean的名称
   */
  private static final String DATASOURCE_NAME = "mysqlDataSource";
  private static final String FACTORY_NAME = "mysqlSqlSessionFactory";
  public static final String SESSION_NAME = "mysqlSqlSessionTemplate";

  @Bean(DATASOURCE_NAME)
  @ConfigurationProperties("datasource.druid.mysql")
  public DataSource dataSourceTwo() {
    DruidDataSource ds= DruidDataSourceBuilder.create().build();
    ds.setDbType(JdbcUtils.MYSQL);
    return ds;
  }

  /**
   * Mybatis的SQL会话工厂
   * @param dataSource 数据源
   * @return SqlSessionFactory
   * @throws Exception 创建SqlSessionFactory发生异常
   */
  @Bean(name = FACTORY_NAME)
  public SqlSessionFactory sqlSessionFactory(@Qualifier(DATASOURCE_NAME) DataSource dataSource) throws Exception {
    final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dataSource);
    ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    sqlSessionFactoryBean.setMapperLocations(resolver.getResources(MAPPER_LOCATION));
    sqlSessionFactoryBean.setConfigLocation(resolver.getResource(CONFIG_LOCATION));
    return sqlSessionFactoryBean.getObject();
  }

  @Bean(SESSION_NAME)
  public SqlSessionTemplate sqlSessionTemplate(@Qualifier(FACTORY_NAME) SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

配置文件

为了其它数据源配置的相对独立性，单独保存为一个文件mysql.properties。
在入口类上，定义@PropertySource，本文在主数据源之外，又定义了两个数据源。

@SpringBootApplication
@ImportResource(locations = {"classpath:config/conf.xml"})
@PropertySource(encoding = "UTF8", value = {"classpath:config/datasource/sqlserver.properties",
    "classpath:config/datasource/mysql.properties"})
public class Application {
  //内容略
}
############################################
# DataSource - druid    Mysql数据源
############################################
# 多数据源，涉及到异构数据库，必须明确指定dbType，否则druid的WallFilter转换SQL出错
# 取值内容可参考 com.alibaba.druid.util.JdbcConstants
datasource.druid.mysql.db-type=mysql
datasource.druid.mysql.driver-class-name=com.mysql.jdbc.Driver
datasource.druid.mysql.url=jdbc:mysql://192.168.1.2:3306/bbf?characterEncoding=UTF-8
datasource.druid.mysql.username=root
datasource.druid.mysql.password=root

# 初始连接数
datasource.druid.mysql.initial-size=5
#最大连接池数量。default=8+
datasource.druid.mysql.max-active=20
# 获取连接时最大等待时间，单位毫秒。
# 配置了maxWait之后，缺省启用公平锁，并发效率会有所下降。
# 如果需要可以通过配置useUnfairLock属性为true使用非公平锁
datasource.druid.mysql.max-wait=60000
# 开启池的prepared statement池功能，PSCache对支持游标的数据库性能提升巨大
# 如果用Oracle, 则把poolPreparedStatements配置为true, mysql 5.5之后建议true
datasource.druid.mysql.pool-prepared-statements=true
# 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
# 在Druid中，会存在Oracle下PSCache占用内存过多的问题，可以把这个数据配置大一些，比如100。默认=-1
datasource.druid.mysql.max-open-prepared-statements=100
# 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。
# 如果validationQuery为null,testOnBorrow,testOnBorrow,testOnReturn,testWhileIdle都不会起作用。这个可以不配置
datasource.druid.mysql.validation-query=SELECT 'V';
# 单位:秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法
# mysql实现的不是很合理，不建议在mysql下配置此参数
datasource.druid.mysql.validation-query-timeout=1000
# 是否在从池中取出连接前进行检验。如果检验失败，则从池中去除连接并尝试取出另一个
# 注意: 设置为true后，validation-query参数必须设置
datasource.druid.mysql.test-on-borrow=false
# 是否在归还连接池前进行检验
# 注意: 设置为true后，validation-query参数必须设置
datasource.druid.mysql.test-on-return=false
# 建议配置为true,不影响性能，并且保证安全性。
# 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，
# 执行validationQuery检测连接是否有效，validation-query参数必须设置。default=false
datasource.druid.mysql.test-while-idle=true
# 连接池中的minIdle数据以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作。default=false
datasource.druid.mysql.keep-alive=true
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 default=1分钟
#有两个含义：
# (1)Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接
# (2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
datasource.druid.mysql.time-between-eviction-runs-millis=60000
#池中的连接保持空闲而不被驱逐的最小时间，单位是毫秒
datasource.druid.mysql.min-evictable-idle-time-millis=100000
datasource.druid.mysql.max-evictable-idle-time-millis=200000
#合并多个DruidDataSource的监控数据
datasource.druid.mysql.use-global-data-source-stat=false

事务配置

这个因人而异，我是更喜欢xml方式配置事务。

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns = "http://www.springframework.org/schema/beans"
       xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx = "http://www.springframework.org/schema/tx"
       xmlns:aop = "http://www.springframework.org/schema/aop"
       xsi:schemaLocation = "
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
  <!-- 对dataSource 数据源进行事务管理 -->
  <!-- 注意，此处ref的值在DataSourceConfig中配置了 -->
  <bean id = "mysqlTransactionManager"
        class = "org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name = "dataSource" ref = "mysqlDataSource"/>
  </bean>
  <!-- 声明式事物 -->
  <tx:advice id = "mysqlTxAdvice" transaction-manager = "mysqlTransactionManager">
    <tx:attributes>
      <!-- find，query，list，search，get 开头的方法,开启只读,提高数据库访问性能 -->
      <tx:method name = "find*" propagation = "NOT_SUPPORTED" read-only = "true"/>
      <tx:method name = "get*" propagation = "NOT_SUPPORTED" read-only = "true"/>
      <tx:method name = "list*" propagation = "NOT_SUPPORTED" read-only = "true"/>
      <tx:method name = "query*" propagation = "NOT_SUPPORTED" read-only = "true"/>
      <tx:method name = "search*" propagation = "NOT_SUPPORTED" read-only = "true"/>
      <!-- 对save，insert，update，delete 开头的方法进行事务管理,只要有异常就回滚 -->
      <tx:method name = "create*" propagation = "REQUIRES_NEW"
                 rollback-for = "java.lang.Throwable"/>
      <tx:method name = "delete*" propagation = "REQUIRES_NEW"
                 rollback-for = "java.lang.Throwable"/>
      <tx:method name = "do*" propagation = "REQUIRES_NEW" rollback-for = "java.lang.Throwable"/>
      <tx:method name = "insert*" propagation = "REQUIRES_NEW"
                 rollback-for = "java.lang.Throwable"/>
      <tx:method name = "save*" propagation = "REQUIRES_NEW" rollback-for = "java.lang.Throwable"/>
      <tx:method name = "update*" propagation = "REQUIRES_NEW"
                 rollback-for = "java.lang.Throwable"/>
      <!-- 对其他方法 使用默认的事务管理 -->
      <tx:method name = "*" propagation = "SUPPORTS"/>
    </tx:attributes>
  </tx:advice>
  <!-- 事务 AOP 配置 -->
  <aop:config proxy-target-class = "true">
    <aop:pointcut id = "mysqlInterceptorPointCuts"
                  expression = "execution( * com.bbf.frame.service.api.impl.*Impl.*(..))"/>
    <aop:advisor advice-ref = "mysqlTxAdvice" pointcut-ref = "mysqlInterceptorPointCuts"/>
  </aop:config>
</beans>

将多个xml，import到一个xml中，目的是减少复杂度。入口类加入注解@ImportResource(locations = {“classpath:config/conf.xml”})。

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns = "http://www.springframework.org/schema/beans"
       xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation = "http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
  <import resource = "datasource/master_transaction.xml"/>
  <import resource = "datasource/sqlserver_transaction.xml"/>
  <import resource = "datasource/mysql_transaction.xml"/>
</beans>