Logging SQL Queries to the Console in Mybatis

1. Overview

MyBatis is a popular Java-based persistence framework that simplifies database operations by mapping SQL queries to Java methods.

When developing applications using MyBatis, it’s often useful for debugging to see which SQL queries are being used.

In this tutorial, we’ll explore how to log SQL queries to the console in MyBatis.

2. Supported Logging Implementations

Before delving into SQL logging in MyBatis, it’s important to understand the supported logging implementations.

MyBatis is a flexible framework that can integrate with various logging frameworks, including SLF4J, Apache Commons Logging, Log4j 2, and JDK Logging. This article will explore two different logging options: stdout logging and SLF4J.

Stdout logging is beneficial during local feature development as it provides a simple approach for debugging. On the other hand, SLF4J is better suited for production applications, offering versatile abstractions that seamlessly integrate with users’ preferred logging frameworks during deployment.

3. Configuring Stdout Logging in MyBatis

Logging MyBatis SQL using stdout allows us to view the executed SQL statements directly on the console. This method is handy during development and debugging.

To enable stdout logging for MyBatis SQL, we need to add a logging setting in the mybatis-config file of our application:

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

After configuring the logImpl property to STDOUT_LOGGING, MyBatis will output the raw SQL statements, query parameters, and query results when executing SQL queries. The output typically includes detailed information such as the executed SQL, bound parameters, and the returned result set:

==>  Preparing: SELECT addressId, streetAddress FROM Address WHERE addressId = ? 
==> Parameters: 1(Integer)
<==    Columns: ADDRESSID, STREETADDRESS
<==        Row: 1, 123 Main Street

The output indicates preparing a SQL query to fetch data from the Address table using a specific ID. It shows the parameters, the result set columns (ADDRESSID and STREETADDRESS), and an illustrative row of data (ADDRESSID: 1, STREETADDRESS: 123 Main Street). Additionally, it tells us that the total count of returned rows was 1.

Apart from configuring the logImpl property in mybatis-config, we also have the option to set the log implementation programmatically. We can achieve this by calling the static method LogFactory.useStdOutLogging() before calling any other MyBatis method.

Using stdout logging has a downside in that it lacks fine-grained control over the logs. With stdout logging, MyBatis logs all executed SQL queries in detail, which can be overwhelming and make it difficult to focus on the essential information.

To achieve more precise control over logging, such as determining which part or mapper prints the logs, it’s recommended to use a logging framework.

4. Configuring SLF4J and Logback Logging in MyBatis

4.1. Setting Up SLF4J and Logback Logging

First, we need to add the SLF4J and Logback dependencies to our project’s build file. Since Logback automatically includes SLF4J as a transitive dependency, for Maven projects, we only need to specify the Logback dependency in the pom.xml file:

<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.4.14</version>
</dependency>

Next, we need to create a Logback configuration file, typically named logback.xml, to define the logging behavior:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration>
<configuration>
    <appender name="stdout" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%5level [%thread] - %msg%n</pattern>
        </encoder>
    </appender>
    <root level="INFO">
        <appender-ref ref="stdout"/>
    </root>
</configuration>

This configuration creates a root logger to log messages with a log level of INFO or higher and directs them to the stdout appender for output to the console.

Following that, similarly to the stdout logging configuration, we need to set the logImpl property to SLF4J in the mybatis-config file:

<configuration>
    <settings>
        <setting name="logImpl" value="SLF4J" />
    </settings>
</configuration>

4.2. Logging Mapper

By configuring the logging as mentioned above, logging the mapper becomes straightforward. We can set the logger name to the fully-qualified name of the mapper interface, or the namespace if an XML mapper file is used:

<logger name="com.baeldung.mybatis.mapper.AddressMapper" level="TRACE"/>

This allows easy logging control by associating the logger with the desired mapper. Only queries related to this mapper will have trace-level logging applied.

4.3. Logging a Specific Mapper Method

To selectively log the execution of a specific method, such as getFruitById in the FruitMapper, we can configure the logger accordingly:

<logger name="com.baeldung.mybatis.mapper.AddressMapper.getAddresses" level="TRACE"/>

With this configuration, the logger will only print the log to the console when executing the getFruitById method, allowing for more focused and granular logging control.

4.4. Logging Mappers in a Package

We can easily enable logging for all mappers under a specific package by setting the logger name to the package name:

<logger name="com.baeldung.mybatis.mapper" level="TRACE"/>

This approach allows for comprehensive logging across all mappers within the designated package.

4.5. Logging SQL Statements Only

In scenarios where queries can produce large result sets, we may prefer to view the SQL statements without logging the actual results. MyBatis is designed to log SQL statements at the DEBUG level, while it logs results at the TRACE level. If we wish to see the statement without the result, we need to set the logging level to DEBUG:

<logger name="com.baeldung.mybatis.mapper.AddressMapper" level="DEBUG"/>

5. Configuring SQL Logging in MyBatis With Spring Boot

Spring is a widely adopted framework, and in many cases, MyBatis is configured in conjunction with Spring instead of for standalone usage. When working with Spring Boot, there’s little to do to configure MyBatis SQL logging. Spring Boot utilizes logback as its default logging implementation, and MyBatis’ logging mechanism prioritizes SLF4J.

Therefore, to enable MyBatis SQL logging for a specific mapper, we add properties to our Spring Boot application.properties file:

logging.level.com.baeldung.mybatis.spring.ArticleMapper=DEBUG

By configuring the log level to DEBUG for the designated mapper, we’ll have detailed SQL logging for that particular mapper.

6. Conclusion

In this article, we looked at the configuration of SQL logging in MyBatis, including stdout logging, SLF4J with Logback, logging specific mappers/methods/packages, and integration with Spring Boot.