真正的光明决不是永没有黑暗的时间,只是永不被黑暗所掩蔽罢了。真正的英雄决不是永没有卑下的情操,只是永不被卑下的情操所屈服罢了。——《约翰 • 克利斯朵夫》
1、引言
开发时,在控制台打印SQL语句的执行时间和语句对于调试bug和优化SQL语句极为重要。本文主要介绍在SpringBoot框架下,通过注解@Configuration注解配置和mybatis拦截器配置打印SQL执行时间。
2、SQL执行时间
import java.sql.Statement;
import java.util.Properties;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
@Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
@Signature(type = StatementHandler.class, method = "batch", args = { Statement.class })})
@Component
@Configuration
public class SqlStatementInterceptor implements Interceptor {
private static final Logger LOGGER = LoggerFactory.getLogger(SqlStatementInterceptor.class);
@Override
public Object intercept(Invocation invocation){
// 开始时间
long start = System.currentTimeMillis();
invocation.getArgs();
try {
return invocation.proceed();
} catch (Exception e) {
LOGGER.error("执行失败!", e);
return null;
} finally {
long end = System.currentTimeMillis();
long time = end - start;
LOGGER.info("cost time {}ms", time);
}
}
@Override
public Object plugin(Object arg0) {
return Plugin.wrap(arg0, this);
}
@Override
public void setProperties(Properties arg0) {
}
}
然后需要将拦截插件SqlSessionFactory的Bean中管理:
import java.util.Properties;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
import com.github.pagehelper.PageHelper;
@Configuration
@ConditionalOnClass({ SqlSessionFactory.class, SqlSessionFactoryBean.class})
@EnableConfigurationProperties(MybatisProperties.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class MybatisAutoConfiguration {
private static final Logger LOGGER = LoggerFactory.getLogger(MybatisAutoConfiguration.class);
@Autowired
private MybatisProperties properties;
@Autowired
private ResourceLoader resourceLoader = new DefaultResourceLoader();
@PostConstruct
public void checkConfigFileExists() {
if (this.properties.isCheckConfigLocation()) {
Resource resource = this.resourceLoader.getResource(this.properties.getConfig());
Assert.state(resource.exists(), "Cannot find config location: " + resource
+ " (please add config file or check your Mybatis " + "configuration)");
}
}
@Bean(name = "sqlSessionFactory")
@ConditionalOnMissingBean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
if (StringUtils.hasText(this.properties.getConfig())) {
bean.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfig()));
} else {
// 加入sql语句拦截器
bean.setPlugins(new Interceptor[] {pageHelper(), new SqlStatementInterceptor()});
bean.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
bean.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
bean.setMapperLocations(this.properties.getMapperLocations());
}
return bean.getObject();
}
@Bean
@ConditionalOnMissingBean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory, this.properties.getExecutorType());
}
/**
* 分页插件
*
* @param
* @return
*/
@Bean
public PageHelper pageHelper() {
LOGGER.info("注册MyBatis分页插件PageHelper");
PageHelper pageHelper = new PageHelper();
Properties p = new Properties();
p.setProperty("offsetAsPageNum", "true");
p.setProperty("rowBoundsWithCount", "true");
p.setProperty("reasonable", "true");
pageHelper.setProperties(p);
return pageHelper;
}
}