springboot2.0+log4jdbc将sql语句完整写入日志文件并分开保存

首先，引入pom依赖：

接着修改application.yml配置文件（具体视情况而定）

driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy

url: jdbc:log4jdbc:mysql://127.0.0.1:3306/xxxx?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

指定log4j2的配置文件路径

log4j2-test.xml的内容如下

1<!-- 日志文件目录、压缩文件目录、日志格式配置 -->
2<properties>
3    <Property name="fileName">c:/logs/xxxx/api</Property>
4    <Property name="fileGz">c:/logs/xxxx/api/7z</Property>
5    <Property name="LOG\_PATTERN">%d{yyyy-MM-dd HH:mm:ss.SSS} \[%thread\] %-5level %logger{36} - %msg%n</Property>
6</properties>
7
8<Appenders>
9    <!-- 输出控制台日志的配置 -->
10    <Console name="console" target="SYSTEM\_OUT">
11        <!--控制台只输出level及以上级别的信息（onMatch），其他的直接拒绝（onMismatch）-->
12        <ThresholdFilter level="debug" onMatch="ACCEPT" onMismatch="DENY"/>
13        <!-- 输出日志的格式 -->
14        <PatternLayout pattern="${LOG\_PATTERN}"/>
15    </Console>
16
17    <!-- 打印出所有的信息，每次大小超过size，则这size大小的日志会自动存入按年份-月份建立的文件夹下面并进行压缩，作为存档 -->
18    <RollingRandomAccessFile name="infoFile" fileName="${fileName}/web-info.log" immediateFlush="false" filePattern="${fileGz}/$${date:yyyy-MM}/%d{yyyy-MM-dd}-%i.web-info.gz">
19        <PatternLayout pattern="${LOG\_PATTERN}"/>
20        <Policies>
21            <SizeBasedTriggeringPolicy size="20 MB"/>
22        </Policies>
23
24        <Filters>
25            <!-- 只记录info和warn级别信息 -->
26            <ThresholdFilter level="error" onMatch="DENY" onMismatch="NEUTRAL"/>
27            <ThresholdFilter level="info" onMatch="ACCEPT" onMismatch="DENY"/>
28        </Filters>
29
30        <!-- 指定每天的最大压缩包个数，默认7个，超过了会覆盖之前的 -->
31        <DefaultRolloverStrategy max="50"/>
32    </RollingRandomAccessFile>
33
34    <!-- 存储所有error信息 -->
35    <RollingRandomAccessFile name="errorFile" fileName="${fileName}/web-error.log" immediateFlush="false" filePattern="${fileGz}/$${date:yyyy-MM}/%d{yyyy-MM-dd}-%i.web-error.gz">
36        <PatternLayout pattern="${LOG\_PATTERN}"/>
37
38        <Policies>
39            <SizeBasedTriggeringPolicy size="50 MB"/>
40        </Policies>
41
42        <Filters>
43            <!-- 只记录error级别信息 -->
44            <ThresholdFilter level="error" onMatch="ACCEPT" onMismatch="DENY"/>
45        </Filters>
46
47        <!-- 指定每天的最大压缩包个数，默认7个，超过了会覆盖之前的 -->
48        <DefaultRolloverStrategy max="50"/>
49    </RollingRandomAccessFile>
50
51    <File name="log4jdbc\_delete" fileName="c:/logs/xxxx/api/log4jdbc\_delete.log">
52        <MarkerFilter marker="LOG4JDBC\_DELETE" onMatch="ACCEPT" onMismatch="DENY"/>
53        <PatternLayout pattern="%d %-5p %t (%c:%L) - %m%n"/>
54    </File>
55    <File name="log4jdbc\_insert" fileName="c:/logs/xxxx/api/log4jdbc\_insert.log">
56        <MarkerFilter marker="LOG4JDBC\_INSERT" onMatch="ACCEPT" onMismatch="DENY"/>
57        <PatternLayout pattern="%d %-5p %t (%c:%L) - %m%n"/>
58    </File>
59    <File name="log4jdbc\_update" fileName="c:/logs/xxxx/api/log4jdbc\_update.log">
60        <MarkerFilter marker="LOG4JDBC\_UPDATE" onMatch="ACCEPT" onMismatch="DENY"/>
61        <PatternLayout pattern="%d %-5p %t (%c:%L) - %m%n"/>
62    </File>
63    <File name="log4jdbc\_select" fileName="c:/logs/xxxx/api/log4jdbc\_select.log">
64        <MarkerFilter marker="LOG4JDBC\_SELECT" onMatch="ACCEPT" onMismatch="DENY"/>
65        <PatternLayout pattern="%d %-5p %t (%c:%L) - %m%n"/>
66    </File>
67    
68</Appenders>
69
70<Loggers>
71    <logger name="org.springframework.aop.framework.CglibAopProxy" level="ERROR" additivity="false"/>
72    <logger name="springfox.documentation" level="ERROR" additivity="false"/>
73    <logger name="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping" level="ERROR" additivity="false"/>
74    <logger name="org.springframework.core.env.PropertySourcesPropertyResolver" level="ERROR" additivity="false"/>
75    <logger name="org.springframework.web.servlet.DispatcherServlet" level="ERROR" additivity="false"/>
76    <logger name="io.undertow.servlet" level="ERROR" additivity="false"/>
77    <logger name="org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod" level="TRACE"/>
78
79    <logger name="jdbc.sqlonly" level="INFO" additivity="false"/>
80    <logger name="jdbc.resultset" level="INFO" additivity="false"/>
81    <logger name="jdbc.connection" level="INFO" additivity="false"/>
82    <logger name="jdbc.audit" level="INFO" additivity="false"/>
83    <logger name="jdbc.sqltiming" level="INFO" additivity="false">
84        <appender-ref ref="console"/>
85    </logger>
86
87    <!--项目日志等级-->
88    <logger name="com.junko" level="DEBUG"/>
89
90    <logger name="log4jdbc.debug" level="INFO" additivity="false">
91        <appender-ref ref="console"/>
92    </logger>
93
94    <!-- 同步异步混合的方式进行写日志 -->
95    <AsyncLogger name="AsyncLogger" level="INFO" includeLocation="true">
96        <appender-ref ref="console" />
97        <appender-ref ref="infoFile" />
98        <appender-ref ref="errorFile" />
99    </AsyncLogger>
100    <asyncRoot level="INFO" includeLocation="true">
101        <appender-ref ref="console" />
102        <appender-ref ref="infoFile" />
103        <appender-ref ref="errorFile" />
104    </asyncRoot>
105
106    <logger name="log4jdbc.log4j2" level="info" additivity="false">
107        <MarkerFilter marker="LOG4JDBC\_OTHER" onMatch="DENY" onMismatch="NEUTRAL"/>
108        <appender-ref ref="log4jdbc\_insert"/>
109        <appender-ref ref="log4jdbc\_delete"/>
110        <appender-ref ref="log4jdbc\_update"/>
111        <appender-ref ref="log4jdbc\_select"/>
112        <appender-ref ref="console"/>
113    </logger>
114</Loggers>

项目运行后，打开目录查看：

增删改查的执行语句已分开保存，再查看log4jdbc_select.log文件：

可以看到，sql语句里的 ? 占位符已经是实际传入的参数了。