Technology Sharing

MyBatis (27) How to configure MyBatis to print executable SQL statements

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

During the development process, printing executable SQL statements is very helpful for debugging and performance optimization. MyBatis provides several ways to print SQL statements.

1. Use a logging framework

MyBatis can print SQL statements by configuring the logging framework used internally (such as Log4j, Logback, etc.). This is the most commonly used method.

Logback Configuration Example

If you are using Logback, you canlogback.xmlAdd the following configuration to the file:

<configuration>
    <!-- 其他配置 -->

    <!-- 配置MyBatis日志级别 -->
    <logger name="org.apache.ibatis" level="DEBUG"/>
    
    <!-- 如果你想要更详细的输出,包括SQL语句、参数等 -->
    <logger name="java.sql.PreparedStatement" level="TRACE"/>
</configuration>
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

After this configuration, the SQL statements executed by MyBatis and their parameters will be printed to the log.

Log4j Configuration Example

If you are using Log4j, you canlog4j.propertiesAdd the following configuration to the file:

# 配置MyBatis日志级别
log4j.logger.org.apache.ibatis=DEBUG

# 如果你想要更详细的输出,包括SQL语句、参数等
log4j.logger.java.sql.PreparedStatement=TRACE
  • 1
  • 2
  • 3
  • 4
  • 5

2. Use the log implementation provided by MyBatis

MyBatis itself also provides a simple log implementation, which can be set in the MyBatis configuration filemybatis-config.xmlTo enable it, set:

<configuration>
    <settings>
        <!-- 启用日志 -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
</configuration>
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

This will log to standard output.logImplThe value of can beSTDOUT_LOGGINGLOG4JLOG4J2SLF4JEtc., choose according to the logging framework used in your project.

3. Use P6Spy

P6Spy is a database query analysis tool that can proxy the JDBC driver to intercept and record SQL statements. With P6Spy, you can record all SQL statements executed through JDBC without modifying any code.

To use P6Spy, you will need:

  1. Add the P6Spy dependency to your project.
  2. Configurationspy.propertiesFile, specify the actual JDBC driver and log file path, etc.
  3. Modify the database connection configuration and use the P6Spy proxy driver.
Add P6Spy dependency

Take Maven as an example:

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>最新版本</version>
</dependency>
  • 1
  • 2
  • 3
  • 4
  • 5
Configurationspy.properties

existsrc/main/resourcesCreate a directoryspy.propertiesFile and configure it as follows:

driverlist=真实的数据库驱动类名
logfile=日志文件路径
  • 1
  • 2
Modify database connection configuration

Change the database connection driver class tocom.p6spy.engine.spy.P6SpyDriver, the URL prefix is ​​changed tojdbc:p6spy:

Summarize

The above are several ways to implement MyBatis to print executable SQL statements. In actual development, you can choose the appropriate method according to the specific needs of the project and the technology stack used. Usually, configuring the log framework is the simplest and most commonly used method.