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

Background

Recently, interviews are often asked questions about database affairs. Maybe I just know that I look confused after adding a note @ Transactional. Now it is found that this area is really often overlooked, but the interviewer likes this kind of look is not commonly used, but very important questions, so as to take them by surprise. Stop blowing water and start the text.

Solution 1: faceted programming @ Aspect

The main purpose of this scheme is to surround the section of the interface method under the mapper package, and then calculate the time difference before and after. This is typical AOP knowledge, but this kind of calculation is rough, but it is also a solution. The specific methods are as follows:

@ Aspect@Component@Slf4jpublic class MapperAspect {@ AfterReturning ("execution (* cn.xbmchina.mybatissqltime.mapper.*Mapper.* (..)") Public void logServiceAccess (JoinPoint joinPoint) {log.info ("Completed:" + joinPoint);} / * monitors all public methods of the cn.xbmchina.mybatissqltime.mapper..*Mapper package and its subpackages * / @ Pointcut ("execution (* cn.xbmchina.mybatissqltime.mapper.*Mapper.* (..)") Private void pointCutMethod () {} / * * statements surround the notification * * @ param pjp * @ return * @ throws Throwable * / @ Around ("pointCutMethod ()") public Object doAround (ProceedingJoinPoint pjp) throws Throwable {long begin = System.nanoTime (); Object obj = pjp.proceed (); long end = System.nanoTime () Log.info ("call Mapper method: {}, parameters: {}, execution time: {} nanoseconds, time: {} milliseconds", pjp.getSignature () .toString (), Arrays.toString (pjp.getArgs ()), (end-begin), (end-begin) / 1000000); return obj;}}

Plan 2: plug-ins for mybatis

MyBatis has plug-ins involved in the process of creating the four major objects. The plug-in can use the dynamic proxy mechanism to wrap the target object layer by layer, and achieve the effect of intercepting the target object before the target method is executed.

MyBatis allows intercepting calls to be made at some point during the execution of a mapped statement.

By default, the method calls that MyBatis allows you to intercept with plug-ins include:

① Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)

② ParameterHandler (getParameterObject, setParameters)

③ ResultSetHandler (handleResultSets, handleOutputParameters)

④ StatementHandler (prepare, parameterize, batch, update, query)

Here is the code:

Import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;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.session.ResultHandler;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.stereotype.Component;import java.sql.Statement Import java.util.List;import java.util.Properties / * * Sql execution time logging interceptor * * @ author zero * 17:05:28 on December 13, 2019 * / @ 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}) @ Componentpublic class SqlExecuteTimeCountInterceptor implements Interceptor {private static Logger logger = LoggerFactory.getLogger (SqlExecuteTimeCountInterceptor.class) The maximum length of the printed parameter string * / private final static int MAX_PARAM_LENGTH = 50; / * the maximum SQL length of the record * / private final static int MAX_SQL_LENGTH = 200; @ Override public Object intercept (Invocation invocation) throws Throwable {Object target = invocation.getTarget (); long startTime = System.currentTimeMillis (); StatementHandler statementHandler = (StatementHandler) target; try {return invocation.proceed () } finally {long endTime = System.currentTimeMillis (); long timeCount = endTime-startTime; BoundSql boundSql = statementHandler.getBoundSql (); String sql = boundSql.getSql (); Object parameterObject = boundSql.getParameterObject (); List parameterMappingList = boundSql.getParameterMappings (); / / format Sql statement, remove newline characters, replace parameter sql = formatSQL (sql, parameterObject, parameterMappingList) Logger.info ("execution SQL: [, {}] execution time [{} ms]", sql, timeCount) }} / * format / beautify SQL statement * * @ param sql sql statement * @ param parameterObject parameter List * @ return formatted SQL * / private String formatSQL (String sql, Object parameterObject, List parameterMappingList) {/ / enter sql string null to determine if (sql = = null | | sql.length () = 0) {return "" } / / beautify sql sql = beautifySql (sql); / / for scenarios with no parameters, simply beautify sql and return if (parameterObject = = null | | parameterMappingList = = null | | parameterMappingList.size () = = 0) {return sql;} return LimitSQLLength (sql) } / * return the SQL statement after the length limit * @ param sql original SQL statement * / private String LimitSQLLength (String sql) {if (sql = = null | | sql.length () = = 0) {return ";} if (sql.length () > MAX_SQL_LENGTH) {return sql.substring (0, MAX_SQL_LENGTH);} else {return sql } @ Override public Object plugin (Object target) {return Plugin.wrap (target, this);} @ Override public void setProperties (Properties properties) {} / * replace SQL? The corresponding value retains only the first 50 characters * * @ param sql sql statement * @ param valueOf? The corresponding value * / private String replaceValue (String sql, String valueOf) {/ / takes only the first 50 if (valueOf! = null & & valueOf.length () > MAX_PARAM_LENGTH) {valueOf = valueOf.substring (0, MAX_PARAM_LENGTH);} sql = sql.replaceFirst ("\\?", valueOf); return sql } / * beautify sql * * @ param sql sql statement * / private String beautifySql (String sql) {sql = sql.replaceAll ("[\\ s\ n] +", "); return sql;}}

Plan 3: use druid directly

This is what we usually use the most, but we can just talk about it in the interview, and it's probably not easy to ask.

The configuration application.yml file for Springboot+druid is as follows:

Spring: datasource: url: jdbc:mysql://localhost:3306/testdb1?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC driver-class-name: com.mysql.jdbc.Driver # mysql8.0 used com.mysql.jdbc.Driver username: root password: root platform: mysql # to introduce druid connection pool into our configuration through this configuration. Spring will try its best to determine what the type is, and then match the driver class according to the situation. Type: com.alibaba.druid.pool.DruidDataSource druid: initial-size: 5 # initialization size min-idle: 5 # minimum max-active: 100 # maximum max-wait: 60000 # configure the time to get connection wait timeout time-between-eviction-runs-millis: 60000 # configure how often before entering the line to detect idle connections that need to be closed Unit is millisecond min-evictable-idle-time-millis: 300000 # specifies the minimum amount of time before an idle connection can be cleared, in millisecond validationQuery: select 'x' test-while-idle: true # whether to perform a connection test when the connection is idle test-on-borrow: false # when borrowing a connection from the connection pool Whether to test the connection test-on-return: false # whether to test the connection when the connection is returned to the connection pool filters: config,wall,stat # configure the filters of monitoring statistics interception. After removing the monitoring interface, sql cannot count. 'wall' is used for firewall poolPreparedStatements: true # Open PSCache And specify the size of the PSCache on each connection maxPoolPreparedStatementPerConnectionSize: 20 maxOpenPreparedStatements: 20 # turn on the mergeSql function through the connectProperties attribute Slow SQL recording connectionProperties: druid.stat.slowSqlMillis=200;druid.stat.logSlowSql=true Config.decrypt=false # merge monitoring data of multiple DruidDataSource # use-global-data-source-stat: true # WebStatFilter configuration. For instructions, please refer to Druid Wiki Configuration _ configuration WebStatFilter web-stat-filter: enabled: true # whether to enable the default value true url-pattern: / * exclusions: / druid/*,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico session-stat-enable: true session-stat-max-count: 10 # StatViewServlet configuration. For instructions, please refer to StatFilter. Configure _ StatViewServlet configure stat-view-servlet: enabled: true # whether to enable StatViewServlet default value true url-pattern: / druid/* reset-enable: true login-username: admin login-password: admin

Summary

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.