survey

In the recent report writing function, when the data is inaccurate, you need to check the SQL running on the current interface and compare whether there is a problem with the SQL. Then you need to get the SQL statement running on the interface on the business side. The realization of this function is mainly considered from two aspects.
1.Mybatis framework plug-in
Mybatis can get the complete SQL in the whole request thread by setting the plug-in form, intercepting the executed SQL and setting it into ThreadLocal. However, at present, other blogs are SQL placeholders and can not print the SQL completely.
2.Mybatis Log pluging
The source code of mybatis log plugin is mainly used to assemble SQL from the console. There is zero intrusion for business code, which is not suitable for this demand.
This time, we mainly use the plug-in of Mybatis framework to realize the functions. I won't say much nonsense. The above code:

Implementation steps

Create interceptor

@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class})})
public class SqlStatementInterceptor implements Interceptor {

    private final Logger logger = LoggerFactory.getLogger(SqlStatementInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        try {
            return invocation.proceed();
        } finally {
            long endTime = System.currentTimeMillis();
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            BoundSql boundSql = statementHandler.getBoundSql();
            String sql = boundSql.getSql();
            //args contains complete sql
            if (invocation.getArgs().length > 0) {
                try {
                    //getTarget can be obtained. If an error is reported, try invoking getArgs()[0]. Tostring() different SQL connections
                    sql = getTarget(invocation.getArgs()[0]).toString();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            //sql collation
            sql = sql.replace("\n", "").replace("\t", "").replaceAll("\\s+", " ");
            //Collect SQL for this visit
            String threadSql = Constant.THREAD_SQL.get();
            threadSql = threadSql == null ? "" : threadSql;
            threadSql = threadSql + "|" + sql;
            Constant.THREAD_SQL.set(threadSql);

            logger.info("implement SQL: [{}]spend{}ms", sql, (endTime - startTime));
        }
    }

    /**
     * Get the proxied object from the proxy object
     *
     * @param proxy
     * @return
     * @throws Exception
     */
    public Object getTarget(Object proxy) throws Exception {
        Field[] fields = proxy.getClass().getSuperclass().getFields();
        for (Field field : fields) {
            System.out.println(field.getName());
        }
        Field field = proxy.getClass().getSuperclass().getDeclaredField("h");
        field.setAccessible(true);
        //Gets the value of this field in the specified object
        //Gets the value of this field in the Proxy object
        PreparedStatementLogger personProxy = (PreparedStatementLogger) field.get(proxy);
        Field statement = personProxy.getClass().getDeclaredField("statement");
        statement.setAccessible(true);
        return statement.get(personProxy);
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

Add plug-in:

    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setPlugins(new Interceptor[]{new SqlStatementInterceptor()});
        bean.setDataSource(dynamicDataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/**/*.xml"));
        return bean.getObject();
    }

Set response header

Because SQL statements are non business level things, it is not good to put them in the response result body according to the specification.
At first, I used the interceptor to implement it, but the setting of the request header in the postHandler method of the interceptor that stepped on a pit interceptor did not take effect, because the request header returned to the front end after executing the controller, and then executed the postHandler. The following is part of the source code of springMvc

// DispatcherServlet.java class
protected void doDispatch(HttpServletRequest request, HttpServletResponse response) throws Exception {
	....Omit code
	// #1 carry out pre-processing and the processing before entering the controller method
	if (!mappedHandler.applyPreHandle(processedRequest, response)) {
		return;
	}

	// #2 execute the controller method
	mv = ha.handle(processedRequest, response, mappedHandler.getHandler());

	if (asyncManager.isConcurrentHandlingStarted()) {
		return;
	}

	applyDefaultViewName(processedRequest, mv);
	// #3 execute post-processing, that is, the processing after the controller method is executed
	mappedHandler.applyPostHandle(processedRequest, response, mv);
	....Omit code
}

When the #2 method is returned after execution, you can make a breakpoint to have a look


So the interceptor doesn't work. Finally, I implemented it in the way of AOP. The code is as follows:

@Aspect
@Order(1)
@Component
public class SQLAspect implements HandlerInterceptor {

    /**
     * Select the pointcut to annotate the DataSource
     */
    @Pointcut("execution(* com.xxxx.xx.xxx.web.controller..*Controller.*(..))")
    public void dsPointCut() {
    }

    @Around("dsPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        RequestAttributes ra = RequestContextHolder.getRequestAttributes();
        ServletRequestAttributes sra = (ServletRequestAttributes) ra;
        HttpServletResponse response = null;
        if (sra != null) {
            response = sra.getResponse();
        }

        try {
            return point.proceed();
        } finally {
            String sqlList = Constant.THREAD_SQL.get();
            if (!StringUtils.isEmpty(sqlList)) {
                if (response != null) {
                    response.addHeader("Query-Sql", sqlList);
                }
            }
            // Destroy the data source after executing the method
            Constant.THREAD_SQL.remove();
        }
    }
}

So far, the whole business requirements have been realized. Let's explain that printing SQL on the front end will expose the structure of the database, so don't use it in the formal environment. This code is only developed on the test branch and will not be submitted to the main branch.