赞
踩
一、介绍
JSqlParse是一款很精简的sql解析工具,将sql语句转成Java对象。
用法:可以用于数据权限处理,拦截sql解析改写sql等等。
1. 首先引入jar包
- <dependency>
- <groupId>com.github.jsqlparser</groupId>
- <artifactId>jsqlparser</artifactId>
- <version>1.2</version>
- </dependency>
二、结构
三、工具类封装
网上有相关讲解,但是没有进行整合的例子,我写了个工具类供参考!!!直接上代码
3.1 定义SQL枚举类型
- package com.test.enums;
-
- /**
- * @className: SqlType
- * @description: SQL 类型
- * @version: 1.0
- */
- public enum SqlType {
- ALTER,
- CREATEINDEX,
- CREATETABLE,
- CREATEVIEW,
- DELETE,
- DROP,
- EXECUTE,
- INSERT,
- MERGE,
- REPLACE,
- SELECT,
- TRUNCATE,
- UPDATE,
- UPSERT,
- NONE
- }
-
3.2 封装工具类
- package com.test.utils;
-
- import com.test.enums.SqlType;
- import net.sf.jsqlparser.JSQLParserException;
- import net.sf.jsqlparser.expression.LongValue;
- import net.sf.jsqlparser.parser.CCJSqlParserUtil;
- import net.sf.jsqlparser.schema.Table;
- import net.sf.jsqlparser.statement.Statement;
- import net.sf.jsqlparser.statement.alter.Alter;
- import net.sf.jsqlparser.statement.create.index.CreateIndex;
- import net.sf.jsqlparser.statement.create.table.CreateTable;
- import net.sf.jsqlparser.statement.create.view.CreateView;
- import net.sf.jsqlparser.statement.delete.Delete;
- import net.sf.jsqlparser.statement.drop.Drop;
- import net.sf.jsqlparser.statement.execute.Execute;
- import net.sf.jsqlparser.statement.insert.Insert;
- import net.sf.jsqlparser.statement.merge.Merge;
- import net.sf.jsqlparser.statement.replace.Replace;
- import net.sf.jsqlparser.statement.select.FromItem;
- import net.sf.jsqlparser.statement.select.Join;
- import net.sf.jsqlparser.statement.select.Limit;
- import net.sf.jsqlparser.statement.select.PlainSelect;
- import net.sf.jsqlparser.statement.select.Select;
- import net.sf.jsqlparser.statement.select.SelectBody;
- import net.sf.jsqlparser.statement.select.SelectItem;
- import net.sf.jsqlparser.statement.select.SubSelect;
- import net.sf.jsqlparser.statement.select.WithItem;
- import net.sf.jsqlparser.statement.truncate.Truncate;
- import net.sf.jsqlparser.statement.update.Update;
- import net.sf.jsqlparser.statement.upsert.Upsert;
- import net.sf.jsqlparser.util.TablesNamesFinder;
-
- import java.io.StringReader;
- import java.util.ArrayList;
- import java.util.List;
-
- /**
- * @projectName: springboot_demo
- * @packageName: com.test.utils
- * @description: jsqlparser解析SQL工具类
- * PlainSelect类不支持union、union all等请使用SetOperationList接口
- * @version: 1.0
- */
- public class SqlParserTool {
- /**
- * 由于jsqlparser没有获取SQL类型的原始工具,并且在下面操作时需要知道SQL类型,所以编写此工具方法
- *
- * @param sql sql语句
- * @return sql类型,
- * @throws JSQLParserException
- */
- public static SqlType getSqlType(String sql) throws JSQLParserException {
- Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
- if (sqlStmt instanceof Alter) {
- return SqlType.ALTER;
- } else if (sqlStmt instanceof CreateIndex) {
- return SqlType.CREATEINDEX;
- } else if (sqlStmt instanceof CreateTable) {
- return SqlType.CREATETABLE;
- } else if (sqlStmt instanceof CreateView) {
- return SqlType.CREATEVIEW;
- } else if (sqlStmt instanceof Delete) {
- return SqlType.DELETE;
- } else if (sqlStmt instanceof Drop) {
- return SqlType.DROP;
- } else if (sqlStmt instanceof Execute) {
- return SqlType.EXECUTE;
- } else if (sqlStmt instanceof Insert) {
- return SqlType.INSERT;
- } else if (sqlStmt instanceof Merge) {
- return SqlType.MERGE;
- } else if (sqlStmt instanceof Replace) {
- return SqlType.REPLACE;
- } else if (sqlStmt instanceof Select) {
- return SqlType.SELECT;
- } else if (sqlStmt instanceof Truncate) {
- return SqlType.TRUNCATE;
- } else if (sqlStmt instanceof Update) {
- return SqlType.UPDATE;
- } else if (sqlStmt instanceof Upsert) {
- return SqlType.UPSERT;
- } else {
- return SqlType.NONE;
- }
- }
-
- /**
- * 获取sql操作接口,与上面类型判断结合使用
- * example:
- * String sql = "create table a(a string)";
- * SqlType sqlType = SqlParserTool.getSqlType(sql);
- * if(sqlType.equals(SqlType.SELECT)){
- * Select statement = (Select) SqlParserTool.getStatement(sql);
- * }
- *
- * @param sql
- * @return
- * @throws JSQLParserException
- */
- public static Statement getStatement(String sql) throws JSQLParserException {
- Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
- return sqlStmt;
- }
-
- /**
- * 获取tables的表名
- *
- * @param statement
- * @return
- */
- public static List<String> getTableList(Select statement) {
- TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
- List<String> tableList = tablesNamesFinder.getTableList(statement);
- return tableList;
- }
-
- /**
- * 获取join层级
- *
- * @param selectBody
- * @return
- */
- public static List<Join> getJoins(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- List<Join> joins = ((PlainSelect) selectBody).getJoins();
- return joins;
- }
- return new ArrayList<Join>();
- }
-
- /**
- * @param selectBody
- * @return
- */
- public static List<Table> getIntoTables(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- List<Table> tables = ((PlainSelect) selectBody).getIntoTables();
- return tables;
- }
- return new ArrayList<Table>();
- }
-
- /**
- * @param selectBody
- * @return
- */
- public static void setIntoTables(SelectBody selectBody, List<Table> tables) {
- if (selectBody instanceof PlainSelect) {
- ((PlainSelect) selectBody).setIntoTables(tables);
- }
- }
-
- /**
- * 获取limit值
- *
- * @param selectBody
- * @return
- */
- public static Limit getLimit(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- Limit limit = ((PlainSelect) selectBody).getLimit();
- return limit;
- }
- return null;
- }
-
- /**
- * 为SQL增加limit值
- *
- * @param selectBody
- * @param l
- */
- public static void setLimit(SelectBody selectBody, long l) {
- if (selectBody instanceof PlainSelect) {
- Limit limit = new Limit();
- limit.setRowCount(new LongValue(String.valueOf(l)));
- ((PlainSelect) selectBody).setLimit(limit);
- }
- }
-
- /**
- * 获取FromItem不支持子查询操作
- *
- * @param selectBody
- * @return
- */
- public static FromItem getFromItem(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
- return fromItem;
- } else if (selectBody instanceof WithItem) {
- SqlParserTool.getFromItem(((WithItem) selectBody).getSelectBody());
- }
- return null;
- }
-
- /**
- * 获取子查询
- *
- * @param selectBody
- * @return
- */
- public static SubSelect getSubSelect(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
- if (fromItem instanceof SubSelect) {
- return ((SubSelect) fromItem);
- }
- } else if (selectBody instanceof WithItem) {
- SqlParserTool.getSubSelect(((WithItem) selectBody).getSelectBody());
- }
- return null;
- }
-
- /**
- * 判断是否为多级子查询
- *
- * @param selectBody
- * @return
- */
- public static boolean isMultiSubSelect(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
- if (fromItem instanceof SubSelect) {
- SelectBody subBody = ((SubSelect) fromItem).getSelectBody();
- if (subBody instanceof PlainSelect) {
- FromItem subFromItem = ((PlainSelect) subBody).getFromItem();
- return subFromItem instanceof SubSelect;
- }
- }
- }
- return false;
- }
-
- /**
- * 获取查询字段
- *
- * @param selectBody
- * @return
- */
- public static List<SelectItem> getSelectItems(SelectBody selectBody) {
- if (selectBody instanceof PlainSelect) {
- List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
- return selectItems;
- }
- return null;
- }
-
- public static void main(String[] args) throws JSQLParserException {
- Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader("show databases"));
- //获取到子查询语句
- String sql = "select * from (select userid from a) a";
- SqlType sqlType = SqlParserTool.getSqlType(sql);
- if(sqlType.equals(SqlType.SELECT)){
- Select statement = (Select) SqlParserTool.getStatement(sql);
- SubSelect subSelect = SqlParserTool.getSubSelect(statement.getSelectBody());
- System.out.println(subSelect.getSelectBody());
- }
- }
- }
该文章基于原文链接:https://blog.csdn.net/ciqingloveless/article/details/82626304
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。