JSqlParser / Discussion / Open Discussion: Howto get query columns

I\'m a newbie in jsqlparser ans I\'m trying to exrtract tables and colums from complex queries, so I produced a modified version of TablesNamesFinder, included below. However, my code isn\'t really able to extract colums, because it fails also in very simple tests: for example:

Reading SQL from stdin (close statement with \";\", quit; or exit; to quit)
SELECT COLUMN1, COLUMN2
FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3)
LEFT OUTER JOIN MY_TABLE4
WHERE ID = (SELECT MAX(ID) FROM MY_TABLE5) AND ID2 IN (SELECT COL1, COL2 FROM MY_TABLE6);
Reading SQL from stdin (close statement with \";\", quit; or exit; to quit)

Reading SQL from stdin (close statement with \";\", quit; or exit; to quit)

SELECT COLUMN1, COLUMN2 FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3) LEFT OUTER JOIN MY_TABLE4 WHERE ID = (SELECT MAX(ID) FROM MY_TABLE5) AND ID2 IN (SELECT COL1, COL2 FROM MY_TABLE6);

Table list follows:
MY_TABLE1
MY_TABLE2
MY_TABLE3
MY_TABLE4
MY_TABLE5
MY_TABLE6

Column list follows:
ID
ID2
Reading SQL from stdin (close statement with \";\", quit; or exit; to quit)
SELECT COLUMN1,COLUMN2
FROM MY_TABLE1;
Reading SQL from stdin (close statement with \";\", quit; or exit; to quit)

SELECT COLUMN1,COLUMN2 FROM MY_TABLE1;

Table list follows:
MY_TABLE1

Please, can anyone help me?

Thanks.

My code:

package net.sf.jsqlparser.test.columnTablesfinder;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import net.sf.jsqlparser.expression.AllComparisonExpression;
import net.sf.jsqlparser.expression.AnyComparisonExpression;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.CaseExpression;
import net.sf.jsqlparser.expression.DateValue;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitor;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.InverseExpression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.NullValue;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.TimeValue;
import net.sf.jsqlparser.expression.TimestampValue;
import net.sf.jsqlparser.expression.WhenClause;
import net.sf.jsqlparser.expression.operators.arithmetic.Addition;
import net.sf.jsqlparser.expression.operators.arithmetic.BitwiseAnd;
import net.sf.jsqlparser.expression.operators.arithmetic.BitwiseOr;
import net.sf.jsqlparser.expression.operators.arithmetic.BitwiseXor;
import net.sf.jsqlparser.expression.operators.arithmetic.Concat;
import net.sf.jsqlparser.expression.operators.arithmetic.Division;
import net.sf.jsqlparser.expression.operators.arithmetic.Multiplication;
import net.sf.jsqlparser.expression.operators.arithmetic.Subtraction;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsListVisitor;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.Matches;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItemVisitor;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.Union;

public class ColumnsTablesNamesFinder implements SelectVisitor, FromItemVisitor, ExpressionVisitor, ItemsListVisitor {
private List<String> tables = new ArrayList<String>();
private List<String> columns = new ArrayList<String>();

public ColumnsTablesNamesFinder(Select select) {
select.getSelectBody().accept(this);
}

public List<String> getTableList() {
return tables;
}

public List<String> getColumnList() {
return columns;
}

@Override
public void visit(PlainSelect plainSelect) {
plainSelect.getFromItem().accept(this);

if (plainSelect.getJoins() != null) {
for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext();) {
Join join = (Join) joinsIt.next();
join.getRightItem().accept(this);
}
}
if (plainSelect.getWhere() != null)
plainSelect.getWhere().accept(this);

}

@Override
public void visit(Union union) {
for (Iterator iter = union.getPlainSelects().iterator(); iter.hasNext();) {
PlainSelect plainSelect = (PlainSelect) iter.next();
visit(plainSelect);
}
}

@Override
public void visit(Table tableName) {
String tableWholeName = tableName.getWholeTableName();
tables.add(tableWholeName);
}

@Override
public void visit(SubSelect subSelect) {
subSelect.getSelectBody().accept(this);
}

@Override
public void visit(Addition addition) {
visitBinaryExpression(addition);
}

@Override
public void visit(AndExpression andExpression) {
visitBinaryExpression(andExpression);
}

@Override
public void visit(Between between) {
between.getLeftExpression().accept(this);
between.getBetweenExpressionStart().accept(this);
between.getBetweenExpressionEnd().accept(this);
}

@Override
public void visit(Column tableColumn) {
columns.add(tableColumn.getWholeColumnName());
}

@Override
public void visit(Division division) {
visitBinaryExpression(division);
}

@Override
public void visit(DoubleValue doubleValue) {
}

@Override
public void visit(EqualsTo equalsTo) {
visitBinaryExpression(equalsTo);
}

@Override
public void visit(Function function) {
}

@Override
public void visit(GreaterThan greaterThan) {
visitBinaryExpression(greaterThan);
}

@Override
public void visit(GreaterThanEquals greaterThanEquals) {
visitBinaryExpression(greaterThanEquals);
}

@Override
public void visit(InExpression inExpression) {
inExpression.getLeftExpression().accept(this);
inExpression.getItemsList().accept(this);
}

@Override
public void visit(InverseExpression inverseExpression) {
inverseExpression.getExpression().accept(this);
}

@Override
public void visit(IsNullExpression isNullExpression) {
}

@Override
public void visit(JdbcParameter jdbcParameter) {
}

@Override
public void visit(LikeExpression likeExpression) {
visitBinaryExpression(likeExpression);
}

@Override
public void visit(ExistsExpression existsExpression) {
existsExpression.getRightExpression().accept(this);
}

@Override
public void visit(LongValue longValue) {
}

@Override
public void visit(MinorThan minorThan) {
visitBinaryExpression(minorThan);
}

@Override
public void visit(MinorThanEquals minorThanEquals) {
visitBinaryExpression(minorThanEquals);
}

@Override
public void visit(Multiplication multiplication) {
visitBinaryExpression(multiplication);
}

@Override
public void visit(NotEqualsTo notEqualsTo) {
visitBinaryExpression(notEqualsTo);
}

@Override
public void visit(NullValue nullValue) {
}

@Override
public void visit(OrExpression orExpression) {
visitBinaryExpression(orExpression);
}

@Override
public void visit(Parenthesis parenthesis) {
parenthesis.getExpression().accept(this);
}

@Override
public void visit(StringValue stringValue) {
}

@Override
public void visit(Subtraction subtraction) {
visitBinaryExpression(subtraction);
}

private void visitBinaryExpression(BinaryExpression binaryExpression) {
binaryExpression.getLeftExpression().accept(this);
binaryExpression.getRightExpression().accept(this);
}

@Override
public void visit(ExpressionList expressionList) {
for (Iterator iter = expressionList.getExpressions().iterator(); iter.hasNext();) {
Expression expression = (Expression) iter.next();
expression.accept(this);
}

}

@Override
public void visit(DateValue dateValue) {
}

@Override
    public void visit(TimestampValue timestampValue) {
    }

    public void visit(TimeValue timeValue) {
    }

/* (non-Javadoc)
* @see net.sf.jsqlparser.expression.ExpressionVisitor#visit(net.sf.jsqlparser.expression.CaseExpression)
*/
@Override
public void visit(CaseExpression caseExpression) {
// TODO Auto-generated method stub

}

/* (non-Javadoc)
* @see net.sf.jsqlparser.expression.ExpressionVisitor#visit(net.sf.jsqlparser.expression.WhenClause)
*/
@Override
public void visit(WhenClause whenClause) {
// TODO Auto-generated method stub

}

@Override
public void visit(AllComparisonExpression allComparisonExpression) {
allComparisonExpression.GetSubSelect().getSelectBody().accept(this);
}

@Override
public void visit(AnyComparisonExpression anyComparisonExpression) {
anyComparisonExpression.GetSubSelect().getSelectBody().accept(this);
}

@Override
public void visit(SubJoin subjoin) {
subjoin.getLeft().accept(this);
subjoin.getJoin().getRightItem().accept(this);
}

@Override
public void visit(Concat concat) {
visitBinaryExpression(concat);
}

@Override
public void visit(Matches matches) {
visitBinaryExpression(matches);
}

@Override
public void visit(BitwiseAnd bitwiseAnd) {
visitBinaryExpression(bitwiseAnd);
}

@Override
public void visit(BitwiseOr bitwiseOr) {
visitBinaryExpression(bitwiseOr);
}

@Override
public void visit(BitwiseXor bitwiseXor) {
visitBinaryExpression(bitwiseXor);
}

public static void main(String args) {

try {
CCJSqlParserManager pm = new CCJSqlParserManager();

Statement statement = null;

BufferedReader in = null;

if(args.length < 1) {
System.out.println(\"Reading SQL from stdin (close statement with \\\";\\\", quit; or exit; to quit)\");
in = new BufferedReader(new InputStreamReader(System.in));
} else {
in = new BufferedReader(new InputStreamReader(new FileInputStream(args)));
}

// Read all SQL statements from input.
String sql = \"\", line;

while((line = in.readLine()) != null) {
if (\"exit;\".startsWith(line.toLowerCase()) || \"quit;\".startsWith(line.toLowerCase()))
break;

if (line.trim().length() < 1) // skip empty lines.
continue;

sql += \" \" + line;

if (! line.trim().endsWith(\";\")) {
if(args.length < 1)
System.out.println(\"\\nReading SQL from stdin (close statement with \\\";\\\", quit; or exit; to quit)\");
} else {
statement = pm.parse(new StringReader(sql));

// Only select statements are accepted.
if (statement instanceof Select) {
Select selectStatement = (Select) statement;

System.out.println(sql); // Display the statement.

ColumnsTablesNamesFinder explorer = new ColumnsTablesNamesFinder(selectStatement);

// Display table names, if any.
List<String> tables = explorer.getTableList();
if (tables.size() > 0) {
System.out.println(\"\\nTable list follows:\");

for (Iterator iter = tables.iterator(); iter.hasNext();)
System.out.println(iter.next());
}

// Display column names, if any.
List<String> columns = explorer.getColumnList();
if (columns.size() > 0) {
System.out.println(\"\\nColumn list follows:\");

for (Iterator iter = columns.iterator(); iter.hasNext();)
System.out.println(iter.next());
}
}
sql = \"\";
}
}

} catch(Exception e) {
e.printStackTrace();
}

System.out.println(\"\\nBye\");
}
}

Howto get query columns

Forums

Help

Howto get query columns

Howto get query columns

Forums

Help

Howto get query columns document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Howto get query columns