Alias Field

[baczek00]

Hi
I know how to retrieve a list of fields from a SQL query, but how to get the alias of these fields?

Regards

[wumpz]

Aliases are stored in three different places in the parsers result. 1: Table - object 2: SelectExpressionItem object 3: WithItem object (I think it is some kind of alias). So using the TableNamesFinder you could adapt it to find all aliases.

[baczek00]

Thanks for the suggestion, it works.

[baczek00]

JSQLParser supports allias functin?

example:
SELECT Count(*) as aliastest FROM tabeltest

Regards

[wumpz]

It should. count(*) would be a SelectExpressionItem. Are there any parse exceptions?

[baczek00]

Hi,
I have code:

public class NaviSoftRealMeta implements Serializable {

    private String Table;
    private String FieldName;
    private String AliasFieldName;

    public void setTable(String Table) {
        this.Table = Table;
    }

    public void setFieldName(String FieldName) {
        this.FieldName = FieldName;
    }

    public void setAliasFieldName(String AliasFieldName) {
        this.AliasFieldName = AliasFieldName;
    }

    public String getTable() {
        return Table;
    }

    public String getFieldName() {
        return FieldName;
    }

    public String getAliasFieldName() {
        return AliasFieldName;
    }

}

and class

public class JSQLTools implements SelectVisitor, FromItemVisitor, ExpressionVisitor, ItemsListVisitor, SelectItemVisitor {

    private NaviSoftRealMeta meta;
    private List tables;
    private List columns;
    private String nameColumn;
    private List<NaviSoftRealMeta> metaDataColumns;
    private Select select;
    private Connection c;

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

    public List getColumnList(Select select) throws SQLException {
        columns = new ArrayList<>();
        c = FirebirdConnectionDataSource.getConnection();
        this.select = select;
        select.getSelectBody().accept(this);
        c.close();
        return columns;

    }

    public List<NaviSoftRealMeta> getColumnListTest(Select select) throws SQLException {
        metaDataColumns = new ArrayList<NaviSoftRealMeta>();
        select.getSelectBody().accept(this);
        return metaDataColumns;
    }

    public String getDeleteSQL(Delete delete) {
        return delete.toString();

    }

    @Override
    public void visit(PlainSelect plainSelect) {
        if (plainSelect.getSelectItems() != null) {
            for (Iterator SIt = plainSelect.getSelectItems().iterator(); SIt.hasNext();) {
                SelectItem SI = (SelectItem) SIt.next();
                SI.accept(this);
            }
        }
        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 (columns == null || metaDataColumns == null) {
            if (plainSelect.getWhere() != null) {
                plainSelect.getWhere().accept(this);
            }
        }
    }

    @Override
    public void visit(Table tableName) {
        if (!(tables == null)) {
            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) {
        if (!(columns == null)) {
            columns.add(tableColumn.getWholeColumnName());
        }
        if (!(metaDataColumns == null)) {
            meta = new NaviSoftRealMeta();
            nameColumn = tableColumn.getColumnName();
            meta.setTable(tableColumn.getTable().getName());
            meta.setFieldName(nameColumn);
        }
    }

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

    public 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) {
    }

    @Override
    public void visit(TimeValue timeValue) {
    }

    @Override
    public void visit(CaseExpression caseExpression) {
    }

    @Override
    public void visit(WhenClause whenClause) {
    }

    @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) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(Matches mtchs) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(BitwiseAnd ba) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(BitwiseOr bo) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(BitwiseXor bx) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(AllColumns ac) {
        if (!(columns == null)) {
            List<NaviSoftFieldConfig> configfield;
            List<NaviSoftField> configColumnList = new ArrayList<>();
            Integer Index = 0;
            Boolean Exists = false;
            try {
                configfield = NaviSoftToolsServer.getMetaDataTemp(c, select.getSelectBody().toString());
                for (int i = 0; i < configfield.size(); i++) {
                    if (configColumnList.isEmpty()) {
                        NaviSoftField NSF = new NaviSoftField();
                        NSF.setFieldName(configfield.get(i).getFieldName());
                        NSF.setIndex(Index);
                        configColumnList.add(NSF);
                        columns.add(NSF.getFieldName());
                        Exists = true;
                    } else {
                        Exists = false;
                        Index = 0;
                        for (int j = 0; j < configColumnList.size(); j++) {
                            if (configColumnList.get(j).getFieldName().equalsIgnoreCase(configfield.get(i).getFieldName())) {
                                Index = configColumnList.get(j).getIndex();
                                configColumnList.get(j).setIndex(Index + 1);
                                Exists = true;
                                columns.add(configColumnList.get(j).getFieldName() + configColumnList.get(j).getIndex());

                            }
                        }
                    }
                    if (!Exists) {
                        NaviSoftField NSF = new NaviSoftField();
                        NSF.setFieldName(configfield.get(i).getFieldName());
                        NSF.setIndex(Index);
                        configColumnList.add(NSF);
                        columns.add(NSF.getFieldName());
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(JSQLTools.class.getName()).log(Level.SEVERE, null, ex);
            } catch (JSQLParserException ex) {
                Logger.getLogger(JSQLTools.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public void visit(AllTableColumns atc) {
        atc.getTable().accept(this);


    }

    @Override
    public void visit(SelectExpressionItem sei) {
        sei.getExpression().accept(this);
        if (!(metaDataColumns == null)) {
            if (sei.getAlias() == null) {
                meta.setAliasFieldName(nameColumn);
            } else {
                meta.setAliasFieldName(sei.getAlias());
            }
            metaDataColumns.add(meta);
        }
    }

    @Override
    public void visit(SetOperationList sol) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(WithItem wi) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(LateralSubSelect lss) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(ValuesList vl) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(CastExpression ce) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(Modulo modulo) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(AnalyticExpression ae) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(ExtractExpression ee) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void visit(MultiExpressionList mel) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}

My method of retrieving the alias:

 JSQLParserStatement JSQLPS = new JSQLParserStatement(sSQL.toString());
        JSQLTools jsqltools = new JSQLTools();
        List<NaviSoftRealMeta> fieldlist = jsqltools.getColumnListTest(JSQLPS.getSelect());
        for (int i = 0; i < fieldlist.size(); i++) {
            System.out.println(fieldlist.get(i).getFieldName() + " " + fieldlist.get(i).getAliasFieldName());
        }
    }

This query gives good results:
String sSQL = "SELECT imie as a, nazwisko as b FROM osoby"

The second query I give exception
String sSQL = "SELECT Count(*) as aliastest FROM osoby"

Exception in thread "main" java.lang.NullPointerException
    at testconnectionpool.JSQLTools.visit(JSQLTools.java:430)
    at net.sf.jsqlparser.statement.select.SelectExpressionItem.accept(SelectExpressionItem.java:52)
    at testconnectionpool.JSQLTools.visit(JSQLTools.java:129)
    at net.sf.jsqlparser.statement.select.PlainSelect.accept(PlainSelect.java:105)
    at testconnectionpool.JSQLTools.getColumnListTest(JSQLTools.java:115)
    at testconnectionpool.TestConnectionPool.main(TestConnectionPool.java:26)
Java Result: 1

Regards

[wumpz]

The statement is parsed successfully. What happens in testconnectionpool.JSQLTools.visit(JSQLTools.java:430). That is not part of JSqlParser.

[baczek00]

The statement is parsed successfully. What happens in testconnectionpool.JSQLTools.visit (JSQLTools.java: 430). That is not part of JSqlParser.

JSQLParser class is in the package "testconnectionpool"

The name "testconnectionpool" is a package. Line 430 is this method:

@Override
    public void visit(SelectExpressionItem sei) {
        sei.getExpression().accept(this);
        if (!(metaDataColumns == null)) {
            if (sei.getAlias() == null) {
                meta.setAliasFieldName(nameColumn);
            } else {
                meta.setAliasFieldName(sei.getAlias());  ----> Here is an excerpt from line 430
            }
            metaDataColumns.add(meta);
        }
    }

Regards

[baczek00]

Amendment:
JSQLTools class is in the package "testconnectionpool"

[wumpz]

If (looking at your sourcecode) "sei" is null then the NPE should be thrown at the first line of your method (sei.getExp..). I think your variable "meta" is null.

[baczek00]

Hi there,
You were right the variable "meta" was null. Thanks for your help.

Regards