网站改版工作方案,网站运营每天做的,自己做竞猜网站挣钱吗,源码打包成app简介 JSqlParse是一款很精简的sql解析工具#xff0c;它可以将常用的sql文本解析成具有层级结构的语法树#xff0c;我们可以针对解析后的节点进行处理(增加、移除、修改等操作),从而生成符合我们业务要求的sql#xff0c;比如添加过滤条件等等 JSqlParse采用访问者模式
项…简介 JSqlParse是一款很精简的sql解析工具它可以将常用的sql文本解析成具有层级结构的语法树我们可以针对解析后的节点进行处理(增加、移除、修改等操作),从而生成符合我们业务要求的sql比如添加过滤条件等等 JSqlParse采用访问者模式
项目简介 项目结构非常简单从截图上看就5个包。如果对源码感兴趣的可以直接从github上下载源码包调试。其中expression包包含了所有的sql表达式的抽象对象 statement包含了所有sql语句的类型比如增删改查ddl语句,rollback语句等等 schema包是对数据库基本单元的抽象服务器、数据库、表、列等等 parser包是整个解析的核心逻辑感兴趣的可以自行源码调试 使用示例 上面已经做了关于该解析工具的简单介绍对于工具类最重要的使用。以下举例关于增、删、改、查的sql语句中均增加一列为例介绍该工具的简单使用
依赖引入
dependencygroupIdcom.github.jsqlparser/groupIdartifactIdjsqlparser/artifactIdversion4.5/version
/dependency
新增add
原始sqlinsert 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());ListColumn columns insert.getColumns();columns.add(new Column(STATUS));Select select insert.getSelect();SelectBody selectBody select.getSelectBody();if (selectBody instanceof SetOperationList) {SetOperationList operationList (SetOperationList)selectBody;ListSelectBody 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;ListExpression expressions expressionList.getExpressions();for (Expression expression : expressions) {if(expression instanceof RowConstructor) {RowConstructor rowConstructor (RowConstructor)expression;ExpressionList exprList rowConstructor.getExprList();ListExpression 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 {Overridepublic void visit(Insert insert) {printTableName(insert.getTable());ListColumn 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 {Overridepublic void visit(SetOperationList setOpList) {ListSelectBody selects setOpList.getSelects();for (SelectBody body : selects) {body.accept(this);}}Overridepublic 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 {Overridepublic void visit(ExpressionList expressionList) {ListExpression 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 {Overridepublic void visit(RowConstructor rowConstructor) {ExpressionList exprList rowConstructor.getExprList();ListExpression expressions exprList.getExpressions();expressions.add(new StringValue(0));}
}以上是关于新增sql增加一列作为插入的简单运用其中有通过类型判断处理和通过访问者模式处理(基于java多态实现)最终打印的结果如下 删除delete
原sqldelete from t_user_info where user_name ? and addres ?
期望在删除时增加过滤条件STATUS0
相关代码如下
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 {Overridepublic 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语句修改***/
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();ArrayListUpdateSet 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 {Overridepublic void visit(Update update) {CommonUtil.printTableName(update.getTable());ArrayListUpdateSet 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 {Overridepublic 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 {Overridepublic 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的过滤条件完成多租户数据隔离处理的。具体的源码逻辑可以调试根据
