I have been trying to get all column vs. table regardless of alias used, for example, assume this query
What I am currently doing isIn the code below, I’m resolving tables for ana given alias at the end of each scope below.
ScopedData class is used to store data on different query scopes. When exiting from that scope I remove the code,data associated with the current scope by calliing pop().
SqlTokenVisitor class extending TablesNamesFinder sligtly differs from it's parent. I've overriden one of it's visit() methods to change the order of visiting.
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.function.Supplier;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.ParenthesedFromItem;
import net.sf.jsqlparser.statement.select.ParenthesedSelect;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
// I used this class to store data on different scopes when exiting from that scope i simply pop
class ScopedData<T> extends LinkedList<T> {
private final Supplier<T> creator;
private ScopedData<T> nonEmptyScope() {
if (this.isEmpty()) {
throw new IllegalStateException("Currently scope is empty");
} else {
return this;
}
}
public ScopedData(Supplier<T> creator) {
this.creator = creator;
}
public void enterScope() {
this.push(creator.get());
}
public void exitScope() {
this.nonEmptyScope().pop();
}
public T current() {
return this.nonEmptyScope().peek();
}
}
// I am using different class than TablesNamesFinder but it is almost same as that
class SqlTokenVisitor<T> extends TablesNamesFinder<T> {
// I have overridden this method because I have changed the order of visiting from in my class
@Override
public <S> T visit(PlainSelect plainSelect, S context) {
List<WithItem> withItemsList = plainSelect.getWithItemsList();
if (withItemsList != null && !withItemsList.isEmpty()) {
for (WithItem withItem : withItemsList) {
withItem.accept((SelectVisitor<?>) this, context);
}
}
if (plainSelect.getFromItem() != null) {
plainSelect.getFromItem().accept(this, context);
}
if (plainSelect.getJoins() != null) {
visitJoins(plainSelect.getJoins(), context);
}
if (plainSelect.getWhere() != null) {
plainSelect.getWhere().accept(this, context);
}
if (plainSelect.getHaving() != null) {
plainSelect.getHaving().accept(this, context);
}
if (plainSelect.getOracleHierarchical() != null) {
plainSelect.getOracleHierarchical().accept(this, context);
}
if (plainSelect.getSelectItems() != null) {
for (SelectItem<?> item : plainSelect.getSelectItems()) {
item.accept(this, context);
}
}
return null;
}
private <S> T visitJoins(List<Join> joins, S context) {
for (Join join : joins) {
join.getFromItem().accept(this, context);
join.getRightItem().accept(this, context);
for (Expression expression : join.getOnExpressions()) {
expression.accept(this, context);
}
}
return null;
}
public SqlTokenVisitor() {
this.init(true);
}
}
/**
* Scoped visitor helps to process query in scoped manner for example assume this query,
*
* ```
* SELECT e.name, // new scope (select 1)
* (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id // new scope (select 2)) AS project_count,
* (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id // new scope (select 3)) AS task_count
* FROM employees AS e;
* ```
*
* parse_statement(select_1):
* push_state(stack, select_1)
* parse_statement(select_2),
* push_state(stack, select_2)
* use_state(stack)
* pop_state(stack)
* parse_statement(select_3)
* push_state(stack, select_3)
* use_state(stack)
* pop_state(stack)
* use_state(stack)
* pop_state(stack)
*
* we are doing pop since used data no more needed also it helps to reuse the same variable
* in different scope. Another use of stack is assume
*
* ```
* select a.name, (select a.name from table1 as a) from table2 as a
* ```
*
* upon pushing state of sub select it hides the outer variable `a` since it is located at
* bottom and we look up symbol from top of stack which is good since we need to use nearest
* matching variable.
*
* In below implementation we simply call enterScope before visit and exitScope
* after visit Since the parser parses the Elements as tree we can simply
* use that,
*
* enteringScope() <- 1
* select
* |
* |-- enteringScope() <- 2
* |-- select
* |-- exitingScope() <- 2
* |
* |-- enteringScope() <- 3
* |-- select
* |-- exitingScope() <- 3
* |
* exitingScope() <- 1
*/
abstract class ScopedVisitor<T> extends SqlTokenVisitor<T> {
@Override
public <S> T visit(PlainSelect select, S context) {
this.enteringScope();
super.visit(select, context);
this.exitingScope();
return null;
}
public abstract void enteringScope();
public abstract void exitingScope();
}
class ColumnVisitor extends ScopedVisitor<Void> {
private void processScope(ScopeData scopeData) {
for (Column column : scopeData.columns) {
String resolvedTable = this.resolveAlias(column.getTable()).getName();
System.out.println(column.getColumnName() + " : " + resolvedTable);
}
}
private Table resolveAlias(Table alias) {
// Try to find alias table in scopes
for (ScopeData data : scopedData) {
Table resolved = resolveAlias(data.tables, alias);
if (resolved != null) {
return resolved;
}
}
// if no alias found then it is original table
return alias;
}
public Table resolveAlias(List<Table> tables, Table alias) {
// since if one table alias matches with another tabe name we can resolve it as table
for (Table table : tables) {
if (table.getAlias() != null && table.getAlias().getName().equals(alias.getName())) {
return table;
}
}
return null;
}
@Override
public <S> Void visit(LateralSubSelect lateralSubSelect, S context) {
// we are traking other table that are not actual table but alias
if (lateralSubSelect.getAlias() != null) {
this.scopedData.current().otherTables.add(lateralSubSelect.getAlias().getName());
}
super.visit(lateralSubSelect, context);
return null;
}
@Override
public <S> Void visit(WithItem withItem, S context) {
this.scopedData.current().otherTables.add(withItem.getAlias().getName());
super.visit(withItem, context);
return null;
}
@Override
public <S> Void visit(ParenthesedSelect select, S context) {
if (select.getAlias() != null) {
this.scopedData.current().otherTables.add(select.getAlias().getName());
}
super.visit(select, context);
return null;
}
@Override
public <S> Void visit(ParenthesedFromItem parenthesis, S context) {
if (parenthesis.getAlias() != null) {
this.scopedData.current().otherTables.add(parenthesis.getAlias().getName());
}
super.visit(parenthesis, context);
return null;
}
@Override
public <S> Void visit(Column column, S context) {
super.visit(column, context);
if (column.getTable() != null) {
this.scopedData.current().columns.add(column);
}
return null;
}
@Override
public <S> Void visit(Table table, S context) {
super.visit(table, context);
this.scopedData.current().tables.add(table);
return null;
}
@Override
public void enteringScope() {
this.scopedData.enterScope();
}
@Override
public void exitingScope() {
this.processScope(this.scopedData.current());
this.scopedData.exitScope();
}
private static class ScopeData {
public final List<Column> columns = new LinkedList<>();
public final List<Table> tables = new LinkedList<>();
public final List<String> otherTables = new LinkedList<>();
}
private final ScopedData<ScopeData> scopedData = new ScopedData<>(ScopeData::new);
}
public class Main {
public static void main(String[] args) throws JSQLParserException {
String query = "SELECT e.name, \n" + //
" (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id) AS project_count,\n" + //
" (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id) AS task_count\n" + //
"FROM employees AS e;";
Statement stmt = CCJSqlParserUtil.parse(query);
stmt.accept(new ColumnVisitor());
}
}
You can find jsqlparserJSqlParser in github
