<input id="0qass"><u id="0qass"></u></input>
  • <input id="0qass"><u id="0qass"></u></input>
  • <menu id="0qass"><u id="0qass"></u></menu>

    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檢查語句

    ?數據庫?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

    基本配置參數說明:

    ?

    ?

    ?

    ??2020 CSDN 皮膚主題: 書香水墨 設計師:CSDN官方博客 返回首頁
    多乐彩