快捷導(dǎo)航

MybatisPlus?自定義插件實(shí)現(xiàn)攔截SQL修改功能(實(shí)例詳解)

更新時(shí)間：2023年11月15日 09:52:01 作者：愛碼猿

這篇文章主要介紹了MybatisPlus?自定義插件實(shí)現(xiàn)攔截SQL修改功能,本文通過實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧

最近項(xiàng)目內(nèi)使用MybatisPlus整合Phoenix實(shí)現(xiàn)對(duì)HBase進(jìn)行操作,但是Phoenix的sql語法和MySQL不太一樣,導(dǎo)致得在列上加@TableField申明列簇名稱和列名稱,不太友好,所以自己寫了個(gè)插件攔截sql并進(jìn)行修改

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;
/**
 * @author guojunwang
 * @date 2021-11-29 17:06
 */
public class PhoenixMPPlugin extends JsqlParserSupport implements InnerInterceptor {
    /**
     * 查詢時(shí)處理邏輯
     */
    @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后執(zhí)行
        mpBs.sql(parserSingle(mpBs.sql(), null));
    }
    /**
     * 增刪改時(shí) 處理邏輯
     */
    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
        MappedStatement ms = mpSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();
        //增刪改調(diào)用 JSqlParser工具修改sql后執(zhí)行
        if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
            PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
            mpBs.sql(parserMulti(mpBs.sql(), null));
        }
    }
    /**
     * 以處理查詢sql為例,增刪改的實(shí)現(xiàn)可根據(jù)自己的業(yè)務(wù)實(shí)現(xiàn)
     */
    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {
        //此處處理select邏輯 將字符串拼接上 雙引號(hào)
        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( "新增前調(diào)用,可修改sql" );
    }
    @Override
    protected void processDelete(Delete delete, int index, String sql, Object obj) {
        System.out.println( "刪除前調(diào)用,可修改sql" );
    }
    @Override
    protected void processUpdate(Update update, int index, String sql, Object obj) {
        System.out.println("修改調(diào)用,可修改sql");
    }
//---------------以下為處理sql操作,根據(jù)自己業(yè)務(wù)功能完善
    /**
     * 處理查詢字段
     */
    private List<SelectItem> disposeSelectColumn(List<SelectItem> 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() ) );
        }
        //如果是函數(shù)
        if( item.getExpression() instanceof Function){
            Function function = (Function) item.getExpression();
            return new SelectExpressionItem( reFormatFunction( function ) );
        }
         return item;
    }
    /**
     * 重新格式化 查詢語句
     * @param plainSelect 查詢語句
     * @return 格式化的查詢語句
     */
    public void reformatPlainSelect(PlainSelect plainSelect){
        //處理要查詢的字段
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        //處理查詢條件
        plainSelect.setSelectItems( disposeSelectColumn( selectItems ) );
        //處理 where 條件
        plainSelect.setWhere( disposeSelectWhere( plainSelect.getWhere() )  );
    }
    /**
     * 重新格式化列
     * @param columnExp 列
     * @param alias 列別名
     * @return 格式化的列
     */
    private Column reFormatSelectColumn( Column columnExp,Alias alias ){
        if( columnExp == null ) return columnExp;
        //表名和列簇名會(huì)在一起
        String tableAndCFName= columnExp.getTable() == null ? "" : columnExp.getTable().toString();
        //字段名
        String columnName= columnExp.getColumnName();
        //根據(jù) `.` 分隔方便處理表名和列簇名
        String[] tableAndCFInfo = tableAndCFName.split("\\.");
        // 可能會(huì)出現(xiàn)很多情況 列名  列簇.列名  表名.列簇.列名 表名.列名
        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() );
    }
    /**
     * 重新格式化查詢函數(shù)
     * @param function 函數(shù)
     * @return 格式化的函數(shù)
     */
    private Function reFormatFunction( Function function ){
        List<Expression> expressions = function.getParameters().getExpressions();
        //對(duì)于是列的參數(shù)進(jìn)行格式化
        expressions = expressions.stream().map(exp -> {
            if (exp instanceof Column) return reFormatSelectColumn((Column) exp, null);
            return exp;
        }).collect(Collectors.toList());
        //重新設(shè)置回去
        function.getParameters().setExpressions(expressions);
        return function;
    }
    /**
     * 重新格式化子查詢
     * @param subSelect 子查詢
     * @return 格式化的函數(shù)
     */
    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() );
        }
        //如果左邊表達(dá)式是列信息 格式化
        if(  binaryExpression.getLeftExpression() instanceof Column ){
            Column newColumn = reFormatSelectColumn((Column) binaryExpression.getLeftExpression(), null);
            binaryExpression.setLeftExpression( newColumn );
        }
        //如果左邊表達(dá)式是 子查詢 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 );
        }
        //如果右邊表達(dá)式是 子查詢 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 = "\"";
        //如果已經(jīng)有前綴了直接返回
        if( str.contains(PREFIX) ) return str;
        //拼接前綴和后綴
        return new StringBuilder().append(PREFIX).append(str).append(SUFFIX).toString();
    }
}

使用: 編寫配置類配置MybatisPlus并設(shè)置插件

@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);
    }
}

到此這篇關(guān)于MybatisPlus 自定義插件實(shí)現(xiàn)攔截SQL修改功能的文章就介紹到這了,更多相關(guān)MybatisPlus 攔截SQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: