-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
Simplier and more generic parsing using XPath #1626
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 4 comments 1 reply
-
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
Beta Was this translation helpful? Give feedback.
All reactions
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
I don't feel like JXPath was a good option.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.