Listing all the tables/columns of parsed SQL SELECT statement

Question

I'm trying to extract a list of tables/columns from an SQL SELECT statement with JSqlParser. The SELECT contains a calculation and a nested SELECT:

SELECT col1 AS a, (col2 - col21) AS b, col3 AS c FROM table1 
       where col1 in (select col4 from table4)

This should return:

col1 table1
col2 table1
col21 table1
col3 table1
col4 table4

If I parse it with CCJSqlParserUtil, I get something different:

    Select stmt = (Select) CCJSqlParserUtil.parse("SELECT col1 AS a, col2 AS b, col3 AS c "
            + "FROM table1 WHERE where col1 in (select col4 from table4)");
    Map<String, Expression> map = new HashMap<>();        
    for (SelectItem selectItem : ((PlainSelect)stmt.getSelectBody()).getSelectItems()) {
        selectItem.accept(new SelectItemVisitorAdapter() {
            @Override
            public void visit(SelectExpressionItem item) {
                map.put(item.getAlias().getName(), item.getExpression());
            }
        });
    }

    System.out.println("map " + map);

prints:

map {a=col1, b=col2, c=col3}

and that is not what I'm looking for. The SELECT statement could even be more complex, with nested and sub-nested SELECTs; is there a way to get a list of all the columns/tables?

score 1 · Accepted Answer · answered Nov 22 '19 at 12:57

To extract all tablenames (analog column names) you could use the TableNamesFinder:

Statement statement = CCJSqlParserUtil.parse("SELECT * FROM MY_TABLE1");
Select selectStatement = (Select) statement;
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
List<String> tableList = tablesNamesFinder.getTableList(selectStatement);

The code you use (I assume from JSQLParsers wiki) is used for:

The task here is to build a map, where the alias is the key and the expression to this alias the value of one Map entry.

Therefore something completely different.

Additionally you should check: Parsing table and column names from SQL/HQL Java.

Keep in mind, JSqlParser is only a parser. To map columns to tables you have to check your database schema and introduce some kind of block or visibility checking within your sql. Here are some problematic constellations:

select a from tab1, tab2
select a, (select a from tab2) b from tab1
...

Listing all the tables/columns of parsed SQL SELECT statement

1 Answers1