spring mvc + mybatis + LOG4J2 打印SQL语句

2022-07-09 09:09:33

项目框架：maven+spring mvc + mybatis + log4j2，想在框架中增加log4j自动输出sql语句功能。借鉴了http://blog.csdn.net/rangqiwei/article/details/50825090方法。

步骤

1、resource文件夹下增加mybatis-config.xml文件，内容如下：

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration  
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>  
        <setting name="logImpl" value="LOG4J2"/>  
    </settings>  
</configuration>

2、修改spring-mybatis.xml文件，在<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 节点下增加<property name="configLocation" value="classpath:mybatis-config.xml"></property>

<!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->  
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="dataSource" />  
        <!-- 自动扫描mapping.xml文件 -->  
        <property name="mapperLocations" value="classpath*:mybatis_mapping/Mapper_*.xml"></property>
        <property name="configLocation" value="classpath:mybatis-config.xml"></property><!-- 配置mybatis的日志记录 -->    
    </bean>

3、修改log4j2.xml文件，在Loggers节点中增加映射mybatis的DAO层接口包路径，修改后内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
 
     
<!-- Author:  Crunchify.com  -->
	<Appenders>
		<Console name="Console" target="SYSTEM_OUT">
			<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p %c{1}:%L - %msg%n" />
		</Console>
 		<!-- 注意%i和 %d{yyyyMMdd}，这样子才能将文件删除-->
		<RollingFile name="RollingFile" filename="/log/Convergence.log"
			filepattern="/log/%d{yyyyMMdd}-Convergence-%i.log">
			<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p %c{1}:%L - %msg%n" />
			<Policies>
				<SizeBasedTriggeringPolicy size="20 MB" />
			</Policies>
			<DefaultRolloverStrategy max="20" >
				<Delete basePath="/log/" maxDepth="1">  
                  <IfFileName glob="*-Convergence-*.log" />  
                  <IfLastModified age="30D" />  
                </Delete>  
			</DefaultRolloverStrategy>
		</RollingFile>
 
	</Appenders>
	<Loggers>
		<!-- name中的值为mybatis的DAO层接口包路径 -->
		<logger name="convergence.idao" level="DEBUG" additivity="false">  
            <appender-ref ref="Console"/>  
        </logger>  
		<Root level="DEBUG">
			<AppenderRef ref="Console" />
			<AppenderRef ref="RollingFile" />
		</Root>
	</Loggers>
</Configuration>

结果如下，控制台输出SQL语句，以及SQL语句的参数

2017-08-12 16:37:33 [http-bio-8080-exec-9] DEBUG SqlSessionUtils:104 - Creating a new SqlSession
2017-08-12 16:37:33 [http-bio-8080-exec-6] DEBUG SqlSessionUtils:104 - Creating a new SqlSession
2017-08-12 16:37:33 [http-bio-8080-exec-6] DEBUG SqlSessionUtils:140 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11699ce4] was not registered for synchronization because synchronization is not active
2017-08-12 16:37:33 [http-bio-8080-exec-9] DEBUG SqlSessionUtils:140 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@64d9841a] was not registered for synchronization because synchronization is not active
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG SpringManagedTransaction:86 - JDBC Connection [jdbc:oracle:thin:@//127.0.0.1:1521/orcl, UserName=CONVERGENCE, Oracle JDBC driver] will not be managed by Spring
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG getAllListByIDAndOrgtype:139 - ==>  Preparing: select * from ORGANIZATIONREGISTER WHERE PARENTID = ? AND ORGTYPE = ? order by QUENUM 
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG SpringManagedTransaction:86 - JDBC Connection [jdbc:oracle:thin:@//127.0.0.1:1521/orcl, UserName=CONVERGENCE, Oracle JDBC driver] will not be managed by Spring
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG newPageCountbydict:139 - ==>  Preparing: select count(*) from(SELECT c.ID CATALOGID,c.CATALOGNAME,o.ID ORGID,o.ORGNAME,c.CREATETIME from CATALOG c,organizationregister o WHERE c.ORGID=o.ID AND c.id in ( SELECT DISTINCT catalogid from ( SELECT b.catalogid,(select mattername　from pubmatterregister where id = b.pubmatterid) pubmattername,mattername from BUSINESSMATTER b ) where 1=1 union ( SELECT DISTINCT c.id from CATALOG c,organizationregister o WHERE c.ORGID=o.ID ) ) ORDER BY sortnum ) 
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG newPageCountbydict:139 - ==> Parameters: 
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG getAllListByIDAndOrgtype:139 - ==> Parameters: DGFF62D33C0000D6AE8B8C4315LOP097(String), 1(String)
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG newPageCountbydict:139 - <==      Total: 1
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG SqlSessionUtils:168 - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@64d9841a]
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG getAllListByIDAndOrgtype:139 - <==      Total: 1
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG SqlSessionUtils:168 - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11699ce4]
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG SqlSessionUtils:104 - Creating a new SqlSession
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG SqlSessionUtils:140 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2034bc6e] was not registered for synchronization because synchronization is not active
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG SpringManagedTransaction:86 - JDBC Connection [jdbc:oracle:thin:@//127.0.0.1:1521/orcl, UserName=CONVERGENCE, Oracle JDBC driver] will not be managed by Spring
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG getAllListByIDAndOrgtype:139 - ==>  Preparing: select * from ORGANIZATIONREGISTER WHERE PARENTID = ? AND ORGTYPE = ? order by QUENUM 
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG SqlSessionUtils:104 - Creating a new SqlSession
2017-08-12 16:37:35 [http-bio-8080-exec-6] DEBUG getAllListByIDAndOrgtype:139 - ==> Parameters: DGFF62D33C0000D6AE8B8C4315LOP097(String), 2(String)
2017-08-12 16:37:35 [http-bio-8080-exec-9] DEBUG SqlSessionUtils:140 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSe