MybatisPlus 自定义插件实现拦截SQL修改功能

最近项目内使用MybatisPlus整合Phoenix实现对Hbase进行操作,但是Phoenix的sql语法和MySQL不太一样,导致得在列上加@TableField申明列簇名称和列名称,不太友好,所以自己写了个插件拦截sql并进行修改

package org.gjw.config;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Binaryexpression;
import net.sf.jsqlparser.expression.expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;


public class PhoenixMPPlugin extends JsqlParserSupport implements InnerInterceptor {

    
    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
        //通过 JSqlParser工具修改查询sql后执行
        mpBs.sql(parserSingle(mpBs.sql(), null));
    }


    
    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
        MappedStatement ms = mpSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();

        //增删改调用 JSqlParser工具修改sql后执行
        if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
            PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
            mpBs.sql(parserMulti(mpBs.sql(), null));
        }

    }


    
    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {

        //此处处理select逻辑 将字符串拼接上 双引号
        SelectBody selectBody = select.getSelectBody();

        if(selectBody instanceof PlainSelect) reformatPlainSelect((PlainSelect) selectBody);

    }



    @Override
    protected void processInsert(Insert insert, int index, String sql, Object obj) {
        System.out.println( "新增前调用,可修改sql" );
    }

    @Override
    protected void processDelete(Delete delete, int index, String sql, Object obj) {
        System.out.println( "删除前调用,可修改sql" );
    }

    @Override
    protected void processUpdate(Update update, int index, String sql, Object obj) {
        System.out.println("修改调用,可修改sql");
    }





    

//---------------以下为处理sql操作,根据自己业务功能完善


    
    private List disposeSelectColumn(List selectItems){
        return selectItems.stream().map( this::resetSelectItem ).collect(Collectors.toList());
    }


    private SelectItem resetSelectItem( SelectItem selectItem ){
        //如果不符合直接返回
        if( !(selectItem instanceof SelectexpressionItem) ) return selectItem;


        SelectexpressionItem item = (SelectexpressionItem)selectItem;

        //如果是列
        if( item.getexpression() instanceof Column ){
            Column columnExp = (Column)item.getexpression();
            return new SelectexpressionItem( reFormatSelectColumn( columnExp,item.getAlias() ) );
        }

        //如果是函数
        if( item.getexpression() instanceof Function){
            Function function = (Function) item.getexpression();
            return new SelectexpressionItem( reFormatFunction( function ) );
        }


         return item;
    }


    
    public void reformatPlainSelect(PlainSelect plainSelect){

        //处理要查询的字段
        List selectItems = plainSelect.getSelectItems();

        //处理查询条件
        plainSelect.setSelectItems( disposeSelectColumn( selectItems ) );

        //处理 where 条件
        plainSelect.setWhere( disposeSelectWhere( plainSelect.getWhere() )  );

    }


    
    private Column reFormatSelectColumn( Column columnExp,Alias alias ){
        if( columnExp == null ) return columnExp;

        //表名和列簇名会在一起
        String tableAndCFName= columnExp.getTable() == null ? "" : columnExp.getTable().toString();
        //字段名
        String columnName= columnExp.getColumnName();


        //根据 `.` 分隔方便处理表名和列簇名
        String[] tableAndCFInfo = tableAndCFName.split("\.");

        // 可能会出现很多情况 列名  列簇.列名  表名.列簇.列名 表名.列名
        String tableName = tableAndCFInfo[0];
        String cf        = tableAndCFInfo[tableAndCFInfo.length - 1];

        //如果表名和字段名相等,只有3种情况: 列名  表名.列名  列簇.列名
        if( StrUtil.equals(tableName,cf)  && StrUtil.isNotBlank(tableName) ){
            //判断前缀是表名还是列名  要求列簇必须全大写 表名不能全大写
            //如果全大写这是列簇名
            if( StrUtil.equals(cf.toUpperCase(),cf) ) {
                tableName = "";
            }else cf = ""; //否则是表名
        }

        StringBuilder finalName = new StringBuilder();

        //如果表名不为空 拼接表名
        if( StrUtil.isNotBlank( tableName ) )   finalName.append( tableName ).append( "." );
        //如果列簇名不为空 拼接列簇名
        if( StrUtil.isNotBlank( cf ) ) finalName.append( appendPrefixAndSuffix(cf) ).append(".");
        //拼接字段名
        finalName.append( appendPrefixAndSuffix(columnName) );
        //拼接别名: as xxx
        if( alias !=null ) finalName.append(" ").append( alias.getName() );


        //重新格式化列名 封装返回
        return new Column( finalName.toString() );
    }

    
    private Function reFormatFunction( Function function ){

        List expressions = function.getParameters().getexpressions();

        //对于是列的参数进行格式化
        expressions = expressions.stream().map(exp -> {
            if (exp instanceof Column) return reFormatSelectColumn((Column) exp, null);
            return exp;
        }).collect(Collectors.toList());

        //重新设置回去
        function.getParameters().setexpressions(expressions);

        return function;
    }


    
    private SubSelect reFormatSubSelect( SubSelect subSelect ){

        if( subSelect.getSelectBody() instanceof PlainSelect ){
            reformatPlainSelect( (PlainSelect)subSelect.getSelectBody() );
        }

        return subSelect;
    }



    public expression disposeSelectWhere(expression expression){

        if( !(expression instanceof Binaryexpression) ) return expression;

        Binaryexpression binaryexpression =(Binaryexpression)expression;

        //如果左边还是多条件的
        if( binaryexpression.getLeftexpression() instanceof Binaryexpression){
            disposeSelectWhere( binaryexpression.getLeftexpression() );
        }

        //如果右边还是多条件的
        if( binaryexpression.getRightexpression() instanceof Binaryexpression){
            disposeSelectWhere( binaryexpression.getRightexpression() );
        }


        //如果左边表达式是列信息 格式化
        if(  binaryexpression.getLeftexpression() instanceof Column ){
            Column newColumn = reFormatSelectColumn((Column) binaryexpression.getLeftexpression(), null);
            binaryexpression.setLeftexpression( newColumn );
        }

        //如果左边表达式是 子查询 processPlainSelect
        if(binaryexpression.getLeftexpression() instanceof SubSelect){
            SubSelect subSelect = (SubSelect)binaryexpression.getLeftexpression();
            if( subSelect.getSelectBody() instanceof PlainSelect ){
                reformatPlainSelect( (PlainSelect)subSelect.getSelectBody() );
            }
        }


        //如果右边是列信息 格式化
        if(  binaryexpression.getRightexpression() instanceof Column ){
            Column newColumn = reFormatSelectColumn((Column) binaryexpression.getLeftexpression(), null);
            binaryexpression.setRightexpression( newColumn );
        }

        //如果右边表达式是 子查询 processPlainSelect
        if( binaryexpression.getRightexpression() instanceof SubSelect){
            SubSelect subSelect = (SubSelect)binaryexpression.getRightexpression();
            reFormatSubSelect( subSelect );
        }

        return binaryexpression;
    }


    private String appendPrefixAndSuffix(String str){

        final String PREFIX = """;
        final String SUFFIX = """;

        //如果已经有前缀了直接返回
        if( str.contains(PREFIX) ) return str;

        //拼接前缀和后缀
        return new StringBuilder().append(PREFIX).append(str).append(SUFFIX).toString();
    }
}

使用: 编写配置类配置MybatisPlus并设置插件

@MapperScan(value = "org.gjw.mapper.phoenix",sqlSessionTemplateRef = "phoenixSqlSessionTemplate",sqlSessionFactoryRef = "phoenixSqlSessionFactory")
@Configuration
public class PhoenixConfig {


    @Bean
    @ConfigurationProperties("spring.datasource.phoenix")
    public DataSource phoenixDataSource(){
        return new HikariDataSource();
    }

    @Bean
    public SqlSessionFactory phoenixSqlSessionFactory( @Qualifier("phoenixDataSource") @Autowired DataSource phoenixDataSource) throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource( phoenixDataSource() );

        sqlSessionFactoryBean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources("classpath*:/phoenixMapper*.xml"));

        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor( new PhoenixMPPlugin() );

        sqlSessionFactoryBean.setPlugins( interceptor );

        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        mybatisConfiguration.setMapUnderscoreToCamelCase(true);
        mybatisConfiguration.setLogImpl(StdOutImpl.class);

        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        return sqlSessionFactoryBean.getObject();
    }


    @Bean
    public SqlSessionTemplate phoenixSqlSessionTemplate( @Qualifier("phoenixSqlSessionFactory") @Autowired SqlSessionFactory phoenixSqlSessionFactory){
        return new SqlSessionTemplate( phoenixSqlSessionFactory );
    }

    @Bean
    public DataSourceTransactionManager phoenixDataSourceTransactionManager(@Qualifier("phoenixDataSource") @Autowired DataSource phoenixDataSource){
        return new DataSourceTransactionManager(phoenixDataSource);
    }


}