SpringBoot中通过mybatis拦截器打印sql执行时间

2022-06-25 11:15:34

真正的光明决不是永没有黑暗的时间,只是永不被黑暗所掩蔽罢了。真正的英雄决不是永没有卑下的情操,只是永不被卑下的情操所屈服罢了。——《约翰 • 克利斯朵夫》

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;
    }
}
  • 作者:chun_soft
  • 原文链接:https://chunsoft.blog.csdn.net/article/details/82532000
    更新时间:2022-06-25 11:15:34