JSQLParser gel all select items from query and subquery

Question

I want to get all the select items from query and subquery

as per this link, https://stackoverflow.com/a/30505741/8249665 I can get select items from the main query.

For subquery like this- "SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)", I'll have to navigate to selectbody -> where -> rightItemList -> again selectbody -> finally items.

Also, there can be more nested selects. How do I achieve it for both with subquery/subqueries and without subquery?

score 1 · Accepted Answer · answered Apr 22 '20 at 10:49

This one is a simple extension of the link you mentioned.

To traverse the parse tree JSqlParser provides the visitor pattern. Here I reuse the TablesNamesFinder, which uses a for your purposes complete implementation of visiting all nodes. Now it is of no importance how deeply your sqls are nested. For each call of the overwritten method you get the main SQL or sub selects of your SQL.

public static void main(String args[]) throws JSQLParserException {
    String sql = "SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)";

    Select select = (Select) CCJSqlParserUtil.parse(sql);

    TablesNamesFinder tablesNamesFinder = new TablesNamesFinder() {
        @Override
        public void visit(PlainSelect plainSelect) {
            System.out.println("plainselect -> " + plainSelect.toString());
            for (SelectItem item : plainSelect.getSelectItems()) {
                System.out.println(item.toString());
            }
            super.visit(plainSelect);
        }
    };

    tablesNamesFinder.getTableList(select);
}

JSQLParser gel all select items from query and subquery

1 Answers1