The interceptor of mybatis implements statistical analysis of SQL Execution time, And mybatis performs statistical analysis.

The interceptor of mybatis implements statistical analysis of SQL Execution time, And mybatis performs statistical analysis.

Reprinted from: http://blog.csdn.net/andyzhaojianhui/article/details/72823055

The idea of the interceptor for mybatis comes from three places

That is, the following three areas can be used. In other cases, developers need to use them according to the actual situation.

1. for paging queries, we can adopt a standardized naming method for paging queries. Then, we can intercept the SQL statements that require paging queries based on the naming method and divide the total number of pages into several pages, the number of pages and the total number of pages are returned in an object. In this way, you only need to call the dao method on the page.

2. read/write Splitting: Before executing the SQL statement, obtain whether the SQL statement is a query method and control the data source to be accessed based on this condition.

3. Statistical analysis of the SQL Execution time is required (the execution here includes network bandwidth because it is not intercepted before and after mysql execution, so the SQL here is not only the real execution time of SQL in the database, it is longer than the actual time)

 

How to implement such an interceptor

First, mybatis has long been expected to have such a requirement for our development, so an org. apache. ibatis. plugin. Interceptor interface is opened.

We only need to implement this interface and add annotations and then rewrite the intercept method.

If you use mybatis. xml, that is, the configuration of mybatis, you can configure the corresponding interceptor name here.

If you are using mybatis managed by spring, you need to configure and register the corresponding interceptor in the spring configuration file.

 

Code Implementation

The following is a brief excerpt of the implementation code for "3", that is, to calculate the SQL Execution time.

There are two other types of development that can be implemented and explored based on your own ideas.

-Hide code

package com.ssm;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import org.apache.ibatis.executor.Executor;
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.plugin.Signature;
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.apache.log4j.Logger;  
@Intercepts({     
 @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 }) }) 
 public class SqlStatementInterceptor implements Interceptor{            private static Logger logger = Logger.getLogger(SqlStatementInterceptor.class);       
 @SuppressWarnings("unused")      private Properties properties;            @Override      public Object intercept(Invocation arg0) throws Throwable {     
     MappedStatement mappedStatement = (MappedStatement) arg0.getArgs()[0];          Object parameter = null;          if (arg0.getArgs().length > 1) { 
             parameter = arg0.getArgs()[1];          }          String sqlId = mappedStatement.getId();          BoundSql boundSql = mappedStatement.getBoundSql(parameter);    
      Configuration configuration = mappedStatement.getConfiguration();                    Object returnValue = null;          long start = System.currentTimeMillis();     
     returnValue = arg0.proceed();          long end = System.currentTimeMillis();          long time = (end - start);            if (time > 1) {     
         String sql = getSql(configuration, boundSql, sqlId, time);              logger.error(sql);        }                    return returnValue;      }      
      public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {          String sql = showSql(configuration, boundSql);     
     StringBuilder str = new StringBuilder(100);          str.append(sqlId);          str.append(":");         
 str.append(sql);          str.append(":");          str.append(time);          str.append("ms");          return str.toString();      }          
  public static String showSql(Configuration configuration, BoundSql boundSql) {          Object parameterObject = boundSql.getParameterObject();        
  List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();          String sql = boundSql.getSql().replaceAll("[\\s]+", " ");       
   if (parameterMappings.size() > 0 && parameterObject != null) {              TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();          
    if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {                  sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));                 }
 else {                  MetaObject metaObject = configuration.newMetaObject(parameterObject);         
         for (ParameterMapping parameterMapping : parameterMappings) {                      String propertyName = parameterMapping.getProperty();               
       if (metaObject.hasGetter(propertyName)) {                          Object obj = metaObject.getValue(propertyName);                    
      sql = sql.replaceFirst("\\?", getParameterValue(obj));                      } else if (boundSql.hasAdditionalParameter(propertyName)) {                      
    Object obj = boundSql.getAdditionalParameter(propertyName);                          sql = sql.replaceFirst("\\?", getParameterValue(obj));               
      }                  }              }          }          return sql;      }           
 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(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);          
    value = "'" + formatter.format(new Date()) + "'";          } else {              if (obj != null) {                  value = obj.toString();              } 
else {                  value = "";              }             }          return value;      }        @Override      public Object plugin(Object arg0) {      
    return Plugin.wrap(arg0, this);      }        @Override      
public void setProperties(Properties arg0) {          this.properties = arg0;      }    }

The following is the configuration in spring. If you configure the mybatis configuration file separately, you need to query how to configure

-Hide code

<! -- Configure mybitasSqlSessionFactoryBean --> <bean id = "sqlSessionFactory" class = "org. mybatis. spring. sqlSessionFactoryBean "> 
<property name =" dataSource "ref =" dataSource "/> <property name =" typeAliasesPackage "value =" com/ssm/entity "> </property> 
<property name = "mapperLocations" value = "classpath *: com/ssm/dao/sqlxml /*. xml "> </property> <property name =" plugins "> 
<array> <bean class =" com. ssm. sqlStatementInterceptor "> 
<property name =" properties "> <value> property-key = property-value </value> </property> </bean> </array> </property> </bean>

The last executed SQL and sqlID and SQL Execution time are output in the log.

 

 

References:

Implement by PAGE (I did not implement it myself, because the actual situation does not have such a special need for such business logic ):

Http://www.cnblogs.com/jethypc/p/5149183.html

Execute the time statistics (I modified the spring configuration, but I don't know why the following configuration is useless ):

Http://blog.csdn.net/tq02h2a/article/details/50772652

Read/write splitting (the fourth solution is to use the interceptor ):

Http://www.jianshu.com/p/2222257f96d3

View comments