Simplier and more generic parsing using XPath

[manticore-projects]

Example SQL

select 1 as b, 2, 3 from dual as tablename where a=b and b=c;

Can be simply queried via XPath:

String sqlString = "select 1 as b, 2, 3 from dual as tablename where a=b and b=c;";
    
// return ANY column of the SELECT statement
Collection<Column> columns = JSQLFormatter.extract(sqlString, Column.class, "//Column");
for (Column column: columns) {
  System.out.println("Found ALL column: " + column);
}

// return only columns part of the WHERE clause on the Left side of the EQUALSTO
columns = JSQLFormatter.extract(sqlString, Column.class, "//where/leftExpression/Column");
for (Column column: columns) {
  System.out.println("Found WHERE column: " + column);
}

Output

Found ALL column: a
Found ALL column: b
Found ALL column: b
Found ALL column: c
Found WHERE column: a
Found WHERE column: b

Based on the Parsed Object Tree:

SQL Text
 └─Statements: net.sf.jsqlparser.statement.select.Select
    └─selectBody: net.sf.jsqlparser.statement.select.PlainSelect
       ├─selectItems: net.sf.jsqlparser.statement.select.SelectExpressionItem
       │  ├─LongValue: 1
       │  └─Alias:  AS b
       ├─selectItems: net.sf.jsqlparser.statement.select.SelectExpressionItem
       │  └─LongValue: 2
       ├─selectItems: net.sf.jsqlparser.statement.select.SelectExpressionItem
       │  └─LongValue: 3
       ├─Table: dual AS tablename
       │  └─Alias:  AS tablename
       └─where: net.sf.jsqlparser.expression.operators.conditional.AndExpression
          ├─leftExpression: net.sf.jsqlparser.expression.operators.relational.EqualsTo
          │  ├─Column: a
          │  └─Column: b
          └─rightExpression: net.sf.jsqlparser.expression.operators.relational.EqualsTo
             ├─Column: b
             └─Column: c

I believe this was a much easier way to quickly query or access Objects of a SQL statement instead of writing the Listeners.
The code above works nicely in JSQLFormatter already -- do we want to port it into JSQLParser directly?

[manticore-projects]

Querying specifically using a full XPath:

// return only the C column based on the complete XPath
columns = JSQLFormatter.extract(sqlString, Column.class, "/Statements/selectBody/where/rightExpression/Column[2]");
for (Column column: columns) {
  System.out.println("Found specific column by complete XPath: " + column);
}

Found specific column by complete XPath: c

[wumpz]

As I said, cool. Any reason why you do not use https://commons.apache.org/proper/commons-jxpath/? Or do you use it? When I needed something like that it worked for me.

Could we integrate this in JSqlParser itself?

[manticore-projects]

As I said, cool. Any reason why you do not use https://commons.apache.org/proper/commons-jxpath/? Or do you use it?

Actually no, I did not even know that something like this existed and did not find it when searching for such a lib.
So I wrote it myself, which also have the advantage that I can influence the Tree and iron out certain design flaws (e. g. Table is not a Leaf due to the Alias -- but is expected to be shown as a Leaf with Alias as an attribute`).

Also, JSQLParser is self containing so far without Runtime Dependencies to Third party libs and Commons JXPath has not seen updates since 2015.

Could we integrate this in JSqlParser itself?

Yes, its plain Java code. Only the Tree Visualization depends on a small library. We could either shadow it or re-implement it (in a simplified form).

I will play a bit with JXPath and then craft a PR against JSQLParser.

[manticore-projects]

I don't feel like JXPath was a good option.

[wumpz]

Actually, you are right that we should keep JSqlParser jar independent from third party libs. And you are right, JXPath is quite an old lad. ;) Still it works.