开篇之前，说一句题外话。多数据源和动态数据源的区别。

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

配置参考

如何配置多数据源，网上教程一大堆。可参考 。

问题描述

在实际开发配置中发现，如果要启用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
    
      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防火墙过滤器 * 
使用多类型数据源时，因共用WallProvider解析器，导致判断数据源类型出错
 * @author BBF * @see com.alibaba.druid.wall.WallFilter */public class FrameWallFilter extends WallFilter {  /**   * 用线程安全的ConcurrentHashMap存储WallProvider对象   */  private final Map
    
      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统计监控过滤器 * 
使用多类型数据源时，因没有及时清空dbType，导致判断数据源类型出错
 * @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优先。

/**   * 自定义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。

@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.JdbcConstantsdatasource.druid.mysql.db-type=mysqldatasource.druid.mysql.driver-class-name=com.mysql.jdbc.Driverdatasource.druid.mysql.url=jdbc:mysql://192.168.1.2:3306/bbf?characterEncoding=UTF-8datasource.druid.mysql.username=rootdatasource.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之后建议truedatasource.druid.mysql.pool-prepared-statements=true# 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。# 在Druid中，会存在Oracle下PSCache占用内存过多的问题，可以把这个数据配置大一些，比如100。默认=-1datasource.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=falsedatasource.druid.mysql.test-while-idle=true# 连接池中的minIdle数据以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作。default=falsedatasource.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=100000datasource.druid.mysql.max-evictable-idle-time-millis=200000#合并多个DruidDataSource的监控数据datasource.druid.mysql.use-global-data-source-stat=false

事务配置

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

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