SpringBoot配置MyBatis的sql执行超时时间(mysql)
2023-09-14 09:06:17 时间
- 当某些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 ................
}
相关文章
- 「mysql优化专题」高可用性、负载均衡的mysql集群解决方案(12)
- MySQL将一张表的某些列数据,复制到另外一张表,并且修改某些内容
- MYSQL查询上级和下级
- MySQL -- 单行函数
- centos下yum搭建安装linux+apache+mysql+php环境
- MySQL中的group_concat函数的使用
- mysql:管理mysql的二进制日志(mysql 8.0.19)
- paip. mysql如何临时 暂时 禁用 关闭 触发器
- 《Springboot极简教程》使用Spring Boot, JPA, Mysql, ThymeLeaf,gradle, Kotlin快速构建一个CRUD Web App
- Ubuntu下(Linux+Apache+MYSQL+PHP, LAMP)环境搭建
- SpringBoot(Spring、SpringMVC)集成Mybatis操作Mysql数据库
- MySQL提权之启动项提权——开机启动的程序,那时候启动的程序权限都是system
- 【项目实战】WebFlux整合r2dbc-mysql实战
- mysql message from server: “Too many connections“ 错误现象 解决方案
- MySQL-第十三章-核心参数优化(详解)
- [MySQL] 解决办法: Error: Transaction test error: file /etc/my.cnf from install of mysql-community-server