索取营销诊断书
021-80394720QQ
    您的位置：上海毫米网络优化公司 > 网站优化分享 >
    相关推荐recommended
    JSqlParser的使用
    作者：mmseoamin日期：2024-04-30

    简介

            JSqlParse是一款很精简的sql解析工具，它可以将常用的sql文本解析成具有层级结构的语法树，我们可以针对解析后的节点进行处理(增加、移除、修改等操作),从而生成符合我们业务要求的sql，比如添加过滤条件等等

         JSqlParse采用访问者模式

    项目简介

            JSqlParser的使用,第1张

    项目结构非常简单，从截图上看就5个包。如果对源码感兴趣的可以直接从github上下载源码包调试。其中expression包包含了所有的sql表达式的抽象对象：

    JSqlParser的使用,第2张

    statement包含了所有sql语句的类型，比如：增删改查，ddl语句,rollback语句等等

    JSqlParser的使用,第3张

    schema包是对数据库基本单元的抽象：服务器、数据库、表、列等等

    JSqlParser的使用,第4张

    parser包是整个解析的核心逻辑，感兴趣的可以自行源码调试

    JSqlParser的使用,第5张

    使用示例

            上面已经做了关于该解析工具的简单介绍，对于工具类，最重要的使用。以下举例关于增、删、改、查的sql语句中，均增加一列为例介绍该工具的简单使用

    依赖引入

    
    com.github.jsqlparser
    jsqlparser
    4.5

    新增add

    原始sql：insert into t_user_info(id,user_name,address) values('123','zhangsan','龙华')

    期望在执行该sql时，能增加一列STATUS作为插入

    都是一些api的运用，相关代码如下：

    package com.lyc.boot.client.test.insert;
import com.lyc.boot.client.test.insert.visitor.InsertStatementVisitor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.RowConstructor;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import java.util.List;
import static com.lyc.boot.client.test.CommonUtil.printStatement;
import static com.lyc.boot.client.test.CommonUtil.printTableName;
@Slf4j
public class InsertCommonTest {
    private static final String INSERT_COMMON = "insert into t_user_info(id,user_name,address) values('123','zhangsan','龙华')";
    public static void main(String[] args) throws JSQLParserException {
        useCommonAddColumn();
//        useVisitorAddColumn();
    }
    private static void useCommonAddColumn() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(INSERT_COMMON);
        printStatement(statement);
        if (statement instanceof Insert) {
            Insert insert = (Insert)statement;
            printTableName(insert.getTable());
            List columns = insert.getColumns();
            columns.add(new Column("STATUS"));
            Select select = insert.getSelect();
            SelectBody selectBody = select.getSelectBody();
            if (selectBody instanceof SetOperationList) {
                SetOperationList operationList = (SetOperationList)selectBody;
                List selects = operationList.getSelects();
                for (SelectBody body : selects) {
                    if (body instanceof ValuesStatement) {
                        ValuesStatement valuesStatement = (ValuesStatement)body;
                        ItemsList itemsList = valuesStatement.getExpressions();
                        if(itemsList instanceof ExpressionList) {
                            ExpressionList expressionList = (ExpressionList)itemsList;
                            List expressions = expressionList.getExpressions();
                            for (Expression expression : expressions) {
                                if(expression instanceof RowConstructor) {
                                    RowConstructor rowConstructor = (RowConstructor)expression;
                                    ExpressionList exprList = rowConstructor.getExprList();
                                    List rowConstructorExList = exprList.getExpressions();
                                    rowConstructorExList.add(new StringValue("0"));
                                }
                            }
                        }
                    }
                }
            }
        }
        printStatement(statement);
    }
    /**
     * 使用访问者方式增加insert的column
     *
     * @throws JSQLParserException
     */
    private static void useVisitorAddColumn() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(INSERT_COMMON);
        printStatement(statement);
        statement.accept(new InsertStatementVisitor());
        printStatement(statement);
    }
}

    package com.lyc.boot.client.test.insert.visitor;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import java.util.List;
import static com.lyc.boot.client.test.CommonUtil.printTableName;
public class InsertStatementVisitor extends StatementVisitorAdapter {
    @Override
    public void visit(Insert insert) {
        printTableName(insert.getTable());
        List columns = insert.getColumns();
        columns.add(new Column("status"));
        Select select = insert.getSelect();
        SelectBody selectBody = select.getSelectBody();
        selectBody.accept(new InsertSelectVisitor());
    }
}

    package com.lyc.boot.client.test.insert.visitor;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectVisitorAdapter;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import java.util.List;
public class InsertSelectVisitor extends SelectVisitorAdapter {
    @Override
    public void visit(SetOperationList setOpList) {
        List selects = setOpList.getSelects();
        for (SelectBody body : selects) {
            body.accept(this);
        }
    }
    @Override
    public void visit(ValuesStatement valuesStatement) {
        ItemsList itemsList = valuesStatement.getExpressions();
        itemsList.accept(new InsertItemsListVisitor());
    }
}

    package com.lyc.boot.client.test.insert.visitor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.util.validation.validator.ItemsListValidator;
import java.util.List;
public class InsertItemsListVisitor extends ItemsListValidator {
    @Override
    public void visit(ExpressionList expressionList) {
        List expressions = expressionList.getExpressions();
        for (Expression expression : expressions) {
            expression.accept(new InsertExpressionVisitor());
        }
    }
}

    package com.lyc.boot.client.test.insert.visitor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitorAdapter;
import net.sf.jsqlparser.expression.RowConstructor;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import java.util.List;
public class InsertExpressionVisitor extends ExpressionVisitorAdapter {
    @Override
    public void visit(RowConstructor rowConstructor) {
        ExpressionList exprList = rowConstructor.getExprList();
        List expressions = exprList.getExpressions();
        expressions.add(new StringValue("0"));
    }
}

          以上是关于新增sql增加一列作为插入的简单运用，其中有通过类型判断处理和通过访问者模式处理(基于java多态实现)，最终打印的结果如下：

    JSqlParser的使用,第6张

    删除delete

    原sql：delete from t_user_info where user_name = ? and addres = ?

    期望在删除时增加过滤条件STATUS='0'

    相关代码如下：

    package com.lyc.boot.client.test.delete;
import com.lyc.boot.client.test.delete.visitor.DeleteStatementVisitor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import static com.lyc.boot.client.test.CommonUtil.printStatement;
@Slf4j
public class DeleteCommonTest {
    private static final String DELETE_COMMON = "delete from t_user_info where user_name = ? and addres = ?";
    public static void main(String[] args) throws JSQLParserException {
//        commonAddColumn();
        visitorAddColumn();
    }
    private static void visitorAddColumn() throws JSQLParserException{
        Statement statement = CCJSqlParserUtil.parse(DELETE_COMMON);
        printStatement(statement);
        statement.accept(new DeleteStatementVisitor());
        printStatement(statement);
    }
    private static void commonAddColumn() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(DELETE_COMMON);
        printStatement(statement);
        if(statement instanceof Delete) {
            Delete delete = (Delete)statement;
            DeleteStatementVisitor.addColumn(delete);
        }
        printStatement(statement);
    }
}


    package com.lyc.boot.client.test.delete.visitor;
import com.lyc.boot.client.test.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.delete.Delete;
import java.util.Objects;
@Slf4j
public class DeleteStatementVisitor extends StatementVisitorAdapter {
    @Override
    public void visit(Delete delete) {
        addColumn(delete);
    }
    public static void addColumn(Delete delete) {
        CommonUtil.printTableName(delete.getTable());
        Expression where = delete.getWhere();
        Parenthesis parenthesis = new Parenthesis(new EqualsTo(new Column("STATUS"), new StringValue("1")));
        if (Objects.isNull(where)) {
            delete.setWhere(parenthesis);
        } else {
            delete.setWhere(new AndExpression(where,parenthesis));
        }
    }
}

    执行结果如下图：

    JSqlParser的使用,第7张

    修改update

    原sql为：update t_user_info set user_name = ?,address = ? where id = ? and score = ?

    期望在修改时set增加STATUS = ? where条件增加STATUS = '1'

    package com.lyc.boot.client.test.update;
import com.lyc.boot.client.test.update.visitor.UpdateStatementVisitor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import java.util.ArrayList;
import java.util.Objects;
import static com.lyc.boot.client.test.CommonUtil.printStatement;
/**
 * update语句修改
 *
 *
 */
@Slf4j
public class UpdateCommonTest {
    private static final String COMMON_UPDATE = "update t_user_info set user_name = ?,address = ? where id = ? and score = ?";
    public static void main(String[] args) throws JSQLParserException {
//        commonUpdateAddColumn();
        visitorAddColumn();
    }
    private static void visitorAddColumn() throws JSQLParserException{
        Statement statement = CCJSqlParserUtil.parse(COMMON_UPDATE);
        printStatement(statement);
        statement.accept(new UpdateStatementVisitor());
        printStatement(statement);
    }
    private static void commonUpdateAddColumn() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(COMMON_UPDATE);
        printStatement(statement);
        if(statement instanceof Update) {
            Update update = (Update)statement;
            Table table = update.getTable();
            ArrayList updateSets = update.getUpdateSets();
            Column column = new Column("STATUS");
            StringValue stringValue = new StringValue("?");
            JdbcParameter jdbcParameter = new JdbcParameter();
            UpdateSet updateSet = new UpdateSet(column,jdbcParameter);
            updateSets.add(updateSet);
            Expression whereExpression = update.getWhere();
            EqualsTo equalsTo = new EqualsTo(new Column("STATUS"), new StringValue("1"));
            Parenthesis parenthesis = new Parenthesis(equalsTo);
            if (Objects.isNull(whereExpression)) {
                update.setWhere(parenthesis);
            } else {
                update.setWhere(new AndExpression(whereExpression,parenthesis));
            }
        }
        printStatement(statement);
    }
}

    package com.lyc.boot.client.test.update.visitor;
import com.lyc.boot.client.test.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import java.util.ArrayList;
import java.util.Objects;
@Slf4j
public class UpdateStatementVisitor extends StatementVisitorAdapter {
    @Override
    public void visit(Update update) {
        CommonUtil.printTableName(update.getTable());
        ArrayList updateSets = update.getUpdateSets();
        UpdateSet statusUpdateSet = new UpdateSet(new Column("STATUS"), new JdbcParameter());
        updateSets.add(statusUpdateSet);
        Expression where = update.getWhere();
        Parenthesis parenthesis = new Parenthesis(new EqualsTo(new Column("STATUS"), new StringValue("1")));
        if (Objects.isNull(where)) {
            update.setWhere(parenthesis);
        } else {
            update.setWhere(new AndExpression(where,parenthesis));
        }
    }
}

    执行结果如下图所示：

    JSqlParser的使用,第8张

    查询select

    原sql如下：select id as id,user_name as userName,address as address from t_user_info where id = ? and user_name = ? order by create_time desc

    期望在查询时增加where的过滤条件STATUS = '1'

    package com.lyc.boot.client.test.select;
import com.lyc.boot.client.test.select.visitor.SelectSelectVisitor;
import com.lyc.boot.client.test.select.visitor.SelectStatementVisitor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import static com.lyc.boot.client.test.CommonUtil.printStatement;
@Slf4j
/**
 * 给查询条件添加更多的过滤条件
 *
 * and status = '1'
 */
public class SelectCommonTest {
    private static final String SELECT_COMMON = "select id as id,user_name as userName,address as address from t_user_info where id = ? and user_name = ? order by create_time desc";
    public static void main(String[] args) throws JSQLParserException {
//        commonSelectAddWhere();
        visitorSelectAddWhere();
    }
    private static void visitorSelectAddWhere() throws JSQLParserException{
        Statement statement = CCJSqlParserUtil.parse(SELECT_COMMON);
        printStatement(statement);
        statement.accept(new SelectStatementVisitor());
        printStatement(statement);
    }
    private static void commonSelectAddWhere() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(SELECT_COMMON);
        printStatement(statement);
        if (statement instanceof Select) {
            Select select = (Select)statement;
            SelectBody selectBody = select.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect)selectBody;
                SelectSelectVisitor.setWhereExpression(plainSelect);
            }
        }
        printStatement(statement);
    }
}

    package com.lyc.boot.client.test.select.visitor;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
public class SelectStatementVisitor extends StatementVisitorAdapter {
    @Override
    public void visit(Select select) {
        SelectBody selectBody = select.getSelectBody();
        selectBody.accept(new SelectSelectVisitor());
    }
}

    package com.lyc.boot.client.test.select.visitor;
import com.lyc.boot.client.test.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectVisitorAdapter;
import java.util.List;
import java.util.Objects;
@Slf4j
public class SelectSelectVisitor extends SelectVisitorAdapter {
    @Override
    public void visit(PlainSelect plainSelect) {
        setWhereExpression(plainSelect);
    }
    public static void setWhereExpression(PlainSelect plainSelect) {
        Expression where = plainSelect.getWhere();
        EqualsTo equalsTo = new EqualsTo(new Column("STATUS"), new StringValue("1"));
        Parenthesis parenthesis = new Parenthesis(equalsTo);
        if (Objects.isNull(where)) {
            plainSelect.setWhere(parenthesis);
        } else {
            AndExpression andExpression = new AndExpression(where, parenthesis);
            plainSelect.setWhere(andExpression);
        }
    }
}

    执行结果如下图：

    JSqlParser的使用,第9张

    扩展简析

    jsqlParser的实际之一就是在mybaits-plus中的各种插件，比如：多租户插件com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor

    该插件的作用是：在执行sql时在where条件处增加了过滤条件(默认是tenant_id = ?,具体的字段可以自己配置实现)

    当配置了MybatisPlusInterceptor，并且添加了TenantLineInnerInterceptor时，在执行sql时会被该拦截器拦截，具体的源码流程如下：

    JSqlParser的使用,第10张

    当执行查询语句时，sql会被MybatisPlusInterceptor插件拦截，插件调TenantLineInnerInterceptor的beforeQuery方法触发

    其中BaseMultiTableInnerInterceptor是JsqlParserSupport的子类，提供了模板方法用于修改sql

    图上，生成的sql由com.baomidou.mybatisplus.extension.parser.JsqlParserSupport#parserSingle方法决定

    JSqlParser的使用,第11张

    最终执行sql解析完成添加过滤条件的操作：

    JSqlParser的使用,第12张

    在TenantLineInnerInterceptor插件中，最终是在where结尾出添加了(默认)tenant_id = xxxx的过滤条件，完成多租户数据隔离处理的。具体的源码逻辑可以调试根据

     

    上一篇：高级Java开发工程师手把手教你用AI人工智能帮你写JAVA代码实际案例一全网唯一

    下一篇：Windows中网络适配器里的VMnet1和VMnet8出现感叹号怎么解决？

    网站优化运营
    整站seo优化
    网络营销运营
    成功案例
    品牌创意网站
    营销型网站
    小程序
    微信小程序
    微信公众号
    百度熊掌号
    网站优化分享
    关于我们
    公司概况
    公司新闻
    公司文化
    结构体系
    企业资质
    加入我们
    关注我们
    扫描二维码
    获取更多信息
    精彩一手掌握
    Copyright © 2014-2019 seo,上海网站优化_网络制作_网站seo排名_网站建设推广_上海网络公司 版权所有 沪ICP备19022315号