你的位置:首页 > 信息动态 > 新闻中心
信息动态
联系我们

【jsqlpaser使用001】 简单使用

2021-12-1 2:36:21

一.简单介绍与使用

简介

SqlParser是一个SQL语句解析器。它将SQL转换为Java类的可遍历层次结构。

引入依赖

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

测试类

@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
public class JsqlparserTest {
    @Test
    public void testSelect() throws JSQLParserException {
        // 使用工具类把SQL转换为Select对象
        Select select = (Select) CCJSqlParserUtil.parse("SELECT username,age,sex FROM user");
        SelectBody selectBody = select.getSelectBody();
        System.err.println(selectBody);
    }
}

源码结构

https://github.com/JSQLParser/JSqlParser

expression sql构建相关类

  • Alias
  • EqualsTo
  • InExpression等

parser sql解析相关类

  • CCJSqlParser
  • CCJSqlParserUtil

schema 数据库schema相关的类 ,比如表、列等

  • Column
  • Database
  • MultiPartName
  • Sequence
  • Server
  • Synonym
  • Table

statement 封装了数据库操作对象,create、insert、delete、select等

  • alter
  • create
  • delete
  • drop
  • execute
  • grant
  • insert
  • merge
  • replace
  • select
  • update
  • upsert

util 各种工具类、不同DB版本、SQL标准等处理类

  • SelectUtils
  • DatabaseType
注意点
  • union操作想得到PlainSelect需要自己再包一层 select * from

简单工具类

/**
 * jsqlparser解析SQL工具类
 * PlainSelect类不支持union、union all等请使用SetOperationList接口
 */
public class SqlParserTool {
    /**
     * 由于jsqlparser没有获取SQL类型的原始工具,并且在下面操作时需要知道SQL类型,所以编写此工具方法
     *
     * @param sql sql语句
     * @return sql类型,
     * @throws JSQLParserException
     */
    public static SqlTypeEnum getSqlType(String sql) throws JSQLParserException {
        Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
        if (sqlStmt instanceof Alter) {
            return SqlTypeEnum.ALTER;
        } else if (sqlStmt instanceof CreateIndex) {
            return SqlTypeEnum.CREATEINDEX;
        } else if (sqlStmt instanceof CreateTable) {
            return SqlTypeEnum.CREATETABLE;
        } else if (sqlStmt instanceof CreateView) {
            return SqlTypeEnum.CREATEVIEW;
        } else if (sqlStmt instanceof Delete) {
            return SqlTypeEnum.DELETE;
        } else if (sqlStmt instanceof Drop) {
            return SqlTypeEnum.DROP;
        } else if (sqlStmt instanceof Execute) {
            return SqlTypeEnum.EXECUTE;
        } else if (sqlStmt instanceof Insert) {
            return SqlTypeEnum.INSERT;
        } else if (sqlStmt instanceof Merge) {
            return SqlTypeEnum.MERGE;
        } else if (sqlStmt instanceof Replace) {
            return SqlTypeEnum.REPLACE;
        } else if (sqlStmt instanceof Select) {
            return SqlTypeEnum.SELECT;
        } else if (sqlStmt instanceof Truncate) {
            return SqlTypeEnum.TRUNCATE;
        } else if (sqlStmt instanceof Update) {
            return SqlTypeEnum.UPDATE;
        } else if (sqlStmt instanceof Upsert) {
            return SqlTypeEnum.UPSERT;
        } else {
            return SqlTypeEnum.NONE;
        }
    }

    /**
     * 获取sql操作接口,与上面类型判断结合使用
     * example:
     * String sql = "create table a(a string)";
     * SqlTypeEnum sqlType = SqlParserTool.getSqlType(sql);
     * if(sqlType.equals(SqlTypeEnum.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));
        }
        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));
        }
        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();
                    if (subFromItem instanceof SubSelect) {
                        return true;
                    }
                }
            }
        }
        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"));
    }
}

测试类

@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
public class JsqlparserTest {
    /**
     * 使用工具类把SQL转换为Select对象
     */
    @Test
    public void testSelect() throws JSQLParserException {
        // 使用工具类把SQL转换为Select对象
        Select select = (Select) CCJSqlParserUtil.parse("select username,age,sex from user");
        SelectBody selectBody = select.getSelectBody();
        System.err.println(selectBody);
    }

    /**
     * 设置别名
     */
    @Test
    public void testSelectSql() {
        String sql = "select username,age,sex from user";
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        Select select = null;
        try {
            select = (Select) parserManager.parse(new StringReader(sql));
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        SelectBody selectBody = select.getSelectBody();
        PlainSelect plainSelect = parseSelectBody(selectBody);
        if (Objects.nonNull(plainSelect)) {
            FromItem fromItem = plainSelect.getFromItem();
            Alias alias = fromItem.getAlias();
            if (Objects.isNull(alias)) {
                String name = "自定义别名";
                fromItem.setAlias(new Alias(name));
                List<SelectItem> items = plainSelect.getSelectItems();
                for (SelectItem item : items) {
                    SelectExpressionItem selectExpressionItem = (SelectExpressionItem) item;
                    Expression expression = selectExpressionItem.getExpression();
                    Column column = (Column) expression;
                    Table tableInfo = new Table();
                    tableInfo.setAlias(new Alias(name));
                    column.setTable(tableInfo);
                }
            }
        }
        log.info(plainSelect.toString());
    }

    protected static PlainSelect parseSelectBody(SelectBody selectBody) {
        if (selectBody instanceof PlainSelect) {
            return (PlainSelect) selectBody;
        } else {
            System.out.println(selectBody.getClass() + ":遇到无法解析的对象:" + Thread.currentThread().getStackTrace()[1].getMethodName());
            return null;
        }
    }

    /**
     * 不支持union操作
     */
    @Test
    public void testSelectSqlUnion() throws JSQLParserException {
        String sql = "select t1.name from table1 t1 union all select t2.b,t3.c from table2 t2,table3 t3 where t2.b = t3.c";
        Statement stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //多select不能直接解析
        SetOperationList setOperationList = (SetOperationList) selectBody;
        PlainSelect plainSelect = (PlainSelect) selectBody;//抛错
    }

    /**
     * 解析sql
     */
    @Test
    public void testSelectSql2() throws JSQLParserException {
        //只能再包一层
        String sql = "select * from (select t1.name from table1 t1 union all select t2.b,t3.c from table2 t2,table3 t3 where t2.b = t3.c)";
        Statement stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        PlainSelect plainSelect = (PlainSelect) selectBody;
        //From分析
        FromItem fromItem = plainSelect.getFromItem();
        //子查询不能直接获取到tableName
        //        Table tableFrom = (Table) fromItem; // table.getAlias().getName(), table.getName()
        // join分析
        List<Join> joins = plainSelect.getJoins();
        if (CollectionUtils.isNotEmpty(joins)) {
            for (Join join : joins) {
                FromItem rightItem = join.getRightItem();
                Table table = (Table) rightItem; // (table.getAlias().getName(), table.getName());
                // on 条件分析
                Expression onExpression = join.getOnExpression();  //和where一样
                // where 分析
                Expression where = plainSelect.getWhere();
                // where 有多种情况,一种就是简单的a = b,
                //另外就是 a = b or ... and,
                // AndExpression
                //OrExpression
                //XorExpression
                if (where instanceof EqualsTo) {
                    EqualsTo equalsTo = (EqualsTo) where;
                    Expression rightExpression = equalsTo.getRightExpression();
                    Expression leftExpression = equalsTo.getLeftExpression();
                    if (rightExpression instanceof Column && leftExpression instanceof Column) {
                        Column rightColumn = (Column) rightExpression;
                        Column leftColumn = (Column) leftExpression;
                        System.out.println(rightColumn.getTable().toString() + "表的" + rightColumn.getColumnName() + "字段 -> " + leftColumn.getTable().toString() + "表的" + leftColumn.getColumnName() + "字段");
                    }
                } else if (where instanceof AndExpression) {
                    AndExpression andExpression = (AndExpression) where;
                    Expression leftExpression = andExpression.getLeftExpression();
                    Expression rightExpression = andExpression.getRightExpression();
                }
            }
        }
    }

    /**
     * 测试查询返回增加一列
     */
    @Test
    public void testAddSelectColumn() throws Exception {
        Select select = (Select) CCJSqlParserUtil.parse("select name from user where id = 1");
        SelectUtils.addExpression(select, new Column("mail"));
        Assert.assertEquals(select.toString(), "SELECT name, mail FROM user WHERE id = 1");
    }

    /**
     * 测试查询语句增加where条件
     */
    @Test
    public void testAddWhereCondition() throws Exception {
        Select select = (Select) CCJSqlParserUtil.parse("select name from user");
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        if (plainSelect.getWhere() == null) {
            EqualsTo equalsTo = new EqualsTo();
            equalsTo.setLeftExpression(new Column("id"));
            equalsTo.setRightExpression(new LongValue(1000L));
            plainSelect.setWhere(equalsTo);
        }
        Assert.assertEquals(select.toString(), "SELECT name FROM user WHERE id = 1000");
    }

    /**
     * 测试增加where查询条件
     */
    @Test
    public void testAddCondition() throws Exception {
        Select select = (Select) CCJSqlParserUtil.parse("select name from user where id = 1000");
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        // 原where表达式
        Expression where = plainSelect.getWhere();
        // 新增的查询条件表达式
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column("name"));
        equalsTo.setRightExpression(new StringValue("'张三'"));
        // 用and链接条件
        AndExpression and = new AndExpression(where, equalsTo);
        // 设置新的where条件
        plainSelect.setWhere(and);
        Assert.assertEquals(select.toString(), "SELECT name FROM user WHERE id = 1000 AND name = '张三'");
    }

    /**
     * 测试null条件
     */
    @Test
    public void testNullCondition() throws Exception {
        Select select = (Select) CCJSqlParserUtil.parse("select name from user where id = 1000");
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        // 原where表达式
        Expression where = plainSelect.getWhere();
        // 新增的null判断条件
        IsNullExpression isNullExpression = new IsNullExpression();
        isNullExpression.setLeftExpression(new Column("name"));
        isNullExpression.setNot(true);
        // 用and链接条件
        AndExpression and = new AndExpression(where, isNullExpression);
        // 设置新的where条件
        plainSelect.setWhere(and);
        Assert.assertEquals(select.toString(), "SELECT name FROM user WHERE id = 1000 AND name IS NOT NULL");
    }

    /**
     * 获取表名
     */
    @Test
    public void testNullCondition11() throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse("select * from public.customer union all  select * from public.user");
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(statement);
        System.out.println(tableList);
        if (statement instanceof Commit) {
            Commit commit = (Commit) statement;
            System.out.println(commit.getClass());
        }
    }

}