springboot+mybatis+数据库连接池+mysql

Question

求助

我这边有一个springboot的应用，通过浏览器请求后台服务，后台服务通过mybatis查询数据库的数据（数据库服务关闭），由于数据库连接不上连接池会报数据库无法连接，并且重复尝试连接，此时后台服务请求的无法捕获到数据库无法连接的异常整个线程一直处于等待状态，不知道该怎么捕获异常，这个请求资源能够正常释放？

Answer 1

1、先在pom.xml中引入druid依赖包

com.alibaba
druid
1.0.13

2、配置propertie文件

#mybatis.type-aliases-package=com.example.demo.model
#mybatis.mapper-locations=classpath*:com/example/demo/mapper/*.xml
#spring.datasource.url=jdbc:mysql://localhost:3306/day13?useUnicode=true&characterEncoding=utf-8
#spring.datasource.username=root
#spring.datasource.password=
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver

ds1.datasource.url=jdbc:mysql://localhost:3306/day13?useUnicode=true&characterEncoding=utf-8
ds1.datasource.username=root
ds1.datasource.password=
ds1.datasource.driverClassName=com.mysql.jdbc.Driver

ds1.datasource.initialSize=20
ds1.datasource.minIdle=20
ds1.datasource.maxActive=200
ds1.datasource.maxWait=60000
ds1.datasource.timeBetweenEvictionRunsMillis=60000
ds1.datasource.minEvictableIdleTimeMillis=300000
ds1.datasource.testWhileIdle=true
ds1.datasource.testOnBorrow=false
ds1.datasource.testOnReturn=false
ds1.datasource.poolPreparedStatements=true
ds1.datasource.maxPoolPreparedStatementPerConnectionSize=20

3、spring-boot不支持自动配druid连接池，通过定制化DataSource来实现

package com.example.demo.bean;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

@Configuration
@MapperScan(basePackages = DataSourceConfig.PACKAGE,sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {
static final String PACKAGE = "com.example.demo.model";
static final String MAPPER_LOCATION = "classpath*:com/example/demo/mapper/*.xml";

@Value("${ds1.datasource.url}")
private String url;
@Value("${ds1.datasource.username}")
private String username;
@Value("${ds1.datasource.password}")
private String password;
@Value("${ds1.datasource.driverClassName}")
private String driverClassName;

@Value("${ds1.datasource.maxActive}")
private Integer maxActive;
@Value("${ds1.datasource.minIdle}")
private Integer minIdle;
@Value("${ds1.datasource.initialSize}")
private Integer initialSize;
@Value("${ds1.datasource.maxWait}")
private Long maxWait;
@Value("${ds1.datasource.timeBetweenEvictionRunsMillis}")
private Long timeBetweenEvictionRunsMillis;
@Value("${ds1.datasource.minEvictableIdleTimeMillis}")
private Long minEvictableIdleTimeMillis;
@Value("${ds1.datasource.testWhileIdle}")
private Boolean testWhileIdle;
@Value("${ds1.datasource.testWhileIdle}")
private Boolean testOnBorrow;
@Value("${ds1.datasource.testOnBorrow}")
private Boolean testOnReturn;


@Bean(name = "dataSource")
@Primary
public DataSource dataSource(){
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName(driverClassName);
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);

    //连接池配置
    dataSource.setMaxActive(maxActive);
    dataSource.setMinIdle(minIdle);
    dataSource.setInitialSize(initialSize);
    dataSource.setMaxWait(maxWait);
    dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
    dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
    dataSource.setTestWhileIdle(testWhileIdle);
    dataSource.setTestOnBorrow(testOnBorrow);
    dataSource.setTestOnReturn(testOnReturn);

    dataSource.setValidationQuery("SELECT 'x'");
    dataSource.setPoolPreparedStatements(true);
    dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
    try {
        dataSource.setFilters("stat");
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return dataSource;
}

@Bean(name = "transactionManager")
@Primary
public DataSourceTransactionManager transactionManager(){
    return new DataSourceTransactionManager(dataSource());
}

@Bean(name = "sqlSessionFactory")
@Primary
public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
    final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
    sessionFactoryBean.setDataSource(dataSource);
    sessionFactoryBean.setTypeAliasesPackage("com.example.demo.model");
    sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DataSourceConfig.MAPPER_LOCATION));
    return sessionFactoryBean.getObject();
}

}

DruidDBConfig类被@Configuration标注，用作配置信息； DataSource对象被@Bean声明，为Spring容器所管理， @Primary表示这里定义的DataSource将覆盖其他来源的DataSource

4、直接使用