java中JSqlParser的使用

更新時(shí)間：2024年07月01日 11:20:45 作者：昱宸星光

JSqlParse是一款很精簡(jiǎn)的sql解析工具,它可以將常用的sql文本解析成具有層級(jí)結(jié)構(gòu)的語(yǔ)法樹(shù),本文主要介紹了java中JSqlParser的使用,具有一定的參考價(jià)值,感興趣的可以了解一下

簡(jiǎn)介

JSqlParse是一款很精簡(jiǎn)的sql解析工具，它可以將常用的sql文本解析成具有層級(jí)結(jié)構(gòu)的語(yǔ)法樹(shù)，我們可以針對(duì)解析后的節(jié)點(diǎn)進(jìn)行處理(增加、移除、修改等操作),從而生成符合我們業(yè)務(wù)要求的sql，比如添加過(guò)濾條件等等

JSqlParse采用訪問(wèn)者模式

項(xiàng)目簡(jiǎn)介

項(xiàng)目結(jié)構(gòu)非常簡(jiǎn)單，從截圖上看就5個(gè)包。如果對(duì)源碼感興趣的可以直接從github上下載源碼包調(diào)試。其中expression包包含了所有的sql表達(dá)式的抽象對(duì)象：

statement包含了所有sql語(yǔ)句的類型，比如：增刪改查，ddl語(yǔ)句,rollback語(yǔ)句等等

schema包是對(duì)數(shù)據(jù)庫(kù)基本單元的抽象：服務(wù)器、數(shù)據(jù)庫(kù)、表、列等等

parser包是整個(gè)解析的核心邏輯，感興趣的可以自行源碼調(diào)試

使用示例

上面已經(jīng)做了關(guān)于該解析工具的簡(jiǎn)單介紹，對(duì)于工具類，最重要的使用。以下舉例關(guān)于增、刪、改、查的sql語(yǔ)句中，均增加一列為例介紹該工具的簡(jiǎn)單使用

依賴引入

<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','龍華')

期望在執(zhí)行該sql時(shí)，能增加一列STATUS作為插入

都是一些api的運(yùn)用，相關(guān)代碼如下：

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

    /**
     * 使用訪問(wèn)者方式增加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"));
    }
}

以上是關(guān)于新增sql增加一列作為插入的簡(jiǎn)單運(yùn)用，其中有通過(guò)類型判斷處理和通過(guò)訪問(wèn)者模式處理(基于java多態(tài)實(shí)現(xiàn))，最終打印的結(jié)果如下：

刪除delete

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

期望在刪除時(shí)增加過(guò)濾條件STATUS='0'

相關(guān)代碼如下：

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

執(zhí)行結(jié)果如下圖：

修改update

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

期望在修改時(shí)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語(yǔ)句修改
 *
 *
 */
@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<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;

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

執(zhí)行結(jié)果如下圖所示：

查詢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

期望在查詢時(shí)增加where的過(guò)濾條件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
/**
 * 給查詢條件添加更多的過(guò)濾條件
 *
 * 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);
        }
    }
}

執(zhí)行結(jié)果如下圖：