• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

Springboot整合druid,日数据量超出500万频繁操作数据时数据库经常断开连接的问题

互联网 diligentman 2周前 (10-17) 10次浏览

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检查语句

 数据库  validationQuery
 Oracle  select 1 from dual
 mysql  select 1
 DB2  select 1 from sysibm.sysdummy1
 microsoft sql  select 1
 hsqldb  select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
 postgresql  select version()
 ingres  select 1
 derby  select 1
 H2  select 1

基本配置参数说明:

Springboot整合druid,日数据量超出500万频繁操作数据时数据库经常断开连接的问题

 

 

 


喜欢 (0)