mybatis输出SQL格式化方式

mybatis输出sql格式化

通过第三方日志工具可以控制日志级别的输出，但是我们发现mybatis输出的sql不是那么的完整，我们sql里的参数值并没有打印出来，下面我就来讲讲怎么样对mybatis的输出sql格式化。

首先我这个案例是基于spring boot 来开发的，所以配置和传统的xml配置有所区别，spring boot大大简化了一些配置，它把配置放到java代码，我们只需要使用注解就可替代一些以前xml的配置。

自定义拦截器

import java.io.printstream;
import java.text.dateformat;
import java.util.date;
import java.util.list;
import java.util.locale;
import java.util.properties;
import java.util.regex.matcher;
import org.apache.commons.collections.collectionutils;
import org.apache.ibatis.mapping.boundsql;
import org.apache.ibatis.mapping.mappedstatement;
import org.apache.ibatis.mapping.parametermapping;
import org.apache.ibatis.plugin.interceptor;
import org.apache.ibatis.plugin.intercepts;
import org.apache.ibatis.plugin.invocation;
import org.apache.ibatis.plugin.plugin;
import org.apache.ibatis.reflection.metaobject;
import org.apache.ibatis.session.configuration;
import org.apache.ibatis.type.typehandlerregistry;
import org.springframework.stereotype.component;
/**
 * 自定义mybatis拦截器,格式化sql输出,
 *
 * @author zengsong
 * @version 1.0
 * @description 只对查询和更新语句做了格式化，其它语句需要手动添加
 * @date 2019/5/30 10:17
 **/
@intercepts({@org.apache.ibatis.plugin.signature(type=org.apache.ibatis.executor.executor.class, method="update", args={mappedstatement.class, object.class}), @org.apache.ibatis.plugin.signature(type=org.apache.ibatis.executor.executor.class, method="query", args={mappedstatement.class, object.class, org.apache.ibatis.session.rowbounds.class, org.apache.ibatis.session.resulthandler.class})})
@component
public class mybatisresultinterceptor
        implements interceptor
{
    public object intercept(invocation invocation)
            throws throwable
    {
        try
        {
            mappedstatement mappedstatement = (mappedstatement)invocation.getargs()[0];
            object parameter = null;
            if (invocation.getargs().length > 1) {
                parameter = invocation.getargs()[1];
            }
            string sqlid = mappedstatement.getid();
            boundsql boundsql = mappedstatement.getboundsql(parameter);
            configuration configuration = mappedstatement.getconfiguration();
            string sql = getsql(configuration, boundsql, sqlid);
            system.out.println(sql);
        }
        catch (exception localexception) {}
        return invocation.proceed();
    }
    public static string getsql(configuration configuration, boundsql boundsql, string sqlid)
    {
        string sql = showsql(configuration, boundsql);
        stringbuilder str = new stringbuilder(100);
        str.append(sqlid);
        str.append(":");
        str.append(sql);
        return str.tostring();
    }
    private static string getparametervalue(object obj)
    {
        string value = null;
        if ((obj instanceof string))
        {
            value = "'" + obj.tostring() + "'";
        }
        else if ((obj instanceof date))
        {
            dateformat formatter = dateformat.getdatetimeinstance(2, 2, locale.china);
            value = "'" + formatter.format(new date()) + "'";
        }
        else if (obj != null)
        {
            value = obj.tostring();
        }
        else
        {
            value = "";
        }
        return value;
    }
    public static string showsql(configuration configuration, boundsql boundsql)
    {
        object parameterobject = boundsql.getparameterobject();
        list<parametermapping> parametermappings = boundsql.getparametermappings();
        string sql = boundsql.getsql().replaceall("[\\s]+", " ");
        metaobject metaobject;
        if ((collectionutils.isnotempty(parametermappings)) && (parameterobject != null))
        {
            typehandlerregistry typehandlerregistry = configuration.gettypehandlerregistry();
            if (typehandlerregistry.hastypehandler(parameterobject.getclass()))
            {
                sql = sql.replacefirst("\\?", matcher.quotereplacement(getparametervalue(parameterobject)));
            }
            else
            {
                metaobject = configuration.newmetaobject(parameterobject);
                for (parametermapping parametermapping : parametermappings)
                {
                    string propertyname = parametermapping.getproperty();
                    if (metaobject.hasgetter(propertyname))
                    {
                        object obj = metaobject.getvalue(propertyname);
                        sql = sql.replacefirst("\\?", matcher.quotereplacement(getparametervalue(obj)));
                    }
                    else if (boundsql.hasadditionalparameter(propertyname))
                    {
                        object obj = boundsql.getadditionalparameter(propertyname);
                        sql = sql.replacefirst("\\?", matcher.quotereplacement(getparametervalue(obj)));
                    }
                    else
                    {
                        sql = sql.replacefirst("\\?", "缺失");
                    }
                }
            }
        }
        return sql;
    }
    public object plugin(object target)
    {
        return plugin.wrap(target, this);
    }
    public void setproperties(properties properties) {}
}

配置拦截器

import com.sengled.cloud.data.platform.dao.mybatis.mybatisresultinterceptor;
import java.util.properties;
import javax.annotation.resource;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
/**
 * 自定义mybatis拦截器
 *
 * @author zengsong
 * @version 1.0
 * @description
 * @date 2019/5/30 10:17
 **/
@configuration
public class mybatisinterceptorconfig
{
    @resource
    private mybatisresultinterceptor mybatisresultinterceptor;
    @bean
    public string myinterceptor()
    {
        properties properties = new properties();
        this.mybatisresultinterceptor.setproperties(properties);
        return "interceptor";
    }
}

配置日志级别

 <logger name="com.apache.ibatis" level="trace"/>
 <logger name="java.sql.connection" level="debug"/>
 <logger name="java.sql.statement" level="debug"/>
 <logger name="java.sql.preparedstatement" level="debug"/>
 <root level="info">
        <appender-ref ref="stdout" />
        <appender-ref ref="file" />
 </root>

mybatis sql语句格式化 trim prefix suffix

标题sql语句格式化

• trim标记：是格式化sql的标记
• prefix：前缀
• suffix：后缀
• prefixoverrides：指定去除多余的前缀内容
• suffixoverrides：指定去除多余的后缀内容

1. select语句

<select id="" parametertype="" resulttype="">
 select * from tb_user
 <trim perfis="where" prefixoverrides = "and | or">
  <if test="name != null"> and name = #{name}</if>
  <if test="gender != null"> and gender= #{gender}</if>
 </trim>
</select>

执行结果为：

select * from tb_user where and name = #{name} andgender = #{gender}

2. insert语句

<insert id="" parametertype="">
    insert into tb_user
    <trim prefix="(" suffix=")" suffixoverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="name!= null">
        name,
      </if>
      <if test="gender!= null">
        gender,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixoverrides=",">
      <if test="id != null">
        #{id,jdbctype=bigint},
      </if>
      <if test="name!= null">
        #{name,jdbctype=varchar},
      </if>
     <if test="gender!= null">
        #{gender,jdbctype=bigint},
      </if>
    </trim>
  </insert>

执行结果为：

insert into tb_user (id,name,gender) values(1,“张三”,20)

3.update语句

  <update id="">
    update tb_user
    　<trim prefix="set" suffix=" where id = #{id}" suffixoverrides="," >
 　　　　<if test="name != null"> name = #{name} , </if>
 　　　　<if test="gender != null"> gender = #{gender} ,</if>
　　　　</trim>
  </update>

执行结果为：

update tb_user set name = “张三”，gender = 30 where id = 1

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。