由于公司項(xiàng)目需要晒奕，在每次查詢完成之后都要把完整的sql語句打印出來煮寡，需要集成輸出完整SQL插件到框架中
單數(shù)據(jù)源配置
編寫攔截器妆毕，并實(shí)現(xiàn)打印sql日志
package com.api.interceptor;
import com.sun.deploy.util.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
/**
* @description: SQLInterceptor
* @author: chh
* @date: 2022/10/25 9:26
**/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
@Signature(type = StatementHandler.class, method = "getBoundSql", args = {}),
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
})
@Component //SpringBoot使用該注解流强，注入容器
public class SqlInterceptor implements Interceptor {
private static final Logger log = LoggerFactory.getLogger(SqlInterceptor.class);
private static final Map<String, Object> MAP = new ConcurrentHashMap<>(2);
private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 重寫intercept痹届，攔截sql，拼接完整sql語句
*
* @param invocation 調(diào)用
* @return Object
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object returnValue;
long start = System.currentTimeMillis();
returnValue = invocation.proceed();
long end = System.currentTimeMillis();
long time = end - start;
try {
final Object[] args = invocation.getArgs();
//獲取原始的ms
if (!(args[0] instanceof MappedStatement)) {
return returnValue;
}
MappedStatement ms = (MappedStatement) args[0];
Object parameter = null;
//獲取參數(shù)打月，if語句成立队腐，表示sql語句有參數(shù)，參數(shù)格式是map形式
if (invocation.getArgs().length > 1) {
parameter = invocation.getArgs()[1];
}
Method method = invocation.getMethod();
String name = method.getName();
String commandName = ms.getSqlCommandType().name();
if (commandName.startsWith("INSERT")) {
name = name + "=新增";
} else if (commandName.startsWith("UPDATE")) {
name = name + "=修改";
} else if (commandName.startsWith("DELETE")) {
name = name + "=刪除";
} else if (commandName.startsWith("SELECT")) {
name = name + "=查詢";
}
// 獲取到節(jié)點(diǎn)的id,即sql語句的id
String sqlId = ms.getId();
// BoundSql就是封裝myBatis最終產(chǎn)生的sql類
BoundSql boundSql = ms.getBoundSql(parameter);
// 獲取節(jié)點(diǎn)的配置
Configuration configuration = ms.getConfiguration();
// 獲取到最終的sql語句
String sql = getSql(configuration, boundSql, sqlId, time, returnValue, name);
log.info(sql);
} catch (Exception e) {
log.error("攔截sql處理出錯(cuò)奏篙，出錯(cuò)原因：" + e.getMessage());
e.printStackTrace();
}
return returnValue;
}
/**
* 封裝了一下sql語句香到，使得結(jié)果返回完整xml路徑下的sql語句節(jié)點(diǎn)id + sql語句
*
* @param configuration 配置
* @param boundSql boundSql
* @param sqlId sqlId
* @param time 執(zhí)行時(shí)間
* @param result 結(jié)果
* @param name sql操作類型
* @return String
*/
public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time, Object result, String name) {
showSql(configuration, boundSql);
String message = "[SqlInterceptor] 執(zhí)行 [" + name + "] 時(shí)間 [" + formatter.format(System.currentTimeMillis()) + "] sql耗時(shí) [" + (double) time / 1000 + "] s";
StringBuilder str = new StringBuilder();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
str.append("\n").append("----------------------------begin【SQL Execute Message】--------------------------------\n");
str.append("【方法】").append(sqlId).append("\n");
str.append("【sql】").append(MAP.get("sql"));
str.append("\n");
str.append("【參數(shù)映射】").append(parameterMappings);
str.append("\n");
str.append("【參數(shù)對(duì)象】").append(StringUtils.join((List<String>) MAP.get("parameters"), ", "));
str.append("\n");
str.append("【結(jié)果】 ");
if (result != null) {
if (result instanceof List) {
str.append("共 ").append(((List) result).size()).append(" 條記錄\n");
} else if (result instanceof Collection) {
str.append("共 ").append(((Collection) result).size()).append(" 條記錄\n");
} else {
str.append("共 1 條記錄").append("\n");
}
//str.append("【結(jié)果詳情】").append(result).append("\n");
} else {
str.append("【結(jié)果】 NULL").append("\n");
}
str.append("【執(zhí)行信息】").append(message);
str.append("\n");
str.append("----------------------------end【SQL Execute Message】--------------------------------\n");
return str.toString();
}
/**
* 如果參數(shù)是String，則添加單引號(hào)报破，
* 如果是日期悠就，則轉(zhuǎn)換為時(shí)間格式器并加單引號(hào)； 對(duì)參數(shù)是null和不是null的情況作了處理
*
* @param obj 參數(shù)
* @return String
*/
private static String getParameterValue(Object obj) {
String value;
if (obj instanceof String) {
value = "'" + obj + "'";
} else if (obj instanceof Date) {
value = "'" + formatter.format(obj) + "'";
} else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
/**
* 進(jìn)行充易？的替換
*
* @param configuration 配置
* @param boundSql boundSql
* @return void
*/
public static void showSql(Configuration configuration, BoundSql boundSql) {
// 獲取參數(shù)
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql
.getParameterMappings();
// sql語句中多個(gè)空格都用一個(gè)空格代替
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
List<String> list = new ArrayList<>();
if (!CollectionUtils.isEmpty(parameterMappings) && parameterObject != null) {
// 獲取類型處理器注冊(cè)器梗脾，類型處理器的功能是進(jìn)行java類型和數(shù)據(jù)庫類型的轉(zhuǎn)換
// 如果根據(jù)parameterObject.getClass(）可以找到對(duì)應(yīng)的類型，則替換
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
list.add(parameterObject + "(" + parameterObject.getClass().getSimpleName() + ")");
} else {
// MetaObject主要是封裝了originalObject對(duì)象盹靴，提供了get和set的方法用于獲取和設(shè)置originalObject的屬性值,主要支持對(duì)JavaBean炸茧、Collection、Map三種類型對(duì)象的操作
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
if (Objects.nonNull(obj)) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
list.add(parameterMapping.getProperty() + "=" + obj + "(" + obj.getClass().getSimpleName() + ")");
} else {
sql = sql.replaceFirst("\\?", "null");
list.add(parameterMapping.getProperty() + "=null");
}
} else if (boundSql.hasAdditionalParameter(propertyName)) {
// 該分支是動(dòng)態(tài)sql
Object obj = boundSql.getAdditionalParameter(propertyName);
if (Objects.nonNull(obj)) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
list.add(parameterMapping.getProperty() + "=" + obj + "(" + obj.getClass().getSimpleName() + ")");
} else {
sql = sql.replaceFirst("\\?", "null");
list.add(parameterMapping.getProperty() + "=null");
}
} else {
sql = sql.replaceFirst("\\?", "缺失");
list.add("缺失");
}//打印出缺失稿静，提醒該參數(shù)缺失并防止錯(cuò)位
}
}
}
MAP.put("sql", sql);
MAP.put("parameters", list);
}
@Override
public Object plugin(Object arg0) {
return Plugin.wrap(arg0, this);
}
@Override
public void setProperties(Properties properties) {}
}
在SpringBoot啟動(dòng)類通過bean注入即可
@Bean
public SqlInterceptor sqlInterceptor (){
return new SqlInterceptor();
}
多數(shù)據(jù)源配置
在集成的過程中梭冠，出現(xiàn)了多數(shù)據(jù)源的情況下，[攔截器]失效的問題改备，死活不打印日志控漠，解決方案如下
@Configuration
@MapperScan(basePackages = "com.**.dao", sqlSessionFactoryRef = "sqlSessionFactory1")
public class MybatisDataSourceAutoConfiguration {
@Primary
@Bean(name = "sqlSessionFactory1")
public SqlSessionFactory basicSqlSessionFactory(DruidDataSource basicDataSource) throws Exception {
MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
factoryBean.setDataSource(basicDataSource);
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
return factoryBean.getObject();
}
}
繼續(xù)測(cè)試，發(fā)現(xiàn)還是沒打印日志信息：
原來執(zhí)行完sessionFactoryBean.getObject() 后，插件雖然由spring管理盐捷，但不會(huì)生效
查了官方文檔后偶翅，發(fā)現(xiàn)需要預(yù)先配置好加入的插件，配置如下：
@Primary
@Bean(name = "sqlSessionFactory1")
public SqlSessionFactory basicSqlSessionFactory(@Qualifier("dataSource1") DruidDataSource basicDataSource) throws Exception {
MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
factoryBean.setDataSource(basicDataSource);
/* start */
//配置攔截器插件
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.addInterceptor(new SqlInterceptor());
factoryBean.setConfiguration(configuration);
/* end */
factoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
return factoryBean.getObject();
}
至此碉渡，成功打泳鬯！希望對(duì)你有幫助滞诺！
