SpringBoot+mybatis+druid+log42实现打印sql日志

淡淡的烟草味﹌ 2023-07-08 06:59 0阅读 0赞

我的SpringBoot版本为:   2.1.10.RELEASE

首先在pom文件里面添加jar包，添加了log42，druid，mysql的jar包

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-log4j2</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.10</version>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>

然后是yml文件配置（仅剩余myabtis，datasource配置）重点是那个filter：

spring:
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource   #配置当前要使用的数据源的操作类型那个
        url: jdbc:mysql://127.0.0.1:3306/test
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: 111
        password: 111
        druid:
          initial-size: 5 # 初始化连接数
          min-idle: 5 # 最小连接数
          max-active: 20 # 最大连接数
          max-wait: 60000 # #连接超时超时配置 1000*60 1min
          time-between-eviction-runs-millis: 60000  # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          min-evictable-idle-time-millis: 600000 # 连接保持空闲而不被驱逐的最长时间。10min（10*60*1000）
          validation-query: SELECT 1 #对于数据库连接的检测
          test-while-idle: true #如果空闲时间大于time-between-eviction-runs-millis 使用validation-query检测连接是否有效，无效则淘汰
          test-on-borrow: false #申请连接时检测连接是否有效 影响性能关闭
          filters: stat,wall,log4j2     #druid监控配置
          filter:
            commons-log:
              connection-log-enabled: false
              statement-log-enabled: false
              result-set-log-enabled: true # 表示是否显示结果集。
              statement-executable-sql-log-enable: true # 表示是否显示SQL语句。
    
    mybatis:
      mapper-locations: classpath:mybatis/*/*.xml #指定xml位置
      configLocation: classpath:mybatis/mybatis-config.xml # 配置信息

然后是  mybatis-config.xml  重点是最后一行setting

<?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="cacheEnabled" value="true"/>
            
            <setting name="lazyLoadingEnabled" value="true"/>
            
            <setting name="aggressiveLazyLoading" value="true"/>
            
            <setting name="multipleResultSetsEnabled" value="true"/>
            
            <setting name="useColumnLabel" value="true"/>
            
            <setting name="useGeneratedKeys" value="true"/>
            
            <setting name="autoMappingBehavior" value="PARTIAL"/>
            
            <setting name="defaultExecutorType" value="SIMPLE"/>
            
            <setting name="mapUnderscoreToCamelCase" value="true"/>
            
            <setting name="localCacheScope" value="SESSION"/>
            
            <setting name="jdbcTypeForNull" value="NULL"/>
            
            <setting name="logImpl" value="LOG4J2" />
        </settings>
    </configuration>

最后的重点，log4j2.xml配置 重点是 sqlRollingLog 这个appender

<?xml version="1.0" encoding="UTF-8"?>
    
    
    
    
    <Configuration status="off">
    	<Appenders>
    		
    		<Console name="Console" target="SYSTEM_OUT">
    			<PatternLayout pattern="[%d{yyyy-MM-dd HH:mm:ss.SSS}]%p[%t]%c{1}|%m%n" />
    		</Console>
    
    		
    		
    		<RollingRandomAccessFile name="DetailRollingFile" fileName="log/test/test_detail.log" filePattern="log/test/test_detail.log.%d{yyyyMMddHH}" immediateFlush="true">
    			
    			<ThresholdFilter level="INFO" onMatch="ACCEPT" onMismatch="DENY"/>
    			
    			<PatternLayout pattern="[%d{yyyy-MM-dd HH:mm:ss.SSS}]%p[%t]%c{1}|%m%n" charset="UTF-8"/>
    			
    			<Policies>
    				
    				<TimeBasedTriggeringPolicy interval="1"/>
    			</Policies>
    		</RollingRandomAccessFile>
    		
    		<RollingRandomAccessFile name="MpspRollingFile" fileName="log/test/mpsp.log" filePattern="log/test/mpsp.log.%d{yyyyMMddHH}">
    			<ThresholdFilter level="INFO" onMatch="ACCEPT" onMismatch="DENY"/>
    			<PatternLayout pattern="[%d{yyyy-MM-dd HH:mm:ss.SSS}]%m%n"/>
    			<Policies>
    				<TimeBasedTriggeringPolicy interval="1"/>
    			</Policies>
    			
    			
    		</RollingRandomAccessFile>
    		
    		<RollingRandomAccessFile name="ssRollingLog" fileName="log/test/ss.log" filePattern="log/test/ss.log.%d{yyyyMMddHH}">
    			<ThresholdFilter level="INFO" onMatch="ACCEPT" onMismatch="DENY"/>
    			<PatternLayout pattern="[%d{yyyy-MM-dd HH:mm:ss.SSS}]%m%n"/>
    			<Policies>
    				<TimeBasedTriggeringPolicy interval="1"/>
    			</Policies>
    		</RollingRandomAccessFile>
    		
    		<RollingRandomAccessFile name="sqlRollingLog" fileName="log/test/sql.log" filePattern="log/test/sql.log.%d{yyyyMMddHH}">
    			<ThresholdFilter level="debug" onMatch="ACCEPT" onMismatch="DENY"/>
    			<PatternLayout pattern="[%d{yyyy-MM-dd HH:mm:ss.SSS}]%p[%t]%c{1}|%m%n" charset="UTF-8"/>
    			<Policies>
    				<TimeBasedTriggeringPolicy interval="1"/>
    			</Policies>
    		</RollingRandomAccessFile>
    	</Appenders>
    
    	
    	<Loggers>
    		
    		<Logger name="mpspLog" level="INFO" additivity="false">
    			<AppenderRef ref="MpspRollingFile" />
    		</Logger>
    		<Logger name="ssLog" level="INFO" additivity="false">
    			<AppenderRef ref="ssRollingLog" />
    		</Logger>
    		<Logger name="detailLog" level="INFO" additivity="false">
    			<AppenderRef ref="DetailRollingFile" />
    		</Logger>
    		<Logger name="com.umpay" level="INFO" additivity="false">
    			<AppenderRef ref="DetailRollingFile" />
    		</Logger>
    
    		<logger name="druid.sql.Statement" level="debug" additivity="false">
    			<appender-ref ref="sqlRollingLog"/>
    		</logger>
    		<logger name="druid.sql.ResultSet" level="debug" additivity="false">
    			<appender-ref ref="sqlRollingLog"/>
    		</logger>
    
    		<Root level="info">
    			<AppenderRef ref="Console" />
    		</Root>
    	</Loggers>
    </Configuration>

配置好了你就可以看到打印在文件里面的sql执行语句和结果集了