IDEA 控制台打印Mybaits SQL语句

背景:

在实际开发过程中,一些简单的sql语句我们可以知道执行的具体sql是什么样子的,但是很多时候我们会用到mybatis的动态sql,虽然方便了我们开发,但是当动态拼接的sql块很多的时候,我们想要知道最终具体执行的完整的sql就比较难了,这个时候我们需要把完整的sql调试处理,这样方便我们排查问题.下面会简单介绍两种方法,希望对各位码友能有帮助.

Mybatis日志简介:

Mybatis 通过使用内置的日志工厂提供日志功能,内置日志工厂会把日志工作委托给下面几种框架之一来实现:

• SLF4J
• Apache Commons Logging
• Log4j2
• Log4j
• JDK logging

Mybatis 内置日志工厂会基于运行时检测的信息来选择使用哪个打印日志,会按上面罗列的顺序使用第一个查找到的实现,当没有找到这些实现时,就会禁用日志功能.

实现方案

方式一: xml

1. 在mybatis-config.xml里面添加一个setting配置,该配置用来指定打印日志的框架
   <settings> <!-- 打印查询语句 --> <setting name="logImpl" value="LOG4J"/> </settings>
   2.增加log4j.properties文件

log4j.rootLogger=INFO,console
log4j.logger.com.study.mybatis.dao =TRACE

log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d(%t)[%p-%1]%m%n

方式二:

1.依然需要log4j.properties配置文件
2.在config配置类中增加init方法初始化logFactory,可以选择的日志实现类有:

org.apache.ibatis.logging.LogFactory.useSlf4jLogging();
org.apache.ibatis.logging.LogFactory.useLog4JLogging();
org.apache.ibatis.logging.LogFactory.useJdkLogging();
org.apache.ibatis.logging.LogFactory.useCommonsLogging();
org.apache.ibatis.logging.LogFactory.useStdOutLogging();

根据项目中使用的日志框架选择相应的实现类

package com.study.mybatis.config;

import org.apache.ibatis.logging.log4j.Log4jImpl;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

@ComponentScan({"com.study.mybatis"})
@Configuration
@MapperScan("com.study.mybatis.dao")//3.配置扫描mpper
public class DBAutoConfig {

	//控制台输出sql语句
    @PostConstruct
    public void init(){
        org.apache.ibatis.logging.LogFactory.useLog4JLogging();
    }
    //1.配置datesource
    @Bean
    public DataSource dataSource(){
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setPassword("MySql!@#123");
        driverManagerDataSource.setUsername("root");
        driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false");
        driverManagerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return driverManagerDataSource;
    }
    //2.配置sqlSessionFactoryBean
    @Bean
     SqlSessionFactoryBean sqlSessionFactoryBean1(DataSource dataSource){
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        return sqlSessionFactoryBean;
    }


}

测试:

package com.study;

import com.alibaba.fastjson.JSON;
import com.study.mybatis.config.DBAutoConfig;
import com.study.mybatis.service.CityService;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import java.io.IOException;
import java.util.List;
import java.util.Map;

public class MybatisTest {
    public static void main(String[] args) throws IOException {

        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext();
        applicationContext.register(DBAutoConfig.class);
        applicationContext.refresh();

        CityService cityService = applicationContext.getBean(CityService.class);
        List<Map<String, Object>> list = cityService.list();
        System.out.println(JSON.toJSONString(list));

    }
}

结果:

方式三:

1.pom中添加依赖

<dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

2.在config中增加日志配置

@ComponentScan({"com.study.mybatis"})
@Configuration
@MapperScan("com.study.mybatis.dao")//3.配置扫描mpper
public class DBAutoConfig {

    //1.配置datesource
    @Bean
    public DataSource dataSource(){
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setPassword("MySqlxxxx");
        driverManagerDataSource.setUsername("root");
        driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false");
        driverManagerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return driverManagerDataSource;
    }
    //2.配置sqlSessionFactoryBean
    @Bean
     SqlSessionFactoryBean sqlSessionFactoryBean1(DataSource dataSource){
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        //增加日志配置,注意configuration是ibatis包下的
        org.apache.ibatis.session.Configuration configuration = new 		   org.apache.ibatis.session.Configuration();
        //注意这里使用的类和pom依赖中引入的依赖要保持一致
        configuration.setLogImpl(Log4jImpl.class);
        //configuration.setLogImpl(Slf4jImpl.class);
        sqlSessionFactoryBean.setConfiguration(configuration);

        sqlSessionFactoryBean.setDataSource(dataSource);
        return sqlSessionFactoryBean;
    }
}

3.mapper文件

public interface CityMapper {

    @Select("select * from city")
    public List<Map<String,Object>> list();

4.log4j.properties,同上面的一样
5.测试类

package com.study;

import com.alibaba.fastjson.JSON;
import com.study.mybatis.config.DBAutoConfig;
import com.study.mybatis.service.CityService;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import java.io.IOException;
import java.util.List;
import java.util.Map;

public class MybatisTest {
    public static void main(String[] args) throws IOException {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext();
        applicationContext.register(DBAutoConfig.class);
        applicationContext.refresh();

        CityService cityService = applicationContext.getBean(CityService.class);
        List<Map<String, Object>> list = cityService.listLimit1();
        System.out.println(JSON.toJSONString(list));

    }
}

测试结果