sql

Springboot整合druid,日資料量超出500萬頻繁操作資料時資料庫經常斷開連線的問題

1. 整合 druid

引入依賴:

  1. <dependency>
  2. <groupId>com.alibaba</groupId>
  3. <artifactId>druid--boot-starter</artifactId>
  4. </dependency>

config 配置:

  1. @Configuration
  2. @EnableConfigurationProperties(DruidPropertityConfig.class)
  3. public class DruidConfig {
  4. // 日誌
  5. private final Logger logger = LoggerFactory.getLogger(getClass());
  6. @Autowired
  7. private DruidPropertityConfig propertityConfig;
  8. /**
  9. * druid屬性配置 Springboot 預設使用org.apache.tomcat.jdbc.pool.DataSource資料來源,預設配置如下:
  10. * Springboot預設支援4種資料來源型別,定義在
  11. * org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
  12. * 中,分別是: org.apache.tomcat.jdbc.pool.DataSource
  13. * com.zaxxer.hikari.HikariDataSource org.apache.commons.dbcp.BasicDataSource
  14. * org.apache.commons.dbcp2.BasicDataSource 對於這4種資料來源,當 classpath
  15. * 下有相應的類存在時,Springboot 會透過自動配置為其生成DataSource Bean,DataSource
  16. * Bean預設只會生成一個,四種資料來源型別的生效先後順序如下:Tomcat--> Hikari --> Dbcp --> Dbcp2 。
  17. * @return
  18. */
  19. @Bean(name = "dataSource")
  20. @Primary
  21. public DataSource druidDataSource() {
  22. logger.info("dataSource propertityConfig:{}", propertityConfig);
  23. DruidDataSource dataSource = new DruidDataSource();
  24. dataSource.setUrl(propertityConfig.getUrl());
  25. dataSource.setUsername(propertityConfig.getUsername());
  26. dataSource.setPassword(propertityConfig.getPassword());
  27. dataSource.setDriverClassName(propertityConfig.getDriverClassName());
  28. // configuration
  29. dataSource.setInitialSize(propertityConfig.getInitialSize());
  30. dataSource.setMinIdle(propertityConfig.getMinIdle());
  31. dataSource.setMaxActive(propertityConfig.getMaxActive());
  32. dataSource.setMaxWait(propertityConfig.getMaxWait());
  33. dataSource.setTimeBetweenEvictionRunsMillis(propertityConfig.getTimeBetweenEvictionRunsMillis());
  34. dataSource.setMinEvictableIdleTimeMillis(propertityConfig.getMinEvictableIdleTimeMillis());
  35. dataSource.setTestWhileIdle(propertityConfig.isTestWhileIdle());
  36. dataSource.setTestOnBorrow(propertityConfig.isTestOnBorrow());
  37. dataSource.setTestOnReturn(propertityConfig.isTestOnReturn());
  38. dataSource.setPoolPreparedStatements(propertityConfig.isPoolPreparedStatements());
  39. dataSource.setMaxPoolPreparedStatementPerConnectionSize(
  40. propertityConfig.getMaxPoolPreparedStatementPerConnectionSize());
  41. dataSource.setValidationQuery(propertityConfig.getValidationQuery());
  42. return dataSource;
  43. }
  44. /**
  45. * 註冊一個druidStatViewServlet
  46. *
  47. * @return
  48. */
  49. @Bean
  50. public ServletRegistrationBean druidStatViewServlet() {
  51. ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),
  52. "/druid/*");
  53. servletRegistrationBean.addInitParameter("allow", "*");
  54. servletRegistrationBean.addInitParameter("loginUsername", "admin");
  55. servletRegistrationBean.addInitParameter("loginPassword", "password");
  56. servletRegistrationBean.addInitParameter("resetEnable", "false");
  57. return servletRegistrationBean;
  58. }
  59. /**
  60. * 註冊一個druidStatFilter
  61. *
  62. * @return
  63. */
  64. @Bean
  65. public FilterRegistrationBean druidStatFilter() {
  66. FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
  67. filterRegistrationBean.addUrlPatterns("/*");
  68. filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
  69. return filterRegistrationBean;
  70. }
  71. }

配置屬性:

  1. @ConfigurationProperties(prefix = "jdbc.datasource")
  2. @Data
  3. public class DruidPropertityConfig {
  4. private String driverClassName;
  5. private String url;
  6. private String username;
  7. private String password;
  8. private int initialSize;
  9. private int minIdle;
  10. private int maxActive;
  11. private int maxWait;
  12. private int timeBetweenEvictionRunsMillis;
  13. private int minEvictableIdleTimeMillis;
  14. private boolean testWhileIdle;
  15. private boolean testOnBorrow;
  16. private boolean testOnReturn;
  17. private boolean poolPreparedStatements;
  18. private int maxPoolPreparedStatementPerConnectionSize;
  19. private String filters;
  20. private String validationQuery;
  21. }

application.yml

  1. jdbc:
  2. datasource:
  3. driverClassName: com..cj.jdbc.Driver
  4. url: jdbc:mysql:XXXX
  5. username: xxxx
  6. password: xxxx
  7. #初始連線數
  8. initialSize: 20
  9. #最小空閒連線數
  10. minIdle: 20
  11. 最大連線池數量
  12. maxActive: 100
  13. #獲取連結超時時間
  14. maxWait: 60000
  15. #每30秒執行一次空閒連接回收器
  16. timeBetweenEvictionRunsMillis: 30000
  17. #池中的連線空閒30分鐘後被回收
  18. minEvictableIdleTimeMillis: 1800000
  19. #此項配置為true即可,不影響效能,並且保證安全性。意義為:申請連線的時候檢測,如果空閒時間大於timeBetweenEvictionRunsMillis,執行validationQuery檢測連線是否有效
  20. testWhileIdle: true
  21. #獲取連結的時候,不校驗是否可用,開啟會有損效能
  22. testOnBorrow: false
  23. #歸還連結到連線池的時候校驗連結是否可用
  24. testOnReturn: false
  25. #開啟遊標快取(mysql不建議開啟)
  26. #poolPreparedStatements: true
  27. #maxPoolPreparedStatementPerConnectionSize: 100
  28. filters: stat,wall,slf4j
  29. #檢測資料庫連結是否有效,必須配置
  30. validationQuery: select 1

常用資料庫validationQuery檢查語句

 資料庫  validationQuery
 Oracle  select 1 from dual
 mysql  select 1
 DB2  select 1 from sysibm.sysdummy1
 microsoft  select 1
 hsqldb  select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
 postgresql  select version()
 ingres  select 1
 derby  select 1
 H2  select 1

基本配置引數說明:

 

 

 

本文章已修改原文用詞符合繁體字使用者習慣使其容易閱讀

版權宣告:此處為CSDN博主「haohao_ding」的原創文章,依據CC 4.0 BY-SA版權協議,轉載請附上原文出處連結及本宣告。

原文連結:https://blog.csdn.net/haohao_ding/article/details/101297148