spring boot 集成log4jdbc 查看完整sql

不知道你有没有见过如下的sql日志，参数全是？,调试起来真是抓狂。

select id, message_key, message_type, message_content, create_time, update_time, retry_times, message_status
 
 from t_message
 WHERE message_status = 'PENDING'
 
 and message_type = ? 
 order by create_time asc
 limit ?

还好我没可以使用log4jdbc进行简单的配置，以打印完整的sql。

1、添加pom.xml依赖

<dependency>
        <groupId>com.googlecode.log4jdbc</groupId>
        <artifactId>log4jdbc</artifactId>
        <version>1.2</version>
</dependency>

2、替换原有的数据库配置 application.properties

# mysql数据库配置
#spring.datasource.url=jdbc:mysql://localhost:3306/moshop
#spring.datasource.username=root
#spring.datasource.password=123456
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
 
 
# log4jdbc数据库配置
spring.datasource.url=jdbc:log4jdbc:mysql://localhost:3306/moshop
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=net.sf.log4jdbc.DriverSpy

3、这样就配置好log4jdbc，启动程序，可以看到完整sql🌶

2019-05-08 16:39:45 [schedulerFactoryBean_Worker-4] INFO  jdbc.sqlonly - select id, message_key, message_type, message_content, create_time, update_time, retry_times, 
message_status from t_message WHERE message_status = 'PENDING' and message_type = 'MB_Notification' 
order by create_time asc limit 20 

4、默认log4jdbc的日志输出输出resultset，和数据库的链接信息，这些信息在我们项目中没有多大的意义

2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.resultset - 5. ResultSet.new ResultSet returned 
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.audit - 5. Statement.executeQuery(select 'x') returned net.sf.log4jdbc.ResultSetSpy@6cfea904
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.resultset - 5. ResultSet.next() returned true
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.resultset - 5. ResultSet.close() returned 
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.audit - 5. Statement.close() returned 
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.audit - 5. Connection.getAutoCommit() returned true
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.audit - 5. Connection.getHoldability() returned 2
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.audit - 5. Connection.isReadOnly() returned false
2019-05-08 16:40:45 [Druid-ConnectionPool-Create-809458919] INFO  jdbc.audit - 5. Connection.getTransactionIsolation() returned 2
20

可以在logback配置文件中把这些信息关掉

<!--日志打印的包的范围，及分类日志文件存储 -->
    <logger name="com.example" level="INFO" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>
 
    <logger name="jdbc.sqlonly" level="INFO" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>
 
    <logger name="jdbc.resultset" level="ERROR" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>
 
    <logger name="jdbc.connection" level="OFF" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>
 
    <logger name="jdbc.sqltiming" level="OFF" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>
 
    <logger name="jdbc.audit" level="OFF" additivity="false">
        <appender-ref ref="STDOUT" />
    </logger>  

完整的logback.xml

<?xml version="1.0" encoding="UTF-8"?>

<configuration scan="true" scanPeriod="60 seconds" debug="false">

    <contextName>logback</contextName>

    <property name="log.path" value="${LOG_PATH:-.}"/>
    <springProperty scope="context" name="appName" source="spring.application.name" defaultValue="ApplicationName"/>
    <springProperty scope="context" name="applicationStack" source="application.stack" defaultValue="beta"/>
    <springProperty scope="context" name="smtpHost" source="spring.mail.host"/>
    <springProperty scope="context" name="smtpPort" source="spring.mail.port"/>
    <springProperty scope="context" name="mailFrom" source="spring.mail.username"/>
    <springProperty scope="context" name="username" source="spring.mail.username"/>
    <springProperty scope="context" name="mailTo" source="tendency.system.emails"/>
    <springProperty scope="context" name="password" source="spring.mail.password"/>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <!-- encoder默认配置为PartternLayoutEncoder -->
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <charset>UTF-8</charset>
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} -
                %msg%n</pattern>
        </encoder>
    </appender>
    <appender name="logfile"
              class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/server.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/server_%d{yyyy-MM-dd}.%i.log
            </fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy
                    class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>10MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>10</maxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}
                - %msg%n</pattern> -->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %-5level - %msg%n</pattern>
        </encoder>
    </appender>

    <appender name="transaction_file"
              class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/transaction.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/transaction_%d{yyyy-MM-dd}.%i.log
            </fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy
                    class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>10MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>10</maxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}
                - %msg%n</pattern> -->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %-5level - %msg%n</pattern>
        </encoder>
    </appender>

    <appender name="MAIL" class="ch.qos.logback.classic.net.SMTPAppender">
        <smtpHost>${smtpHost}</smtpHost>
        <smtpPort>${smtpPort}</smtpPort>
        <username>${username}</username>
        <password>${password}</password>
        <SSL>true</SSL>
        <asynchronousSending>true</asynchronousSending>
        <to>${mailTo}</to>
        <from>${mailFrom}</from>
        <subject>${appName}-[ERROR]</subject>
        <layout class="ch.qos.logback.classic.html.HTMLLayout"/>
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>ERROR</level>
        </filter>
    </appender>


    <logger name="com.eee" level="INFO" additivity="false">
        <appender-ref ref="transaction_file" />
        <appender-ref ref="STDOUT" />
        <appender-ref ref="MAIL"/>
    </logger>
    <logger name="com.eee" level="DEBUG" additivity="false">
        <appender-ref ref="logfile" />
        <appender-ref ref="transaction_file" />
        <appender-ref ref="STDOUT" />
        <appender-ref ref="MAIL"/>
    </logger>
    <root level="ERROR">
        <appender-ref ref="STDOUT" />
        <appender-ref ref="logfile" />
        <appender-ref ref="MAIL"/>
    </root>

    <logger name="org.mybatis" level="INFO" additivity="true">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="logfile"/>
        <appender-ref ref="MAIL"/>
    </logger>

</configuration>