真正的光明决不是永没有黑暗的时间,只是永不被黑暗所掩蔽罢了。真正的英雄决不是永没有卑下的情操,只是永不被卑下的情操所屈服罢了。——《约翰 • 克利斯朵夫》
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@ConfigurationpublicclassSqlStatementInterceptorimplementsInterceptor {privatestaticfinal Logger LOGGER = LoggerFactory.getLogger(SqlStatementInterceptor.class);@Overridepublic Objectintercept(Invocation invocation){// 开始时间long start = System.currentTimeMillis();
invocation.getArgs();try {return invocation.proceed();
}catch (Exception e) {
LOGGER.error("执行失败!", e);returnnull;
}finally {long end = System.currentTimeMillis();long time = end - start;
LOGGER.info("cost time {}ms", time);
}
}@Overridepublic Objectplugin(Object arg0) {return Plugin.wrap(arg0,this);
}@OverridepublicvoidsetProperties(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;
importcom.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;
}
}