Mybatis' SQL statement executed by exception and printed to the log
Recently, I have made an operation and maintenance requirement. I hope that when inserting and updating SQL statements, if the execution fails, it can be printed to a special log file. It can be used to recover data in the database.
I have conducted online research on solutions that can implement this function:
1. The executed SQL statement can be obtained through the Mybatis interceptor
2. Since our databases are written asynchronously, there will be a problem of multi-threading. If you store a variable, the problem of sql being overwritten by other threads before it is read out. The thread is not safe. So consider using ThreadLocal to store the SQL statement of the Mybatis interceptor.
3. In the exception catch code segment, take out the sql statement stored in ThreadLocal and print it to a separate log file.
Specific implementation code
Mybatis interceptor (only intercepts insertion and update operations, and then stitches SQL and stuffs ThreadLocal):
/** * Mybatis interceptor, used to splice sql statements * * @author lightonyang * @date 2022/10/27 */ @Intercepts({ @Signature(type = , method = "update", args = {, }), }) public class MybatisInterceptor implements Interceptor { // If the parameter is String, add single quotes, if it is a date, convert to a time formatter and add single quotes; deal with cases where the parameters are null and not null private static String getParameterValue(Object obj) { String value = null; if (obj instanceof String) { value = "'" + () + "'"; } else if (obj instanceof Date) { DateFormat formatter = (, , ); value = "'" + (new Date()) + "'"; } else { if (obj != null) { value = (); } else { value = "null" ; } } return value; } // conduct? Replacement public static String showSql(Configuration configuration, BoundSql boundSql) { // Get parameters Object parameterObject = (); List<ParameterMapping> parameterMappings = (); // Multiple spaces in sql statements are replaced by one space instead String sql = ().replaceAll("[\\s]+", " "); if ((parameterMappings) && parameterObject != null) { // Get the type processor registrant. The function of the type processor is to convert Java type and database type TypeHandlerRegistry typeHandlerRegistry = (); // If the corresponding type can be found according to (), replace if ((())) { sql = ("\\?", (getParameterValue(parameterObject))); } else { // MetaObject mainly encapsulates originalObject object, provides get and set methods to obtain and set to obtain and set attribute values of originalObject, and mainly supports operations on three types of objects: JavaBean, Collection, and Map. MetaObject metaObject = (parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = (); if ((propertyName)) { Object obj = (propertyName); sql = ("\\?", (getParameterValue(obj))); } else if ((propertyName)) { // This branch is dynamic sql Object obj = (propertyName); sql = ("\\?", (getParameterValue(obj))); } else { // Print out missing, remind the parameter to be missing and prevent misalignment sql = ("\\?", "Missing"); } } } } return sql; } @Override public Object intercept(Invocation invocation) throws Throwable { try { // Get a select/update/insert/delete node in xml, which is a SQL statement MappedStatement mappedStatement = (MappedStatement) ()[0]; Object parameter = null; // Get parameters, if statement is true, indicating that the SQL statement has parameters, and the parameter format is map form if (().length > 1) { parameter = ()[1]; ("parameter = " + parameter); } String sqlId = (); // Get the id of the node, that is, the id of the sql statement ("sqlId = " + sqlId); BoundSql boundSql = (parameter); // BoundSql is the SQL class that encapsulates myBatis final generated Configuration configuration = (); // Get the node configuration String sql = showSql(configuration, boundSql); // Get the final sql statement ().sql(sql); ("sql = " + sql); } catch (Exception e) { (); } // After executing the above task, do not change the original SQL execution process return (); } }
Add Mybatis interceptor to the configuration:
@Configuration public class SqlMonitorConfiguration { @Autowired SqlSessionFactory sqlSessionFactory; @Bean @DependsOn("sqlSessionFactory") public MybatisInterceptor mybatisInterceptor() { MybatisInterceptor mybatisInterceptor = new MybatisInterceptor(); ().addInterceptor(mybatisInterceptor); return mybatisInterceptor; } }
ThreadLocalUtil:
/** * Due to operation and maintenance requirements, the database is abnormal and the SQL statement is output, and the headLocal is introduced. * Used to store SQL statements when writing databases asynchronous multithreads to keep thread-safe * <p> * Remember to finally reset in order to prevent memory leaks * * @author lightonyang * @date 2022/10/31 */ public class TheadLocalUtil { private static final ThreadLocal<TheadLocalUtil> LOCAL = (TheadLocalUtil::new); private String sql; private TheadLocalUtil() { } public static TheadLocalUtil instance() { return (); } public TheadLocalUtil reset() { sql = null; (); return this; } /*For chain addition*/ public TheadLocalUtil sql(String sql) { = sql; return this; } public String getSql() { return sql; } public void setSql(String sql) { = sql; } }
Log configuration (print exception logs to separate log files):
<Appenders> <RollingFile name="mysqlFailFixedTimeFileAppender" fileName="${sys:}/" filePattern="${sys:}/.%d{yyyy-MM-dd_HH}-%"> <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss,SSS} %-5p %c{1}(%F:%L) - %m%n"/> <Policies> <TimeBasedTriggeringPolicy/> <SizeBasedTriggeringPolicy size="200 MB"/> </Policies> <DefaultRolloverStrategy max="24"/> </RollingFile> </Appenders> <Loggers> <AsyncLogger name="mysqlFail" level="info" additivity="false" includeLocation="true"> <AppenderRef ref="mysqlFailFixedTimeFileAppender"/> </AsyncLogger> </Loggers>
Log use (Catch exception in Impl where the operation data is operated, take out the SQL statement from ThreadLocal to print the log):
private static final Logger MYSQL_FAIL_LOG = ("mysqlFail"); try{ ··· }catch{ MYSQL_FAIL_LOG.info(().getSql(sql);); }finally{ (); }
This implementation method has also been tested and tested, and there is no problem of SQL statement coverage under multi-threading, no memory leaks or performance problems, meeting the needs.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.