文章目录 1.集成druid图形化监控 1.配置application.yml 2.测试访问 http://localhost:项目端口/druid 2.MyBatis优化器(显示完整sql) 1.目录 2.SqlBeautyInterceptor.java:sql拦截器 3.MybatisConfiguration.java:将sql拦截器注入容器 4.测试 5.MyBatis优化器动态加载 1.修改MybatisConfiguration.java的bean注入方式 2.application.yml中配置启用sql优化器
1.集成druid图形化监控
1.配置application.yml
spring : datasource : driver-class-name : com.mysql.cj.jdbc.Driverusername : password : url : jdbc: mysql: //bj- - grp- .sql.tencentcdb.com: 24169/sun_frame? useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false type : com.alibaba.druid.pool.DruidDataSource druid : initial-size : 20 min-idle : 20 max-active : 100 max-wait : 60000 stat-view-servlet : enabled : true url-pattern : /druid/* login-username : adminlogin-password : 123456 filter : stat : enabled : true log-slow-sql : true slow-sql-millis : 2000
2.测试访问 http://localhost:项目端口/druid
2.MyBatis优化器(显示完整sql)
1.目录
2.SqlBeautyInterceptor.java:sql拦截器
package com. sunxiansheng. inteceptor ; import org. apache. ibatis. executor. statement. StatementHandler ;
import org. apache. ibatis. mapping. BoundSql ;
import org. apache. ibatis. mapping. ParameterMapping ;
import org. apache. ibatis. plugin. * ;
import org. apache. ibatis. session. ResultHandler ;
import org. apache. ibatis. session. defaults. DefaultSqlSession . StrictMap ;
import org. slf4j. Logger ;
import org. slf4j. LoggerFactory ; import java. lang. reflect. Field ;
import java. sql. Statement ;
import java. util. * ;
import java. util. regex. Matcher ;
import java. util. regex. Pattern ;
@Intercepts ( { @Signature ( type = StatementHandler . class , method = "query" , args = { Statement . class , ResultHandler . class } ) , @Signature ( type = StatementHandler . class , method = "update" , args = { Statement . class } ) , @Signature ( type = StatementHandler . class , method = "batch" , args = { Statement . class } )
} )
public class SqlBeautyInterceptor implements Interceptor { private static final Logger logger = LoggerFactory . getLogger ( SqlBeautyInterceptor . class ) ; private static final Set < Class < ? > > PRIMITIVE_WRAPPER_CLASSES = new HashSet < > ( Arrays . asList ( Byte . class , Short . class , Integer . class , Long . class , Double . class , Float . class , Character . class , Boolean . class ) ) ; @Override public Object intercept ( Invocation invocation) throws Throwable { StatementHandler statementHandler = ( StatementHandler ) invocation. getTarget ( ) ; long startTime = System . currentTimeMillis ( ) ; try { return invocation. proceed ( ) ; } finally { long endTime = System . currentTimeMillis ( ) ; long sqlCost = endTime - startTime; BoundSql boundSql = statementHandler. getBoundSql ( ) ; String sql = boundSql. getSql ( ) ; Object parameterObject = boundSql. getParameterObject ( ) ; List < ParameterMapping > parameterMappingList = boundSql. getParameterMappings ( ) ; sql = formatSql ( sql, parameterObject, parameterMappingList) ; sql = beautifySql ( sql) ; logger. info ( "\n========================\nSQL:\n{}\n执行耗时: [{} ms]\n========================" , sql, sqlCost) ; } } @Override public Object plugin ( Object target) { return Plugin . wrap ( target, this ) ; } @Override public void setProperties ( Properties properties) { } private String formatSql ( String sql, Object parameterObject, List < ParameterMapping > parameterMappingList) { if ( sql == null || sql. trim ( ) . isEmpty ( ) ) { return "" ; } sql = beautifySql ( sql) ; if ( parameterObject == null || parameterMappingList == null || parameterMappingList. isEmpty ( ) ) { return sql; } String sqlWithoutReplacePlaceholder = sql; try { if ( isStrictMap ( parameterObject. getClass ( ) ) ) { StrictMap < ? > strictMap = ( StrictMap < ? > ) parameterObject; if ( isList ( strictMap. get ( "list" ) . getClass ( ) ) ) { sql = handleListParameter ( sql, ( List < ? > ) strictMap. get ( "list" ) ) ; } } else if ( isMap ( parameterObject. getClass ( ) ) ) { sql = handleMapParameter ( sql, ( Map < ? , ? > ) parameterObject, parameterMappingList) ; } else { sql = handleCommonParameter ( sql, parameterMappingList, parameterObject) ; } } catch ( Exception e) { logger. error ( "Error formatting SQL: " , e) ; return sqlWithoutReplacePlaceholder; } return sql; } private String handleCommonParameter ( String sql, List < ParameterMapping > parameterMappingList, Object parameterObject) throws Exception { Class < ? > parameterObjectClass = parameterObject. getClass ( ) ; List < Field > allFields = new ArrayList < > ( ) ; while ( parameterObjectClass != null ) { allFields. addAll ( Arrays . asList ( parameterObjectClass. getDeclaredFields ( ) ) ) ; parameterObjectClass = parameterObjectClass. getSuperclass ( ) ; } for ( ParameterMapping parameterMapping : parameterMappingList) { String propertyValue; String propertyName = parameterMapping. getProperty ( ) ; Field field = allFields. stream ( ) . filter ( f -> f. getName ( ) . equals ( propertyName) ) . findFirst ( ) . orElse ( null ) ; if ( field != null ) { field. setAccessible ( true ) ; propertyValue = String . valueOf ( field. get ( parameterObject) ) ; if ( parameterMapping. getJavaType ( ) . isAssignableFrom ( String . class ) ) { propertyValue = "\"" + propertyValue + "\"" ; } } else if ( isPrimitiveOrPrimitiveWrapper ( parameterObject. getClass ( ) ) ) { propertyValue = parameterObject. toString ( ) ; } else { continue ; } sql = sql. replaceFirst ( "\\?" , Matcher . quoteReplacement ( propertyValue) ) ; } return sql; } private String handleMapParameter ( String sql, Map < ? , ? > paramMap, List < ParameterMapping > parameterMappingList) { for ( ParameterMapping parameterMapping : parameterMappingList) { Object propertyName = parameterMapping. getProperty ( ) ; Object propertyValue = paramMap. get ( propertyName) ; if ( propertyValue != null ) { if ( propertyValue. getClass ( ) . isAssignableFrom ( String . class ) ) { propertyValue = "\"" + propertyValue + "\"" ; } sql = sql. replaceFirst ( "\\?" , Matcher . quoteReplacement ( propertyValue. toString ( ) ) ) ; } } return sql; } private String handleListParameter ( String sql, Collection < ? > col) { if ( col != null && ! col. isEmpty ( ) ) { for ( Object obj : col) { String value = obj. toString ( ) ; if ( obj. getClass ( ) . isAssignableFrom ( String . class ) ) { value = "\"" + value + "\"" ; } sql = sql. replaceFirst ( "\\?" , Matcher . quoteReplacement ( value) ) ; } } return sql; } private String beautifySql ( String sql) { sql = sql. replaceAll ( "[\\s\n]+" , " " ) . trim ( ) ; sql = sql. replaceAll ( "(?i)\\b(SELECT|INSERT INTO|UPDATE|DELETE FROM|FROM|WHERE|SET|VALUES|LEFT JOIN|RIGHT JOIN|INNER JOIN|OUTER JOIN|GROUP BY|ORDER BY|HAVING|LIMIT|OFFSET)\\b" , "\n$1" ) ; sql = sql. replaceAll ( "(?i)\\b(INSERT INTO [^\\(]+\\()" , "\n$1\n " ) ; sql = sql. replaceAll ( "(?i)\\b(VALUES)\\s*\\(" , "\n$1\n (" ) ; sql = sql. replaceFirst ( "\\)\\s*VALUES" , "\n)\nVALUES" ) ; String [ ] lines = sql. split ( "\n" ) ; StringBuilder formattedSql = new StringBuilder ( ) ; int indentLevel = 0 ; for ( String line : lines) { if ( line. matches ( "(?i)^\\s*(SELECT|INSERT INTO|UPDATE|DELETE FROM|FROM|WHERE|SET|VALUES|LEFT JOIN|RIGHT JOIN|INNER JOIN|OUTER JOIN|GROUP BY|ORDER BY|HAVING|LIMIT|OFFSET)\\b.*" ) ) { if ( line. matches ( "(?i)^\\s*(FROM|WHERE|SET|VALUES|LEFT JOIN|RIGHT JOIN|INNER JOIN|OUTER JOIN|GROUP BY|ORDER BY|HAVING|LIMIT|OFFSET)\\b.*" ) ) { indentLevel-- ; } formattedSql. append ( repeat ( " " , indentLevel) ) . append ( line. trim ( ) ) . append ( "\n" ) ; if ( line. matches ( "(?i)^\\s*(SELECT|INSERT INTO|UPDATE|DELETE FROM)\\b.*" ) ) { indentLevel++ ; } } else { formattedSql. append ( repeat ( " " , indentLevel) ) . append ( line. trim ( ) ) . append ( "\n" ) ; } } formattedSql = new StringBuilder ( formattedSql. toString ( ) . replace ( ") VALUES" , "\n) VALUES" ) ) ; formattedSql = new StringBuilder ( formattedSql. toString ( ) . replaceAll ( "\\)," , "\n)," ) ) ; return formattedSql. toString ( ) . trim ( ) ; } private String formatValues ( String sql) { Pattern pattern = Pattern . compile ( "(?i)(VALUES\\s*\\(([^\\)]+)\\))" ) ; Matcher matcher = pattern. matcher ( sql) ; StringBuffer sb = new StringBuffer ( ) ; while ( matcher. find ( ) ) { String group = matcher. group ( 2 ) ; String [ ] values = group. split ( "," ) ; StringBuilder sbGroup = new StringBuilder ( ) ; sbGroup. append ( "VALUES (\n " ) ; for ( String value : values) { sbGroup. append ( value. trim ( ) ) . append ( ", " ) ; } sbGroup. setLength ( sbGroup. length ( ) - 2 ) ; sbGroup. append ( "\n)" ) ; matcher. appendReplacement ( sb, sbGroup. toString ( ) ) ; } matcher. appendTail ( sb) ; return sb. toString ( ) ; } private String repeat ( String str, int count) { StringBuilder result = new StringBuilder ( ) ; for ( int i = 0 ; i < count; i++ ) { result. append ( str) ; } return result. toString ( ) ; } private boolean isPrimitiveOrPrimitiveWrapper ( Class < ? > clazz) { return clazz. isPrimitive ( ) || PRIMITIVE_WRAPPER_CLASSES . contains ( clazz) ; } private boolean isStrictMap ( Class < ? > clazz) { return StrictMap . class . isAssignableFrom ( clazz) ; } private boolean isList ( Class < ? > clazz) { return List . class . isAssignableFrom ( clazz) ; } private boolean isMap ( Class < ? > clazz) { return Map . class . isAssignableFrom ( clazz) ; }
}
3.MybatisConfiguration.java:将sql拦截器注入容器
import com. sunxiansheng. inteceptor. SqlBeautyInterceptor ;
import org. springframework. context. annotation. Bean ;
import org. springframework. context. annotation. Configuration ;
@Configuration
public class MybatisConfiguration { @Bean public SqlBeautyInterceptor sqlBeautyInterceptor ( ) { return new SqlBeautyInterceptor ( ) ; }
}
4.测试
5.MyBatis优化器动态加载
1.修改MybatisConfiguration.java的bean注入方式
package com. sunxiansheng. config ; import com. sunxiansheng. inteceptor. SqlBeautyInterceptor ;
import org. springframework. boot. autoconfigure. condition. ConditionalOnProperty ;
import org. springframework. context. annotation. Bean ;
import org. springframework. context. annotation. Configuration ;
@Configuration
public class MybatisConfiguration { @Bean @ConditionalOnProperty ( name = { "sql.beauty.show" } , havingValue = "true" , matchIfMissing = true ) public SqlBeautyInterceptor sqlBeautyInterceptor ( ) { System . out. println ( ) ; return new SqlBeautyInterceptor ( ) ; }
}
2.application.yml中配置启用sql优化器