这篇文章主要讲解了“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>标题SQL语句格式化
trim标记：是格式化sql的标记
prefix：前缀
suffix：后缀
prefixOverrides：指定去除多余的前缀内容
suffixOverrides：指定去除多余的后缀内容
<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}
<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)
<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
感谢各位的阅读，以上就是“mybatis输出SQL格式化的方法是什么”的内容了，经过本文的学习后，相信大家对mybatis输出SQL格式化的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
