java中JSqlParser的使用
簡介
JSqlParse是一款很精簡的sql解析工具,它可以將常用的sql文本解析成具有層級結(jié)構(gòu)的語法樹,我們可以針對解析后的節(jié)點(diǎn)進(jìn)行處理(增加、移除、修改等操作),從而生成符合我們業(yè)務(wù)要求的sql,比如添加過濾條件等等
JSqlParse采用訪問者模式
項(xiàng)目簡介
項(xiàng)目結(jié)構(gòu)非常簡單,從截圖上看就5個(gè)包。如果對源碼感興趣的可以直接從github上下載源碼包調(diào)試。其中expression包包含了所有的sql表達(dá)式的抽象對象:
statement包含了所有sql語句的類型,比如:增刪改查,ddl語句,rollback語句等等
schema包是對數(shù)據(jù)庫基本單元的抽象:服務(wù)器、數(shù)據(jù)庫、表、列等等
parser包是整個(gè)解析的核心邏輯,感興趣的可以自行源碼調(diào)試
使用示例
上面已經(jīng)做了關(guān)于該解析工具的簡單介紹,對于工具類,最重要的使用。以下舉例關(guān)于增、刪、改、查的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','龍華')
期望在執(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); } /** * 使用訪問者方式增加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增加一列作為插入的簡單運(yùn)用,其中有通過類型判斷處理和通過訪問者模式處理(基于java多態(tài)實(shí)現(xiàn)),最終打印的結(jié)果如下:
刪除delete
原sql:delete from t_user_info where user_name = ? and addres = ?
期望在刪除時(shí)增加過濾條件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語句修改 * * */ @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的過濾條件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); } } }
執(zhí)行結(jié)果如下圖:
擴(kuò)展簡析
jsqlParser的實(shí)際之一就是在mybaits-plus中的各種插件,比如:多租戶插件com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor
該插件的作用是:在執(zhí)行sql時(shí)在where條件處增加了過濾條件(默認(rèn)是tenant_id = ?,具體的字段可以自己配置實(shí)現(xiàn))
當(dāng)配置了MybatisPlusInterceptor,并且添加了TenantLineInnerInterceptor時(shí),在執(zhí)行sql時(shí)會被該攔截器攔截,具體的源碼流程如下:
當(dāng)執(zhí)行查詢語句時(shí),sql會被MybatisPlusInterceptor插件攔截,插件調(diào)TenantLineInnerInterceptor的beforeQuery方法觸發(fā)
其中BaseMultiTableInnerInterceptor是JsqlParserSupport的子類,提供了模板方法用于修改sql
圖上,生成的sql由com.baomidou.mybatisplus.extension.parser.JsqlParserSupport#parserSingle方法決定
最終執(zhí)行sql解析完成添加過濾條件的操作:
在TenantLineInnerInterceptor插件中,最終是在where結(jié)尾出添加了(默認(rèn))tenant_id = xxxx的過濾條件,完成多租戶數(shù)據(jù)隔離處理的。具體的源碼邏輯可以調(diào)試根據(jù)
到此這篇關(guān)于java中JSqlParser的使用的文章就介紹到這了,更多相關(guān)java JSqlParser使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
idea項(xiàng)目全局去掉嚴(yán)格的語法校驗(yàn)方式
這篇文章主要介紹了idea項(xiàng)目全局去掉嚴(yán)格的語法校驗(yàn)方式,具有很好的參考價(jià)值,希望對大家有所幫助。2023-04-04使用開源項(xiàng)目JAVAE2 進(jìn)行視頻格式轉(zhuǎn)換
這篇文章主要介紹了使用開源項(xiàng)目JAVAE 進(jìn)行視頻格式轉(zhuǎn)換,幫助大家更好的利用Java處理視頻,完成自身需求,感興趣的朋友可以了解下2020-11-11IDEA連接達(dá)夢數(shù)據(jù)庫的詳細(xì)配置指南
達(dá)夢數(shù)據(jù)庫(DM Database)作為國產(chǎn)關(guān)系型數(shù)據(jù)庫的代表,廣泛應(yīng)用于企業(yè)級系統(tǒng)開發(fā),本文將詳細(xì)介紹如何在IntelliJ IDEA中配置并連接達(dá)夢數(shù)據(jù)庫,助力開發(fā)者高效完成數(shù)據(jù)庫開發(fā)工作,需要的朋友可以參考下2025-03-03Spring的IOC容器實(shí)例化bean的方式總結(jié)
IOC容器實(shí)例化bean的三種方式:構(gòu)造方法、靜態(tài)工廠、實(shí)例工廠,本文將通過代碼示例給大家詳細(xì)講解一下這三種方式,對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-01-01maven-maven使用-P參數(shù)打包不同環(huán)境問題
這篇文章主要介紹了maven-maven使用-P參數(shù)打包不同環(huán)境問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-11-11