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

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

方法一

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

方法二

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 ................

}

from:https://blog.csdn.net/qq_43842093/article/details/127201100

M	T	W	T	F	S	S
« Oct
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30