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;
@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;
@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
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
