发布时间：2025-06-24 18:36:56  作者：北方职教升学中心  阅读量：481

表、数据库、修改等操作),从而生成符合我们业务要求的sql，比如添加过滤条件等等

     JSqlParse采用访问者模式

项目简介

        

项目结构非常简单，从截图上看就5个包。查的sql语句中，均增加一列为例介绍该工具的简单使用

依赖引入

<dependency>    <groupId>com.github.jsqlparser</groupId>    <artifactId>jsqlparser</artifactId>    <version>4.5</version></dependency>

新增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;@Slf4jpublic 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<Column> 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<SelectBody> 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<Expression> expressions = expressionList.getExpressions();                            for (Expression expression : expressions) {                                if(expression instanceof RowConstructor) {                                    RowConstructor rowConstructor = (RowConstructor)expression;                                    ExpressionList exprList = rowConstructor.getExprList();                                    List<Expression> 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<Column> 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<SelectBody> 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<Expression> 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<Expression> expressions = exprList.getExpressions();        expressions.add(new StringValue("0"));    }}

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

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

执行结果如下图：

修改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语句修改 * * */@Slf4jpublic 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<UpdateSet> 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;@Slf4jpublic class UpdateStatementVisitor extends StatementVisitorAdapter {    @Override    public void visit(Update update) {        CommonUtil.printTableName(update.getTable());        ArrayList<UpdateSet> 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));        }    }}

执行结果如下图所示：

查询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;@Slf4jpublic 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的实际之一就是在mybaits-plus中的各种插件，比如：多租户插件com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor

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

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

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

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

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

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

在TenantLineInnerInterceptor插件中，最终是在where结尾出添加了(默认)tenant_id = xxxx的过滤条件，完成多租户数据隔离处理的。

其中expression包包含了所有的sql表达式的抽象对象：

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

schema包是对数据库基本单元的抽象：服务器、改、移除、删、如果对源码感兴趣的可以直接从github上下载源码包调试。以下举例关于增、列等等

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

使用示例

        上面已经做了关于该解析工具的简单介绍，对于工具类，最重要的使用。