MyBatis7: MyBatis plug-in and example-print each SQL statement and its execution time, mybatis7mybatis

MyBatis7: MyBatis plug-in and example-print each SQL statement and its execution time, mybatis7mybatis

Plugins

Abstract A text clip from the official MyBatis document.

MyBatis allows you to intercept calls of mapped statements at a certain point. By default, MyBatis allows plug-ins to intercept method calls.

• Executor (update, query, flushStatements, commint, rollback, getTransaction, close, isClosed)
• ParameterHandler (getParameterObject, setParameters)
• ResultSetHandler (handleResultSets, handleOutputParameters)
• StatementHandler (prepare, parameterize, batch, update, query)

The details of methods in these classes can be found by viewing the signatures of each method, and their source code exists in the MyBatis release package. You should understand the behavior of the method you override, assuming that you do more than monitor calls. If you try to modify or override a given method, you may break the core of MyBatis. This is a low-level class and method, so use the plug-in with caution.

 

Plug-in Example: print each SQL statement and its execution time

The following code demonstrates how to use the MyBatis plug-in. The scenario to be demonstrated is to print each truly executed SQL statement and its execution time. This is a very useful requirement. MyBatis logs can record SQL statements, but there are several problems:

Writing the MyBatis plug-in is very simple. You only need to implement the Interceptor interface. Here I name my Interceptor SqlCostInterceptor:

1/** 2 * SQL Execution time record interceptor 3 */4 @ Intercepts ({@ Signature (type = StatementHandler. class, method = "query", args = {Statement. class, ResultHandler. class}), 5 @ Signature (type = StatementHandler. class, method = "update", args = {Statement. class}) 6 public class SqlCostInterceptor implements Interceptor {7 8 @ Override 9 public Object intercept (Invocation invocation) throws Throwable {10 Object target = Invocation. getTarget (); 11 if (target instanceof StatementHandler) {12 long startTime = System. currentTimeMillis (); 13 StatementHandler statementHandler = (StatementHandler) target; 14 try {15 return invocation. proceed (); 16} finally {17 long endTime = System. currentTimeMillis (); 18 long sqlCost = endTime-startTime; 19 20 BoundSql boundSql = statementHandler. getBoundSql (); 21 String SQL = B OundSql. getSql (); 22 Object parameterObject = boundSql. getParameterObject (); 23 List <ParameterMapping> parameterMappingList = boundSql. getParameterMappings (); 24 25 // format the SQL statement, remove line breaks, replace parameter 26 SQL = formatSql (SQL, parameterObject, parameterMappingList); 27 28 System. out. println ("SQL: [" + SQL + "] execution time [" + sqlCost + "ms]"); 29} 30} 31 32 return invocation. proceed (); 33} 34 35 @ Override 36 public Ob Ject plugin (Object target) {37 return Plugin. wrap (target, this); 38} 39 40 @ Override 41 public void setProperties (Properties properties) {42 43} 44 45 @ SuppressWarnings ("unchecked ") 46 private String formatSql (String SQL, Object parameterObject, List <ParameterMapping> parameterMappingList) {47 SQL = SQL. replace ("\ n ",""). replace ("\ t ",""). replace ("",""). replace ("(","("). replace (")",")" ); 48 // if an exception occurs, SQL 49 String sqlWithoutReplacePlaceholder = SQL is returned; 50 51 try {52 if (parameterMappingList! = Null) {53 Class <?> ParameterObjectClass = parameterObject. getClass (); 54 55 if (isMap (parameterObjectClass) {56 Map <String, Object> paramMap = (Map <String, Object>) parameterObject; 57 for (ParameterMapping parameterMapping: parameterMappingList) {58 String propertyName = parameterMapping. getProperty (); 59 Object propertyValue = paramMap. get (propertyName); 60 if (propertyValue! = Null) {61 if (propertyValue. getClass (). isAssignableFrom (String. class) {62 propertyValue = "\" "+ propertyValue +" \ ""; 63} 64 65 SQL = SQL. replaceFirst ("\\? ", PropertyValue. toString (); 66} 67} 68} else {69 for (ParameterMapping parameterMapping: parameterMappingList) {70 String propertyName = parameterObjectClass. isPrimitive ()? "Value" 71: parameterMapping. getProperty (); 72 Field field = parameterObjectClass. getDeclaredField (propertyName); 73 field. setAccessible (true); 74 String propertyValue = String. valueOf (field. get (parameterObject); 75 if (propertyValue. getClass (). isAssignableFrom (String. class) {76 propertyValue = "\" "+ propertyValue +" \ ""; 77} 78 79 SQL = SQL. replaceFirst ("\\? ", PropertyValue); 80} 81} 82} 83} catch (Exception e) {84 return sqlWithoutReplacePlaceholder; 85} 86 87 return SQL; 88} 89 90 private boolean isMap (Class <?> ParameterObjectClass) {91 Class <?> [] Classes = parameterObjectClass. getInterfaces (); 92 for (Class <?> Clazz: classes) {93 if (clazz. isAssignableFrom (Map. class) {94 return true; 95} 96} 97 98 return false; 99} 100 101}

Analyze this code.

The first is the annotation @ Intercepts and @ Signature. These two annotations are required, because the Plugin wrap method will take the parameters in these two annotations. @ Intercepts can define multiple @ Signature. One @ Signature indicates that the methods that meet the following conditions will be blocked:

• The interface must be a type defined by type.
• The method name must be the same as the method name.
• The Class type of the method parameter must be consistent with the sequence of Class types defined by args.

The next question is: There are four interfaces to intercept. Why do I use StatementHandler to intercept them? According to the name, ParameterHandler and ResultSetHandler process parameters. The latter process results are not usable, and the rest are Executor and StatementHandler. The reason for intercepting StatementHandler is not to use Executor:

• Executor's update and query methods may use the first-level cache of MyBatis, which leads to statistics that are not the real SQL Execution time.
• The update and query methods of StatementHandler collect statistics on the execution time of the PreparedStatement execute method in any way, despite some errors (the error mainly comes from calculating the processing result time ), but the difference is not big

Next, let's talk about the setProperties method. You can configure some configuration Properties in the <plugin> </plugin> sub-tag <property/>. All the configuration Properties will be in the Properties parameter, the setProperties method can obtain the configured properties for processing.

Next, let's talk about the plugin method, which is used to generate a proxy for the target interface. You do not need to write the proxy generation method yourself, the Plugin class of MyBatis already provides us with the wrap method (if you have your own logic, you can also use the Plugin. add the wrap method before and after, but you must use Plugin. wrap method generation proxy), let's take a look at the implementation of this method:

 1 public static Object wrap(Object target, Interceptor interceptor) { 2     Map<Class<?>, Set<Method>> signatureMap = getSignatureMap(interceptor); 3     Class<?> type = target.getClass(); 4     Class<?>[] interfaces = getAllInterfaces(type, signatureMap); 5     if (interfaces.length > 0) { 6       return Proxy.newProxyInstance( 7           type.getClassLoader(), 8           interfaces, 9           new Plugin(target, interceptor, signatureMap));10     }11     return target;12 }

Because the target here must be an interface, you can safely use the Proxy class provided by JDK. Here it is equivalent to generating a Proxy for the method if the interface meets the method signature.

The last step is the intercept method. Here is the core code of the Interceptor. I will not explain the logic of the method. Let's take a look at it. The only thing to note is that no matter whatIn the end, you must return invocation. proceed ()To ensure layer-by-layer calls of the interceptor.

 

Xml file configuration is an effect demonstration

After writing the plug-in, you only need to configure it once in the config. xml file, which is very simple:

 1 <plugins> 2     <plugin interceptor="org.xrq.mybatis.plugin.SqlCostInterceptor" /> 3 </plugins>

Here, each <plugin> sub-tag represents a plug-in, and interceptor represents the complete path of the interceptor.

With the class and this configuration, you can use SqlCostInterceptor. SqlCostInterceptor is common, but the CRUD of each person is different. I will print the result of CRUD Execution here:

SQL: [insert into mail (id, create_time, modify_time, web_id, mail, use_for) values (null, now (), now (), "1", "123@sina.com ", "Personal use");] execution time [1 ms] SQL: [insert into mail (id, create_time, modify_time, web_id, mail, use_for) values (null, now (), now (), "2", "123@qq.com", "enterprise use");] execution time [1 ms] SQL: [insert into mail (id, create_time, modify_time, web_id, mail, use_for) values (null, now (), now (), "3", "123@sohu.com", "registered account used");] execution time [0 ms]

The complete SQL statement and the execution time of the SQL statement are printed.

However, it should be noted that this plug-in is just a simple Demo. I have not fully tested it and it should not cover all scenarios, therefore, if you want to use this code snippet to print the real SQL statement and its execution time, you still need to make modifications on this basis. However, even if you do not change the code, this plug-in will beautify the SQL statement, it's okay to remove some line breaks.

The implementation principle of the MyBatis plug-in will be explained in detail in my [MyBatis source code analysis] series of articles.