SpringBoot之JdbcTemplate输出完整SQL日志

applicatio.yml开启日志功能

jdbc-log:
  # 开启完整SQL日志输出功能
  enabled: true

logging:
  level:
  	# 切面类路径，日志级别为DEBUG，因为SpringBoot默认日志级别为INFO
    com.xxx.xxx.JdbcTemplateAspect: DEBUG
1
2
3
4
5
6
7
8

日志切面

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.ArrayUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
@Aspect
@Component
@ConditionalOnProperty(prefix = "jdbc-log", value = "enabled", havingValue = "true")
public class JdbcTemplateAspect {
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    @Autowired
    private Environment env;

    @Before(value = "execution(* org.springframework.jdbc.core.JdbcTemplate.*(..))")
    public void afterQuery(JoinPoint joinPoint) {
        String[] activeProfiles = env.getActiveProfiles();
        String activeProfile = "";
        if (activeProfiles.length > 0) {
            activeProfile = activeProfiles[0];
        }
        try {
            Object[] args = joinPoint.getArgs();
            String sql = ((String) args[0]).replaceAll("\\s+", " ");
            if (ArrayUtils.isNotEmpty(args) && args.length == 3 && args[2] instanceof Object[]) {
                Object[] params = (Object[]) args[2];
                List<String> paramList = new ArrayList<>();
                for (Object propertyValue : params) {
                    paramList.add(this.formatParam(propertyValue));
                }
                //转译百分号
                if (sql.contains("%")) {
                    //如果参数不一致直接返回SQL
                    Pattern pattern = Pattern.compile("\\?");
                    Matcher matcher = pattern.matcher(sql);
                    int count = 0;
                    while (matcher.find()) {
                        count++;
                    }
                    if (count == 0 || params.length == 0) {
                        log.debug("SQL:{}", sql);
                        return;
                    }
                    if (params.length != count) {
                        log.error("SQL:{}", sql);
                        log.error("SQL parameters:{}", params);
                        return;
                    }
                    sql = sql.replaceAll("%", "%%");
                }

                sql = sql.replaceAll("\\?", "%s");
                sql = String.format(sql, paramList.toArray());
                log.debug("execute SQL:{}", sql);
            } else {
                log.debug("other execute SQL:{}\n params:{}", sql, ArrayUtils.toString(args));
            }
        } catch (Exception e) {
            log.error("Analysis SQL Exception:", e);
        }
    }

    private String formatParam(Object object) {
        if (object == null) {
            return "null";
        }
        if (object instanceof String) {
            return formatString((String) object);
        }
        if (object instanceof Date) {
            return formatDate((Date) object);
        }
        if (object instanceof LocalDate) {
            return formatLocalDate((LocalDate) object);
        }
        if (object instanceof LocalDateTime) {
            return formatLocalDateTime((LocalDateTime) object);
        }
        return object.toString();
    }

    private static String formatString(String str) {
        return "'" + str + "'";
    }

    private String formatDate(Date date) {
        return "'" + DATE_TIME_FORMATTER.format(date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime()) + "'";
    }

    private String formatLocalDate(LocalDate date) {
        return "'" + DATE_FORMATTER.format(date) + "'";
    }

    private String formatLocalDateTime(LocalDateTime dateTime) {
        return "'" + DATE_TIME_FORMATTER.format(dateTime) + "'";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

启动项目，调用某个JdbcTemplate类里面的方法就会输出完整SQL到控制台，如下内容

execute SQL:select * from table where id=1
1