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

1.Springboot 整合 druid

引入依賴：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

java config 配置：

@Configuration
@EnableConfigurationProperties(DruidPropertityConfig.class)
public class DruidConfig {

	// 日志
	private final Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private DruidPropertityConfig propertityConfig;

	/**
	 * druid屬性配置 Springboot 默認使用org.apache.tomcat.jdbc.pool.DataSource資料源，默認配置如下：
	 * Springboot默認支持4種資料源型別，定義在
	 * org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
	 * 中，分別是： org.apache.tomcat.jdbc.pool.DataSource
	 * com.zaxxer.hikari.HikariDataSource org.apache.commons.dbcp.BasicDataSource
	 * org.apache.commons.dbcp2.BasicDataSource 對于這4種資料源，當 classpath
	 * 下有相應的類存在時，Springboot 會通過自動配置為其生成DataSource Bean，DataSource
	 * Bean默認只會生成一個，四種資料源型別的生效先后順序如下：Tomcat--> Hikari --> Dbcp --> Dbcp2 ，
	 * @return
	 */
	@Bean(name = "dataSource")
	@Primary
	public DataSource druidDataSource() {

		logger.info("dataSource propertityConfig:{}", propertityConfig);

		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setUrl(propertityConfig.getUrl());
		dataSource.setUsername(propertityConfig.getUsername());
		dataSource.setPassword(propertityConfig.getPassword());
		dataSource.setDriverClassName(propertityConfig.getDriverClassName());
		// configuration
		dataSource.setInitialSize(propertityConfig.getInitialSize());
		dataSource.setMinIdle(propertityConfig.getMinIdle());
		dataSource.setMaxActive(propertityConfig.getMaxActive());
		dataSource.setMaxWait(propertityConfig.getMaxWait());
		dataSource.setTimeBetweenEvictionRunsMillis(propertityConfig.getTimeBetweenEvictionRunsMillis());
		dataSource.setMinEvictableIdleTimeMillis(propertityConfig.getMinEvictableIdleTimeMillis());
		dataSource.setTestWhileIdle(propertityConfig.isTestWhileIdle());
		dataSource.setTestOnBorrow(propertityConfig.isTestOnBorrow());
		dataSource.setTestOnReturn(propertityConfig.isTestOnReturn());
		dataSource.setPoolPreparedStatements(propertityConfig.isPoolPreparedStatements());
		dataSource.setMaxPoolPreparedStatementPerConnectionSize(
				propertityConfig.getMaxPoolPreparedStatementPerConnectionSize());
		dataSource.setValidationQuery(propertityConfig.getValidationQuery());
		return dataSource;
	}

	/**
	 * 注冊一個druidStatViewServlet
	 *
	 * @return
	 */
	@Bean
	public ServletRegistrationBean druidStatViewServlet() {
		ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),
				"/druid/*");
		servletRegistrationBean.addInitParameter("allow", "*");
		servletRegistrationBean.addInitParameter("loginUsername", "admin");
		servletRegistrationBean.addInitParameter("loginPassword", "password");
		servletRegistrationBean.addInitParameter("resetEnable", "false");
		return servletRegistrationBean;
	}

	/**
	 * 注冊一個druidStatFilter
	 *
	 * @return
	 */
	@Bean
	public FilterRegistrationBean druidStatFilter() {
		FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
		filterRegistrationBean.addUrlPatterns("/*");
		filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
		return filterRegistrationBean;
	}

}

配置屬性：

@ConfigurationProperties(prefix = "jdbc.datasource")
@Data
public class DruidPropertityConfig {

    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String validationQuery;
}

application.yml

jdbc:
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql:XXXX
    username: xxxx
    password: xxxx
    #初始連接數
    initialSize: 20
    #最小空閑連接數
    minIdle: 20
    最大連接池數量
    maxActive: 100
    #獲取鏈接超時時間
    maxWait: 60000
    #每30秒運行一次空閑連接回收器
    timeBetweenEvictionRunsMillis: 30000
    #池中的連接空閑30分鐘后被回收
    minEvictableIdleTimeMillis: 1800000
    #此項配置為true即可，不影響性能，并且保證安全性，意義為：申請連接的時候檢測，如果空閑時間大于timeBetweenEvictionRunsMillis，執行validationQuery檢測連接是否有效
    testWhileIdle: true
    #獲取鏈接的時候，不校驗是否可用，開啟會有損性能
    testOnBorrow: false
    #歸還鏈接到連接池的時候校驗鏈接是否可用
    testOnReturn: false
    #開啟游標快取（mysql不建議開啟）
    #poolPreparedStatements: true
    #maxPoolPreparedStatementPerConnectionSize: 100
    filters: stat,wall,slf4j
    #檢測資料庫鏈接是否有效，必須配置
    validationQuery: select 1

常用資料庫validationQuery檢查陳述句

基本配置引數說明：