投稿
  1. 极客之音首页
  2. 后端开发
  3. Java

spring mybatis设置SQL语句打印

小半 • • Java • 阅读 264

导读：本篇文章讲解 spring mybatis设置SQL语句打印，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

在使用mybatis进行开发的时候，由于可以动态拼接sql，这样大大方便了我们。但是也有一定的问题，当我们动态sql拼接的块很多的时候，我们要想从*mapper.xml中直接找出完整的sql就会非常的难，这个时候经常会需要把组合之后的完整sql调试出来比较好。下面来看两种调试出sql的两种方式

解决方案
方案1： 
网上说的比较多的，之前也是这么用的一种方式 
1:首先将ibatis log4j运行级别调到DEBUG可以在控制台打印出ibatis运行的sql语句 
2:添加如下语句

###显示SQL语句部分
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.Java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

方案2： 

如果是application.properties

logging.level.com.neo.mapper=debug

若果是application.yml

logging:
  level:
    com.test.mapper: DEBUG

com.test.mapper是 mybatis接口及映射文件包。

application.properties 改成 application.yml

yml文件的好处，天然的树状结构，一目了然

方案3： 

application.properties

server.port=8090

server.session-timeout=30

server.context-path=

server.tomcat.max-threads=0

server.tomcat.uri-encoding=UTF-8


spring.datasource.url = jdbc:mysql://localhost:3306/newbirds

spring.datasource.username = root

spring.datasource.password = mymysql

spring.datasource.driverClassName = com.mysql.jdbc.Driver

# Specify the DBMS

spring.jpa.database = MYSQL

# Show or not log for each sql query

spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update)

spring.jpa.hibernate.ddl-auto = update

# Naming strategy

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy


# stripped before adding them to the entity manager)

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

application.yml


server:

port: 8090

session-timeout: 30

tomcat.max-threads: 0

tomcat.uri-encoding: UTF-8


spring:

datasource:

url : jdbc:mysql://localhost:3306/newbirds

username : root

password : mymysql

driverClassName : com.mysql.jdbc.Driver

jpa:

database : MYSQL

show-sql : true

hibernate:

ddl-auto : update

naming-strategy : org.hibernate.cfg.ImprovedNamingStrategy

properties:

hibernate:

dialect : org.hibernate.dialect.MySQL5Dialect

注意点：

1，原有的key，例如spring.jpa.properties.hibernate.dialect，按“.”分割，都变成树状的配置

2，key后面的冒号，后面一定要跟一个空格

3，把原有的application.properties删掉。然后一定要执行一下  maven -X clean install

方案4： 

springboot对logback的支持是非常好的，不需要任何配置，只需要在resource下加logback.xml就可以实现功能
直接贴代码：

<?xml version="1.0"?>

<configuration>

    <property name="logPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS}  %-5level [%thread][%c.%M:%L]- %msg%n"/>
    <property name="logHome" value="${log_path}/log"/>

    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/file.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/file.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>15 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>


    <appender name="baselog" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/base.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/base.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>15 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="springlog" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/spring.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/spring.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>20 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="sqllog" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/sqllog.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/sql.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>20 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="convertErrorlog" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/convertErrorlog.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/convertErrorlog.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>20 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>
    <appender name="cachelog" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/cachelog.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/cachelog.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>20 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="businessLogger" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/business.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/business.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>20 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>5</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="dataLogger" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${logHome}/data.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${logHome}/data.log.%d.%i.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>20 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>5</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${logPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <root level="WARN">
        <appender-ref ref="FILE"/>
    </root>
    <logger name="com.test" level="WARN" additivity="false">
        <appender-ref ref="baselog"/>
    </logger>
    <logger name="com.smile" level="DEBUG" additivity="false">
        <appender-ref ref="sqllog"/>
    </logger>
    <logger name="java.sql" level="WARN" additivity="false">
        <appender-ref ref="sqllog"/>
    </logger>
    <logger name="org.springframework" level="WARN" additivity="false">
        <appender-ref ref="springlog"/>
    </logger>
    <logger name="o.springframework" level="WARN" additivity="false">
        <appender-ref ref="springlog"/>
    </logger>

    <logger name="ERROR_LOGGER" level="WARN" additivity="false">
        <appender-ref ref="convertErrorlog"/>
    </logger>

    <logger name="CACHE_LOGGER" level="WARN" additivity="false">
        <appender-ref ref="cachelog"/>
    </logger>

    <logger name="business_logger" level="WARN" additivity="false">
        <appender-ref ref="businessLogger"/>
    </logger>

    <logger name="data_logger" level="WARN" additivity="false">
        <appender-ref ref="dataLogger"/>
    </logger>

</configuration>

		
<!-- name包必须能够扫描到所以类，包括启动类 -->
    <logger name="com.smile" level="debug" additivity="false">
        <appender-ref ref="smile" />
    </logger>

spring mybatis设置SQL语句打印
其中值得注意的是：你配置文件中的包名要能够扫描到你的所有类，包括启动类。
否则，你的日志文件是生成了，但是不会向里面写入内容
启动程序打印日志，日志成功生成：

spring mybatis设置SQL语句打印

方案5： 

spring mvc mybatis配置文件：

<?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="false" />
        <!-- 设置关联对象加载的形态,此处为按需加载字段(加载字段由SQL指定),不会加载关联表的所有字段,以提高性能 -->
        <setting name="aggressiveLazyLoading" value="false" />
        <!-- 对于位置的SQL查询,允许返回不同的结果集以达到通用的效果 -->
        <setting name="multipleResultSetsEnabled" value="true" />
        <!-- 允许使用列标签代替列明 -->
        <setting name="useColumnLabel" value="true" />
        <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值), 数据表的pk生成策略将被覆盖 -->
        <setting name="useGeneratedKeys" value="true" />
        <!-- 给予被嵌套的resultMap以字段-属性的映射支持 -->
        <setting name="autoMappingBehavior" value="PARTIAL" />
        <!-- 对于批量更新操作缓存SQL以提高性能 -->
        <setting name="defaultExecutorType" value="REUSE" />
        <!-- 数据库超过25000秒仍未响应则超时 -->
        <setting name="defaultStatementTimeout" value="25000" />
        <!-- 打印查询语句 -->
        <!-- <setting name="logImpl" value="STDOUT_LOGGING" /> -->
    </settings>
</configuration>

 

<setting name="logImpl" value="STDOUT_LOGGING" />

如果将它注释，你的sql就可以打印进日志

如果将它放开，你的sql就打印到了控制台
 

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/14155.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！