SpringBoot integrates mybatis interceptor to modify table name

Time:2022-11-24

background

The company’s framework is developed based on mysql5.7. Recently, an application project was deployed on a linux system, using mysql8.0. Case sensitivity was not enabled during installation, and the customer was not allowed to reinstall the mysql environment, resulting in some frameworks Where the table name is capitalized in the code and business code, the table name cannot be found, so it needs to be handled uniformly

Custom SQLAST adapter

Customize ASTVisitorAdapter to modify the table name

public class MySqlExportTableAliasVisitor extends MySqlASTVisitorAdapter {
    @Override
    public boolean visit(SQLExprTableSource x) {
        SystemConfig systemConfig = SpringBootBeanUtil.getBean(SystemConfig.class);
        if(systemConfig.getDbTableNameProxy().equals("lowcase")){
            x.setExpr(x.getTableName().toLowerCase());
        }else if(systemConfig.getDbTableNameProxy().equals("upcase")){
            x.setExpr(x.getTableName().toUpperCase());
        }
        return true;
    }
}

Customize Mybatis interceptor

Obtain the sql statement through BoundSql, use Druid’s SQLUtils to perform structural analysis on the sql statement, and then re-copy the sql statement after the table name is modified

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class SQLTableNameHandleInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        DataBaseInfoUtil dataBaseInfoUtil = SpringBootBeanUtil.getBean(DataBaseInfoUtil.class);
        if(dataBaseInfoUtil.checkOpenTableNameHandle()){
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");

            String sql = boundSql.getSql();
            List<SQLStatement> stmtList  = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
            MySqlExportTableAliasVisitor visitor = new MySqlExportTableAliasVisitor();
            for (SQLStatement stmt : stmtList) {
                stmt.accept(visitor);
            }
            String handleSQL = SQLUtils.toSQLString(stmtList, JdbcConstants.MYSQL);

            metaStatementHandler.setValue("delegate.boundSql.sql", handleSQL);
        }
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties properties) {

    }

}

Register a custom Myabits interceptor

@Configuration
public class FrameMyBatisPluginConfig {
    @Bean
    @Conditional({SQLTableNameHandleInterceptorCondition.class})
    public String SQLTableNameHandleInterceptor(SqlSessionFactory sqlSessionFactory) {
        //Instantiate the plugin
        SQLTableNameHandleInterceptor sqlTableNameHandleInterceptor = new SQLTableNameHandleInterceptor();
        //Create attribute value
        Properties properties = new Properties();
        properties.setProperty("prop1","value1");
        //Set the attribute value to the plugin
        sqlTableNameHandleInterceptor.setProperties(properties);
        //Add the plugin to the SqlSessionFactory factory
        sqlSessionFactory.getConfiguration().addInterceptor(sqlTableNameHandleInterceptor);
        return "interceptor";
    }
}