Springboot整合druid,日数据量超出500万频繁操作数据时数据库经常断开连接的问题
发表时间:2019-9-24
发布人:葵宇科技
浏览次数:254
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基本配置参数说明: