工程项目中打印SQL的执行时间的两种方式

落日映苍穹つ 2023-05-28 06:48 8阅读 0赞

## 1.使用log4jdbc打印SQL执行时间 ##

本人项目使用的是apollo配置文件的：

1.导入需要的maven包
    
            <dependency>
                <groupId>org.bgee.log4jdbc-log4j2</groupId>
                <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
                <version>1.16</version>
            </dependency>

2.在resources中创建 log4jdbc.log4j2.properties 文件，在文件中添加以下内容：
    
    log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

3.修改数据库链接与驱动
    连接：
    spring.datasource.url = jdbc:log4jdbc:mysql://xxx:3306/db?
    useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE
    驱动：
    spring.datasource.driver-class-name = net.sf.log4jdbc.sql.jdbcapi.DriverSpy

4.修改resources下的logback-spring.xml文件，没有则创建
    
    配置文件中，添加扫描logback-spring.xml的配置
    logging.config = classpath:logback-spring.xml
    
        
        <logger name="jdbc.connection" level="OFF"/>
        <logger name="jdbc.resultset" level="OFF"/>
        <logger name="jdbc.resultsettable" level="OFF"/>
        <logger name="jdbc.audit" level="OFF"/>
        
        <logger name="jdbc.sqltiming" level="INFO"/>
        <logger name="jdbc.sqlonly" level="OFF"/>

项目结构如下：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXNodWFpNTI4_size_16_color_FFFFFF_t_70]

本人所有配置信息如下：
    
    spring.application.name = braintrain-kepler
    spring.cloud.config.profile = test
    spring.datasource.url = jdbc:log4jdbc:mysql://XXX:3306/db?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE
    spring.datasource.username = root
    spring.datasource.password = root
    spring.datasource.type = com.zaxxer.hikari.HikariDataSource
    spring.datasource.driver-class-name = net.sf.log4jdbc.sql.jdbcapi.DriverSpy
    spring.datasource.hikari.maximum-pool-size = 200
    spring.datasource.hikari.minimum-idle = 5
    spring.datasource.hikari.idle-timeout = 180000
    spring.datasource.hikari.connection-timeout = 30000
    spring.datasource.hikari.max-lifetime = 1800000
    spring.datasource.hikari.connection-test-query = SELECT 1
    spring.datasource.hikari.auto-commit = true
    spring.redis.database = 3
    spring.redis.host = XXXXX
    spring.redis.port = 6379
    spring.redis.password = root
    spring.redis.timeout = 5000
    spring.redis.jedis.pool.max-active = 8
    spring.redis.jedis.pool.max-wait = -1
    spring.redis.jedis.pool.max-idle = 8
    spring.redis.jedis.pool.min-idle = 0
    spring.mvc.date-format = yyyy-MM-dd HH:mm:ss
    spring.swagger.title = XXX
    spring.swagger.description = XXX
    spring.swagger.terms-of-service-url = 
    spring.swagger.contact-name = 
    spring.swagger.contact-url = 
    spring.swagger.contact-email = XXX@xxx.com
    spring.swagger.license = Apache 2.0
    spring.swagger.license-url = http://www.apache.org/licenses/LICENSE-2.0.html
    spring.swagger.version = 1.0.0
    spring.swagger.base-package = com.XXXX.controller
    spring.swagger.enable = true
    spring.jackson.date-format = yyyy-MM-dd HH:mm:ss
    spring.jackson.time-zone = GMT+8
    mybatis-plus.configuration.cache-enabled = true
    mybatis-plus.configuration.lazyLoadingEnabled = true
    mybatis-plus.configuration.map-underscore-to-camel-case = true
    mybatis-plus.configuration.multipleResultSetsEnabled = true
    mybatis-plus.global-config.db-config.id-type = id_worker
    mybatis-plus.global-config.db-config.logic-delete-value = 1
    mybatis-plus.global-config.db-config.logic-not-delete-value = 0
    mybatis-plus.global-config.banner = false
    mybatis-plus.mapper-locations = classpath*:mapper/*.xml
    mybatis-plus.typeAliasesPackage = com.XXXX.model
    mybatis-plus.configuration.log-impl = org.apache.ibatis.logging.stdout.StdOutImpl
    mybatis.showLog = true
    management.security.enabled = false
    server.port = ${port:8080}
    logging.config = classpath:logback-spring.xml
    spring.logback.console = true
    spring.logback.config = 
    loginControl = false
    requestAuthControl = true
    eureka.client.serviceUrl.defaultZone = http://eureka-test.XXXX.com/eureka/
    eureka.instance.prefer-ip-address = true
    eureka.instance.leaseRenewalIntervalInSeconds = 5
    eureka.instance.leaseExpirationDurationInSeconds = 10
    feign.client.config.default.connectTimeout = 10000
    feign.client.config.default.readTimeout = 60000
    feign.client.config.default.loggerLevel = basic
    feign.client.config.default.decode404 = false
    feign.okhttp.enabled = true
    feign.httpclient.max-connections = 200
    feign.httpclient.max-connections-per-route = 50
    feign.hystrix.enabled = true
    hystrix.command.default.execution.timeout.enabled = true
    hystrix.command.default.execution.isolation.thread.timeoutInMilliseconds = 10000
    hystrix.threadpool.default.coreSize = 100
    gateway.ribbon.NIWSServerListClassName = com.netflix.loadbalancer.ConfigurationBasedServerList
    gateway.ribbon.listOfServers = open-test.XXXX.com
    ribbon.OkToRetryOnAllOperations = false
    ribbon.ReadTimeout = 10000
    ribbon.ConnectTimeout = 3000
    ribbon.MaxAutoRetries = 0
    ribbon.MaxAutoRetriesNextServer = 0

## 2.使用拦截器来打印sql执行时间 ##

代码如下：
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.ibatis.executor.statement.StatementHandler;
    import org.apache.ibatis.mapping.BoundSql;
    import org.apache.ibatis.mapping.ParameterMapping;
    import org.apache.ibatis.plugin.*;
    import org.apache.ibatis.session.ResultHandler;
    import org.springframework.stereotype.Component;
    
    import java.sql.Statement;
    import java.util.List;
    import java.util.Properties;
    
    /**
     * @Author: ws
     * @Date: 2020/3/28 10:42
     */
    
    /**
     * Sql执行时间记录拦截器
     *
     * @author zk
     * 2019年6月27日17:05:28
     */
    @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
    @Slf4j
    public class SqlExecuteTimeCountInterceptor implements Interceptor {
        /**
         * 打印的参数字符串的最大长度
         */
        private final static int MAX_PARAM_LENGTH = 50;
    
        /**
         * 记录的最大SQL长度
         */
        private final static int MAX_SQL_LENGTH = 2000;
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable {
            Object target = invocation.getTarget();
            long startTime = System.currentTimeMillis();
            StatementHandler statementHandler = (StatementHandler) target;
            try {
                return invocation.proceed();
            } finally {
                long endTime = System.currentTimeMillis();
                long timeCount = endTime - startTime;
    
                BoundSql boundSql = statementHandler.getBoundSql();
                String sql = boundSql.getSql();
                Object parameterObject = boundSql.getParameterObject();
                List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
    
                // 格式化Sql语句，去除换行符，替换参数
                sql = formatSQL(sql, parameterObject, parameterMappingList);
                log.info("执行 SQL：[ , {} ]执行耗时[ {} ms]", sql, timeCount);
            }
        }
    
    
        /**
         * 格式化/美化 SQL语句
         *
         * @param sql                  sql 语句
         * @param parameterObject      参数的Map
         * @param parameterMappingList 参数的List
         * @return 格式化之后的SQL
         */
        private String formatSQL(String sql, Object parameterObject, List<ParameterMapping> parameterMappingList) {
            // 输入sql字符串空判断
            if (sql == null || sql.length() == 0) {
                return "";
            }
            // 美化sql
            sql = beautifySql(sql);
            // 不传参数的场景，直接把sql美化一下返回出去
            if (parameterObject == null || parameterMappingList == null || parameterMappingList.size() == 0) {
                return sql;
            }
            return LimitSQLLength(sql);
        }
    
    
        /**
         * 返回限制长度之后的SQL语句
         *
         *
         * @param sql 原始SQL语句
         */
        private String LimitSQLLength(String sql) {
            if (sql == null || sql.length() == 0) {
                return "";
            }
            if (sql.length() > MAX_SQL_LENGTH) {
                return sql.substring(0, MAX_SQL_LENGTH);
            } else {
                return sql;
            }
        }
    
    
        @Override
        public Object plugin(Object target) {
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) {
    
        }
    
    
        /**
         * 替换SQL 中? 所对应的值, 只保留前50个字符
         *
         * @param sql     sql语句
         * @param valueOf ?对应的值
         */
        private String replaceValue(String sql, String valueOf) {
            //超过50个字符只取前50个
            if (valueOf != null && valueOf.length() > MAX_PARAM_LENGTH) {
                valueOf = valueOf.substring(0, MAX_PARAM_LENGTH);
            }
            sql = sql.replaceFirst("\\?", valueOf);
            return sql;
        }
    
        /**
         * 美化sql
         *
         * @param sql sql语句
         */
        private String beautifySql(String sql) {
            sql = sql.replaceAll("[\\s\n ]+", "  ");
            return sql;
        }
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXNodWFpNTI4_size_16_color_FFFFFF_t_70]: /images/20230528/b6817ffe38404330b4c630722b61d531.png