国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

SpringBoot+Mybatis配置Druid多數(shù)據(jù)源

Songlcy / 3410人閱讀

摘要:多數(shù)據(jù)源,一般用于對接多個業(yè)務上獨立的數(shù)據(jù)庫可能異構數(shù)據(jù)庫。這也就導致異構數(shù)據(jù)庫的檢查也是類似問題。內容略數(shù)據(jù)源多數(shù)據(jù)源,涉及到異構數(shù)據(jù)庫,必須明確指定,否則的轉換出錯取值內容可參考初始連接數(shù)最大連接池數(shù)量。

開篇之前,說一句題外話。多數(shù)據(jù)源和動態(tài)數(shù)據(jù)源的區(qū)別。

多數(shù)據(jù)源,一般用于對接多個業(yè)務上獨立的數(shù)據(jù)庫(可能異構數(shù)據(jù)庫)。

動態(tài)數(shù)據(jù)源,一般用于大型應用對數(shù)據(jù)切分。

配置參考

如何配置多數(shù)據(jù)源,網(wǎng)上教程一大堆。可參考 SpringBoot+MyBatis多數(shù)據(jù)源最簡解決方案。

問題描述

在實際開發(fā)配置中發(fā)現(xiàn),如果要啟用Druid的防火墻監(jiān)控(WallFilter)和統(tǒng)計監(jiān)控(StatFilter),多個異構數(shù)據(jù)源就會出錯,錯誤信息如下:

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

跟蹤Druid的源碼,發(fā)現(xiàn)了問題。

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

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

所有的檢查sql工作,都在checkInternal方法中完成,而provider對象在執(zhí)行init初始化之后就再也沒有改變了。這也就導致異構數(shù)據(jù)庫的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防火墻過濾器
 * 

使用多類型數(shù)據(jù)源時,因共用WallProvider解析器,導致判斷數(shù)據(jù)源類型出錯

* @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 數(shù)據(jù)源 * @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不在當前類定義,繼續(xù)向上轉型 } } } @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統(tǒng)計監(jiān)控過濾器
 * 

使用多類型數(shù)據(jù)源時,因沒有及時清空dbType,導致判斷數(shù)據(jù)源類型出錯

* @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優(yōu)先。
另外兩個注解@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統(tǒng)計監(jiān)控過濾器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();
  }
附錄 數(shù)據(jù)源配置類
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;

/**
 * 配置從數(shù)據(jù)源
 * @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 數(shù)據(jù)源
   * @return SqlSessionFactory
   * @throws Exception 創(chuàng)建SqlSessionFactory發(fā)生異常
   */
  @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);
  }
}
配置文件

為了其它數(shù)據(jù)源配置的相對獨立性,多帶帶保存為一個文件mysql.properties。
在入口類上,定義@PropertySource,本文在主數(shù)據(jù)源之外,又定義了兩個數(shù)據(jù)源。

@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數(shù)據(jù)源
############################################
# 多數(shù)據(jù)源,涉及到異構數(shù)據(jù)庫,必須明確指定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

# 初始連接數(shù)
datasource.druid.mysql.initial-size=5
#最大連接池數(shù)量。default=8+
datasource.druid.mysql.max-active=20
# 獲取連接時最大等待時間,單位毫秒。
# 配置了maxWait之后,缺省啟用公平鎖,并發(fā)效率會有所下降。
# 如果需要可以通過配置useUnfairLock屬性為true使用非公平鎖
datasource.druid.mysql.max-wait=60000
# 開啟池的prepared statement池功能,PSCache對支持游標的數(shù)據(jù)庫性能提升巨大
# 如果用Oracle, 則把poolPreparedStatements配置為true, mysql 5.5之后建議true
datasource.druid.mysql.pool-prepared-statements=true
# 要啟用PSCache,必須配置大于0,當大于0時,poolPreparedStatements自動觸發(fā)修改為true。
# 在Druid中,會存在Oracle下PSCache占用內存過多的問題,可以把這個數(shù)據(jù)配置大一些,比如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實現(xiàn)的不是很合理,不建議在mysql下配置此參數(shù)
datasource.druid.mysql.validation-query-timeout=1000
# 是否在從池中取出連接前進行檢驗。如果檢驗失敗,則從池中去除連接并嘗試取出另一個
# 注意: 設置為true后,validation-query參數(shù)必須設置
datasource.druid.mysql.test-on-borrow=false
# 是否在歸還連接池前進行檢驗
# 注意: 設置為true后,validation-query參數(shù)必須設置
datasource.druid.mysql.test-on-return=false
# 建議配置為true,不影響性能,并且保證安全性。
# 申請連接的時候檢測,如果空閑時間大于timeBetweenEvictionRunsMillis,
# 執(zhí)行validationQuery檢測連接是否有效,validation-query參數(shù)必須設置。default=false
datasource.druid.mysql.test-while-idle=true
# 連接池中的minIdle數(shù)據(jù)以內的連接,空閑時間超過minEvictableIdleTimeMillis,則會執(zhí)行keepAlive操作。default=false
datasource.druid.mysql.keep-alive=true
#配置間隔多久才進行一次檢測,檢測需要關閉的空閑連接,單位是毫秒 default=1分鐘
#有兩個含義:
# (1)Destroy線程會檢測連接的間隔時間,如果連接空閑時間大于等于minEvictableIdleTimeMillis則關閉物理連接
# (2)testWhileIdle的判斷依據(jù),詳細看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的監(jiān)控數(shù)據(jù)
datasource.druid.mysql.use-global-data-source-stat=false
事務配置

這個因人而異,我是更喜歡xml方式配置事務。



  
  
  
    
  
  
  
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    
  
  
  
    
    
  

將多個xml,import到一個xml中,目的是減少復雜度。入口類加入注解@ImportResource(locations = {"classpath:config/conf.xml"})。



  
  
  

文章版權歸作者所有,未經(jīng)允許請勿轉載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/69222.html

相關文章

  • 單手擼了個springboot+mybatis+druid

    摘要:配置想想,我們需要哪些數(shù)據(jù)庫要用到,數(shù)據(jù)庫連接池要用到橋接器要用到,因此要倉庫點我去倉庫中找到搜索這些加進去。 本文旨在用最通俗的語言講述最枯燥的基本知識 最近身邊的程序員掀起了學習springboot的熱潮,說什么學會了springboot在大街上就可以橫著走、什么有了springboot媽媽再也不擔心我的編程了、什么BAT都喜歡的框架...聽得作者那個心癢癢的,于是找了個時間,下載...

    adie 評論0 收藏0
  • SpringBoot進階教程 | 第四篇:整合Mybatis實現(xiàn)數(shù)據(jù)源

    這篇文章主要介紹,通過Spring Boot整合Mybatis后如何實現(xiàn)在一個工程中實現(xiàn)多數(shù)據(jù)源。同時可實現(xiàn)讀寫分離。 準備工作 環(huán)境: windows jdk 8 maven 3.0 IDEA 創(chuàng)建數(shù)據(jù)庫表 在mysql中創(chuàng)建student庫并執(zhí)行下面查詢創(chuàng)建student表 -- ---------------------------- -- Table structure for stud...

    AZmake 評論0 收藏0
  • springboot系列】springboot整合獨立模塊Druid + mybatis-plus

    摘要:申請連接時執(zhí)行檢測連接是否有效,做了這個配置會降低性能。作者在版本中使用,通過監(jiān)控界面發(fā)現(xiàn)有緩存命中率記錄,該應該是支持。允許和不允許單條語句返回多個數(shù)據(jù)集取決于驅動需求使用列標簽代替列名稱。需要驅動器支持。將自動映射所有復雜的結果。 項目github地址:https://github.com/5-Ason/aso... 具體可看 ./db/db-mysql 模塊 本文主要實現(xiàn)的是對...

    RobinTang 評論0 收藏0

發(fā)表評論

0條評論

最新活動
閱讀需要支付1元查看
<