Shulou(Shulou.com)06/02 Report--

This article mainly explains the "mybatis output SQL format method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "mybatis output SQL formatting method is what" it!

Mybatis output SQL formatting

The log level output can be controlled by the third-party logging tool, but we find that the SQL output of mybatis is not so complete, and the parameter values in our SQL are not printed, so I will talk about how to format the output sql of mybatis.

First of all, my case is based on Spring boot, so the configuration is different from the traditional xml configuration. Spring boot greatly simplifies some configurations. It puts the configuration into java code, and we only need to use annotations to replace some previous xml configurations.

Custom interceptor 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 / * Custom mybatis interceptor to format SQL output. * * @ author zengsong * @ version 1.0 * @ description only formats queries and update statements Other statements need to be manually added * @ date 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})}) @ Componentpublic 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 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 ("\\?", "missing");} return sql;} public Object plugin (Object target) {return Plugin.wrap (target, this) } public void setProperties (Properties properties) {}} configure interceptor 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 / * * Custom mybatis interceptor * * @ author zengsong * @ version 1.0 * @ description * @ date 10:17 on 2019-5-30 * * / @ Configurationpublic class MybatisInterceptorConfig {@ Resource private MybatisResultInterceptor mybatisResultInterceptor; @ Bean public String myInterceptor () {Properties properties = new Properties (); this.mybatisResultInterceptor.setProperties (properties); return "interceptor" }} configure log-level mybatis sql statements to format trim prefix suffix

Title SQL statement format

Trim tag: is the tag that formats the sql

Prefix: prefix

Suffix: suffix

PrefixOverrides: specifies to remove redundant prefix content

SuffixOverrides: specifies that redundant suffixes are removed

1. Select statement select * from tb_user AND name = # {name} AND gender= # {gender}

The execution result is:

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

2. Insert statements insert into tb_user id, name, gender, # {id,jdbcType=BIGINT}, # {name,jdbcType=VARCHAR}, # {gender,jdbcType=BIGINT}

The execution result is:

Insert into tb_user (id,name,gender) values (1, "Zhang San", 20)

3.update statement update tb_user name = # {name}, gender = # {gender},

The execution result is:

Update tb_user set name = "Zhang San", gender = 30 where id = 1

Thank you for your reading, the above is the "mybatis output SQL formatting method is what" the content, after the study of this article, I believe you on the mybatis output SQL formatting method is what this problem has a deeper understanding, the specific use of the need for you to practice verification. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.