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

在项目中使用了MyBatis，一个比较苦恼的问题是无法看到执行的SQL的具体情况，所以，就找到了Log4jdbc-log4j2。这个是一个基于jdb

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

MyBatis插入时候获取自增主键方法

MyBatis快速入门教程

1. Log4jdbc-log4j2版本选择

2. 如何引入到项目？

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

org.bgee.log4jdbc-log4j2
log4jdbc-log4j2-jdbcXX
1.16

3. 依赖包的安装

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

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

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

xxx.driverClass=net.sf.log4jdbc.sql.jdbcapi.DriverSpy ------ 数据库驱动程序
............................
xxxx.jdbcUrl = jdbc:log4\: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

logger description

jdbc.sqlonly 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.

jdbc.sqltiming Logs the SQL, post-execution, including timing statistics on how long the SQL took to execute.

jdbc.audit 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.

jdbc.resultset Even more voluminous, because all calls to ResultSet objects are logged.

jdbc.resultsettable Log the jdbc results as a table. Level debug will fill in unread values in the result set.

jdbc.connection Logs connection open and close events as well as dumping all open connection numbers. This is very useful for hunting down connection leak problems.

这些都是需要配置到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

总结：

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