526互联

【Druid】数据库连接超时

发布时间 2023-07-19 20:34:05作者: aaacarrot

现象：
接口请求，10秒钟超时，看了一下，是sql查询耗时太长了，数据还没来得及返回，连接就断开了。
连接断开，上层应用直接报错。。

这些问题，都是。。。知道答案后，就觉得，emmm，好简单。知道答案前，....tmd, 这啥玩意儿。。。。

直接说答案吧：
数据库连接超时，那就配置超时时长，准确地说是 druid 的超时时长。。。
要设置 druid 的 connectTimeout 、socketTimeout 这两个配置项，
嗯。单这样说，有点。。。

一份druid 配置

druid:
  url: jdbc:mysql://10.12.1.9:3306/student?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
  username: username
  password: passwordxxxx
  useSSL: false
  initial-size: 50
  min-idle: 50
  max-active: 200
  connectTimeout: 600000  #1.2.12版本需要单独配置才生效
  socketTimeout: 600000   #1.2.12版本需要单独配置才生效

有了配置，不够，需要在代码里面解析配置
于是要写一个：
DruidProperties.java

import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * druid
 */
@ConfigurationProperties(prefix = "druid")
public class DruidProperties {
    private String url;
    private String username;
    private String password;

    private int initialSize;
    private int minIdle;
    private int maxActive;
    private long maxWait;

    private String validationQuery;

    private int connectTimeout;

    private int socketTimeout;

    private boolean testOnBorrow;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getInitialSize() {
        return initialSize;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public long getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(long maxWait) {
        this.maxWait = maxWait;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }


    public int getConnectTimeout() {
        return connectTimeout;
    }

    public void setConnectTimeout(int connectTimeout) {
        this.connectTimeout = connectTimeout;
    }

    public int getSocketTimeout() {
        return socketTimeout;
    }

    public void setSocketTimeout(int socketTimeout) {
        this.socketTimeout = socketTimeout;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }
}

还有 DruidAutoConfiguration.java

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * Created by chu on 2017/3/2.
 */
@Configuration
@EnableConfigurationProperties(DruidProperties.class)
@ConditionalOnClass(DruidDataSource.class)
@ConditionalOnProperty(prefix = "druid", name = "url")
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
public class DruidAutoConfiguration {

    @Autowired
    private DruidProperties properties;
    /**
     * 获取数据源
     * @return DataSource
     */
    @Bean
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(properties.getUrl());
        dataSource.setUsername(properties.getUsername());
        dataSource.setPassword(properties.getPassword());
        if (properties.getInitialSize() > 0) {
            dataSource.setInitialSize(properties.getInitialSize());
        }
        if (properties.getMaxActive() > 0) {
            dataSource.setMaxActive(properties.getMaxActive());
        }
        if (properties.getMaxWait() > 0) {
            dataSource.setMaxWait(properties.getMaxWait());
        }
        if (properties.getMinIdle() > 0) {
            dataSource.setMinIdle(properties.getMinIdle());
        }
        if (properties.getValidationQuery() != null) {
            dataSource.setValidationQuery(properties.getValidationQuery());
        }
        if (properties.getConnectTimeout() > 0) {
            dataSource.setConnectTimeout(properties.getConnectTimeout());
        }
        if (properties.getSocketTimeout() > 0) {
            dataSource.setSocketTimeout(properties.getSocketTimeout());
        }
        dataSource.setTestOnBorrow(properties.isTestOnBorrow());

        try {
            dataSource.init();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return dataSource;
    }

    /**
     * 获取ServletRegistrationBean
     * @return ServletRegistrationBean
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean druidServletRegistration = new ServletRegistrationBean(new DruidStatViewServlet());
        druidServletRegistration.addInitParameter("allow", "127.0.0.1");
        druidServletRegistration.addUrlMappings("/druid/*");
        return druidServletRegistration;
    }

}

一开始，配置文件写到了

spring:
  datasource:
    druid:
      connectTimeout: 600000  #1.2.12版本需要单独配置才生效
      socketTimeout: 600000   #1.2.12版本需要单独配置才生效

然后配置死活不生效。。。

呃，，，， 所以配置文件，和解析类的前缀一定要匹配才行噢！ 不熟悉的同学，其实很容易踩坑。。。。

我就踩了好多次。。。emmmm