How to retrieve table and column names from SQl using JSQLPARSE

Question

I am using JSQLPARSER for the first time. I have some SQL files which come dynamically, i need to read table and column names from that SQL. After lot of googling I tried with JSQLPARSER. I am trying to read column names from the file but I am unable to read column names due to expression, please can any one correct my code where I went wrong. I am getting CLASSCASTEXCEPTION code:

public static void main(String[] args) throws JSQLParserException
    {
        // TODO Auto-generated method stub
         String statement="SELECT LOCATION_D.REGION_NAME, LOCATION_D.AREA_NAME, COUNT(DISTINCT INCIDENT_FACT.TICKET_ID) FROM LOCATION_D, INCIDENT_FACT WHERE ( LOCATION_D.LOCATION_SK=INCIDENT_FACT.LOCATION_SK ) GROUP BY LOCATION_D.REGION_NAME, LOCATION_D.AREA_NAME"; 
         CCJSqlParserManager parserManager = new CCJSqlParserManager();
         Select select=(Select) parserManager.parse(new StringReader(statement));

         PlainSelect plain=(PlainSelect)select.getSelectBody();     
         List selectitems=plain.getSelectItems();
         System.out.println(selectitems.size());
         for(int i=0;i<selectitems.size();i++)
         {
            Expression expression=((SelectExpressionItem) selectitems.get(i)).getExpression();  
            System.out.println("Expression:-"+expression);
            Column col=(Column)expression;
            System.out.println(col.getTable()+","+col.getColumnName());      
         }
    }

Shammer

Answer 1

public class TablesNamesFinder 
    implements SelectVisitor, FromItemVisitor, ExpressionVisitor, 
    ItemsListVisitor
{
    private List tables;

    public List getTableList(Select select) {
        tables = new ArrayList();
        select.getSelectBody().accept(this);
        return tables;
    }

    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);

    }

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

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

    public void visit(SubSelect subSelect) {
        subSelect.getSelectBody().accept(this);
    }
//and other visit implement ....

}

Hydrology

Answer 2

The third value you request with your select is not a column but a function. Therefore JSqlParser delivers an expression that you cannot cast to Column.

Riverhead

Answer 3

As wumpz pointed out the issue, function in the list cannot be casted to a column. Same is the case with my requirement and this code helped in solving it. Hope this helps. Expression can be verified if it is a column or a function and can be used accordingly.

        for(int i=0;i<selectitems.size();i++)
        {
           Expression expression=((SelectExpressionItem) selectitems.get(i)).getExpression();  
           System.out.println("Expression:-"+expression);
           if( expression instanceof Column)
           {
               Column col=(Column)expression;
               System.out.println(col.getTable()+","+col.getColumnName());      

           }
           else if (expression instanceof Function)
           {
               Function function = (Function) expression;
               System.out.println(function.getAttribute()+","+function.getName()+""+function.getParameters());      

           }

        }

Overwrought