How Can I get multiple Sub queries from "From Clause" using JSQLparser ?

Question

I want to get all the Sub-queries in a from clause but When I have used

 FromItem fromItems = plainSelect.getFromItem();

It just retrieves the first sub query in the from clause and when I used

 Expression exp = plainSelect.getJoins().get(i);

It retrieve the tables in the from statement not the Sub queries.

Okay, If we have the following Example: Select [columns] from Table A, (Subselect) B , (Subselect) C I want to know what exactly in the from clause because in this case if I used the getfromItem (); it will return Table A but will not get the other Subselects, In other words I want to List that contains all the elements in the from statment — Amr Azzam, Aug 23 '17 at 22:30

wumpz · Accepted Answer · 2017-08-26T14:29:06.123

So here is a little code snipped. You have to know, that the first table/subselect is stored within the fromItem from your PlainSelect and the followings within the joins. You have to process both. Thats due to the parsing architecture JSqlParser uses.

Within JSqlParser you have two types of traversing the returned object hierarchy: 1. visitor pattern, 2. direct object hierarchy access. I implemented both within this little example.

EDIT: To find hierarchical all subqueries JSqlParser identifies, I included Type 3, which is kind of a hack, since I somehow missused the deparser.

public class SimpleSqlParser10 {
    public static void main(String args[]) throws JSQLParserException {
        String sql = "SELECT * FROM myTable, (select * from myTable2) as data1, (select b from myTable3) as data2";
        Select select = (Select)CCJSqlParserUtil.parse(sql);
        System.out.println(select.toString());


        System.out.println("Type 1: Visitor processing");
        select.getSelectBody().accept(new SelectVisitorAdapter(){
            @Override
            public void visit(PlainSelect plainSelect) {
                plainSelect.getFromItem().accept(fromVisitor);
                if (plainSelect.getJoins()!=null)
                   plainSelect.getJoins().forEach(join -> join.getRightItem().accept(fromVisitor));
            }
        });

        System.out.println("Type 2: simple method calls");
        processFromItem(((PlainSelect)select.getSelectBody()).getFromItem());
        if (((PlainSelect)select.getSelectBody()).getJoins()!=null)
            ((PlainSelect)select.getSelectBody()).getJoins().forEach(join -> processFromItem(join.getRightItem()));


        System.out.println("Type 3: hierarchically process all subselects");
        select.getSelectBody().accept(new SelectDeParser() {
            @Override
            public void visit(SubSelect subSelect) {
                System.out.println("  found subselect=" + subSelect.toString());
                super.visit(subSelect);             }
        });
    }

    private final static FromItemVisitorAdapter fromVisitor = new FromItemVisitorAdapter() {
        @Override
        public void visit(SubSelect subSelect) {
            System.out.println("subselect=" + subSelect);
        }

        @Override
        public void visit(Table table) {
            System.out.println("table=" + table);
        }
    } ;

    private static void processFromItem(FromItem fromItem) {
        System.out.println("fromItem=" + fromItem);
    }
}

This one outputs

SELECT * FROM myTable, (SELECT * FROM myTable2) AS data1, (SELECT b FROM myTable3) AS data2
Type 1: Visitor processing
table=myTable
subselect=(SELECT * FROM myTable2) AS data1
subselect=(SELECT b FROM myTable3) AS data2
Type 2: simple method calls
fromItem=myTable
fromItem=(SELECT * FROM myTable2) AS data1
fromItem=(SELECT b FROM myTable3) AS data2
Type 3: hierarchically process all subselects
  found subselect=(SELECT * FROM myTable2) AS data1
  found subselect=(SELECT b FROM myTable3) AS data2

Your code is excellent with your example but when I used it with this a real one it gives me null pointer exception and I wonder if I can have List which contains the items in the from clause separated in the List. In this link I have added my test query https://regex101.com/r/aZWyZE/1 — Amr Azzam, Aug 25 '17 at 12:08
Updated my answer. The joins list has to be null checked. This was not needed for my example. — wumpz, Aug 25 '17 at 12:49
Yes, you are right about the null checking but as you can see in the example I have given above, we have 5 Sub-queries BASE, WHITE_LIST, SO, HT , ADJ and when I run your code on this example, they are not separated as u it happened with your code — Amr Azzam, Aug 25 '17 at 19:07
In other words your code dealt with the 5 queries as they are the same query — Amr Azzam, Aug 25 '17 at 19:12
I am not sure if I understand you. Give me more time to run your huge sql. — wumpz, Aug 25 '17 at 23:30
My code example does provide all so called level one subselects of your sql. That is how I understood your question. In your large example there is only one "level one" subselect with the alias SAL_FHS_DWH_5050. All queries you mentioned are subselects of this subselect. This huge sql does fit in your example from the start like: Select [columns] from (Subselect) B, where b is SAL_FHS_DWH_5050. Therefore I assume your question and hint is not correct, since my code does exactly what you first requested. — wumpz, Aug 26 '17 at 13:54
Nevertheless I updated my code to find all subselects within a specific sql. Maybe here is a naming problem. If I select data from a table, then this table is not a subselect. — wumpz, Aug 26 '17 at 14:30
By the way. I forgot to mention, you need JSQLPARSER 1.2-snapshot. With your query I needed to fix a bug within the ExpressionVisitorAdapter. — wumpz, Aug 26 '17 at 18:24

How Can I get multiple Sub queries from "From Clause" using JSQLparser ?

1 Answers1