SpringBoot配置MyBatis的sql执行超时时间（mysql）

Dear 丶 2023-09-30 15:48 23阅读 0赞

*  当某些sql因为不知名原因堵塞时，为了不影响后台服务运行，想要给sql增加执行时间限制，超时后就抛异常，保证后台线程不会因为sql堵塞而堵塞。

### 方法一 ###

yml全局配置：单数据源可以，多数据源时会失效

![img][]

## 方法二 ##

java配置类配置：：成功抛出超时异常。

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    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;
     
    @Configuration
    @MapperScan(basePackages = "top.oldmoon.bill.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
    public class DBConfiguration {
        
     
        @Primary
        @Bean(name = "dataSource")
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource gfDataSource() {
        
            DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
            return dataSource;
        }
     
        @Primary
        @Bean(name = "sqlSessionFactory")
        public SqlSessionFactory gfSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
     
            factoryBean.setDataSource(dataSource);
            factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
     
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setDefaultStatementTimeout(1); // 设置sql执行超时时间：：（秒）
            factoryBean.setConfiguration(configuration);
            SqlSessionFactory sqlSessionFactory = factoryBean.getObject();
            return sqlSessionFactory;
        }
     
        @Primary
        @Bean(name = "transactionManager")
        public DataSourceTransactionManager gfTransactionManager(@Qualifier("dataSource") DataSource dataSource) {
        
            return new DataSourceTransactionManager(dataSource);
        }
     
        @Primary
        @Bean(name = "sqlSessionTemplate")
        public SqlSessionTemplate gfSqlSessionTemplate(
                @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        
            SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
            return sqlSessionTemplate;
        }
    }

Fri Mar 12 15:56:31 CST 2022--方法开始执行！！
    2022-03-12 15:56:38.508 ERROR 24484 --- [nio-8111-exec-3] o.api.advice.ExceptionHandlerAdvice      : 未知异常：
    ### Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    ### The error may exist in file [E:\space-persional\git\oldmoon-frame\oldmoon-app-bill\out\production\resources\mybatis\UserMapper.xml]
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: select * from V_BGD_PWGM_PB_21
    ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    ; Statement cancelled due to timeout or client request; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    org.springframework.dao.QueryTimeoutException:

{
        
        "resultCode": "2",
        "resultMsg": "未知异常，请联系管理员！",
        "token": null,
        "resultInfo": "\r\n### Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request\r\n### The error may exist in file ................
    }

[img]: https://img-blog.csdnimg.cn/20210312155517770.png