使用Log4jdbc-log4j2监听MyBatis中运行的SQL和Connection

小灰灰 2022-08-27 08:00 3阅读 0赞

引言： 在项目中使用了MyBatis，一个比较苦恼的问题是无法看到执行的SQL的具体情况，所以，就找到了Log4jdbc-log4j2。这个是一个基于jdbc层面的监听工具，可以监听对于数据库的主要操作，从而完美的查看到其中执行的操作。

1. Log4jdbc-log4j2版本选择

*  [log4jdbc-log4j2-jdbc3.jar][] for JDBC 3 support in JDK 1.5
 *  [log4jdbc-log4j2-jdbc4.jar][] for JDBC 4 support in JDK 1.6
 *  [log4jdbc-log4j2-jdbc4.1.jar][] for JDBC 4.1 support in JDK 1.7

2.  如何引入到项目？

如果项目是基于Maven工具，则可以直接使用如下依赖，即可完美解决问题：

<dependency>
      <groupId>org.bgee.log4jdbc-log4j2</groupId>
      <artifactId>log4jdbc-log4j2-jdbcXX</artifactId>
      <version>1.16</version>
    </dependency>

3.  依赖包的安装

a.  如有使用log4j, 则需要log4j.-core.jar和log4j-api.jar

b.  如使用slf4j，则需要 slf4j-api.jar

4.  修改数据库连接中的jdbc.url和driver.Class

xxx.driverClass=<strong>net.sf.log4jdbc.sql.jdbcapi.DriverSpy</strong>   ------ 数据库驱动程序
    ............................
    xxxx.jdbcUrl = <strong>jdbc:log4</strong>\:oracle\:thin\:@192.168.1.20\:1521\:orcl    -------------- 数据库连接URL

说明：其中加粗的内容，表示其是被修改后的新内容。

原有的URL：

jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase

替换的URL：

jdbc:log4jdbc:derby://localhost:1527//db-derby-10.2.2.0-bin/databases/MyDatabase

5.  配置好log4j和log4j.properties

6.  新增log4jdbc.log4j2.properties

这里使用Slf4j API， 声明如下：

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

7.  可用的Logger

<table style=""> 
 <tbody> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px"><strong>logger</strong></td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px"><strong>description</strong></td> 
  </tr> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">jdbc.sqlonly</td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">Logs only SQL. SQL executed within a prepared statement is automatically shown with it's bind arguments replaced with the data bound at that position, for greatly increased readability.</td> 
  </tr> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">jdbc.sqltiming</td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">Logs the SQL, post-execution, including timing statistics on how long the SQL took to execute.</td> 
  </tr> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">jdbc.audit</td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">Logs ALL JDBC calls except for ResultSets. This is a very voluminous output, and is not normally needed unless tracking down a specific JDBC problem.</td> 
  </tr> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">jdbc.resultset</td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">Even more voluminous, because all calls to ResultSet objects are logged.</td> 
  </tr> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">jdbc.resultsettable</td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">Log the jdbc results as a table. Level debug will fill in unread values in the result set.</td> 
  </tr> 
  <tr> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">jdbc.connection</td> 
   <td style="font-size:13px; border:1px solid rgb(204,204,204); padding:5px">Logs connection open and close events as well as dumping all open connection numbers. This is very useful for hunting down connection leak problems.</td> 
  </tr> 
 </tbody> 
</table>

这些都是需要配置到log4j.properties或者log4j.xml中去的。

示例如下：

! Log all JDBC calls except for ResultSet calls
    log4j.logger.jdbc.audit=DEBUG,jdbc
    log4j.additivity.jdbc.audit=false
    
    ! Log only JDBC calls to ResultSet objects
    log4j.logger.jdbc.resultset=DEBUG,jdbc
    log4j.additivity.jdbc.resultset=false
    
    ! Log only the SQL that is executed.
    log4j.logger.jdbc.sqlonly=DEBUG,sql
    log4j.additivity.jdbc.sqlonly=false
    
    ! Log timing information about the SQL that is executed.
    log4j.logger.jdbc.sqltiming=DEBUG,sqltiming
    log4j.additivity.jdbc.sqltiming=false
    
    ! Log connection open/close events and connection number dump
    log4j.logger.jdbc.connection=FATAL,connection
    log4j.additivity.jdbc.connection=false
    
    
    ! the appender used for the JDBC API layer call logging above, sql only
    log4j.appender.sql=org.apache.log4j.FileAppender
    log4j.appender.sql.File=./logs/sql.log
    log4j.appender.sql.Append=false
    log4j.appender.sql.layout=org.apache.log4j.PatternLayout
    log4j.appender.sql.layout.ConversionPattern=-----> %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n
    
    ! the appender used for the JDBC API layer call logging above, sql timing
    log4j.appender.sqltiming=org.apache.log4j.FileAppender
    log4j.appender.sqltiming.File=./logs/sqltiming.log
    log4j.appender.sqltiming.Append=false
    log4j.appender.sqltiming.layout=org.apache.log4j.PatternLayout
    log4j.appender.sqltiming.layout.ConversionPattern=-----> %d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n
    
    ! the appender used for the JDBC API layer call logging above
    log4j.appender.jdbc=org.apache.log4j.FileAppender
    log4j.appender.jdbc.File=./logs/jdbc.log
    log4j.appender.jdbc.Append=false
    log4j.appender.jdbc.layout=org.apache.log4j.PatternLayout
    log4j.appender.jdbc.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n
    
    ! the appender used for the JDBC Connection open and close events
    log4j.appender.connection=org.apache.log4j.FileAppender
    log4j.appender.connection.File=./logs/connection.log
    log4j.appender.connection.Append=false
    log4j.appender.connection.layout=org.apache.log4j.PatternLayout
    log4j.appender.connection.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n

总结：

经过一番折腾之后呢，终于可以在系统看到数据库中各类的操作了，数据库的各种行为尽在眼底........

[log4jdbc-log4j2-jdbc3.jar]: http://log4jdbc-log4j2.googlecode.com/files/log4jdbc-log4j2-jdbc3-1.16.jar
[log4jdbc-log4j2-jdbc4.jar]: http://log4jdbc-log4j2.googlecode.com/files/log4jdbc-log4j2-jdbc4-1.16.jar
[log4jdbc-log4j2-jdbc4.1.jar]: http://log4jdbc-log4j2.googlecode.com/files/log4jdbc-log4j2-jdbc4.1-1.16.jar