Amaresh Narayanan Amaresh Narayanan - 3 months ago 14
Java Question

Get Table names and Query types from sql statement in JAVA

I am new to jsqlparser and trying to parse the sql statement to get table name and its query type (In Java).


For eg1.

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany'


From this query, I want to get the table name
Suppliers
and its query type
SELECT
.

Similarly, table name
Suppliers
and its query type
INSERT
.


For eg2.

UPDATE CUSTOMERS SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )


From this query, I want to get the table name
CUSTOMERS_BKP
and its query type
SELECT
.

Similarly, table name
CUSTOMERS
and its query type
UPDATE
.


For eg3.

UPDATE CUSTOMERS SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS WHERE AGE >= 27 )


(I knew that this query can be simplified, but for example I have pasted here.)

From this query, I want to get the table name
CUSTOMERS
and its query type
SELECT
.

Similarly, table name
CUSTOMERS
and its query type
UPDATE
.
Note: here table names are same but different query type.

Same way, I want to get the table name and its query type for any complex sql query.


Could you please help me on this?


Please comment, if you want me to provide any details.

Answer

The question you filed is a tricky one, because JSqlParsers API lacks some extension points for this. To correct this, I created some issues at github. So here is a little sample to get this running. The output is:

eg1
INSERT - Customers
SELECT - [Suppliers]
eg2
UPDATE - [CUSTOMERS]
SELECT - [CUSTOMERS_BKP]
eg3
UPDATE - [CUSTOMERS]
SELECT - [CUSTOMERS]

Your main tool here is the TablesNamesFinder of JSqlParser, that gets all table names of some SQL. Now we have to modify it, to get all select - statement based table names in a separate way. This is done using TablesNamesFinderExt, which does as well correct the subselect problem using an Insert statement.

In short the finder recognizes subselects and sets a global flag inSelect to fill the separate mySelectTableList for subsequent calls of visit(Table ...).

import java.util.ArrayList;
import java.util.List;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;

public class SimpleSqlParserTableNames2 {

    public static void main(String args[]) throws JSQLParserException {
        System.out.println("eg1");
        Insert insert = (Insert)CCJSqlParserUtil.parse("INSERT INTO Customers (CustomerName, Country) SELECT SupplierName, Country FROM Suppliers WHERE Country='Germany'");
        TablesNamesFinderExt finder = new TablesNamesFinderExt();

        System.out.println("INSERT - " + insert.getTable());
        finder.getTableList(insert.getSelect());
        System.out.println("SELECT - " + finder.getSelectTableList());

        System.out.println("eg2");
        Update update = (Update)CCJSqlParserUtil.parse("UPDATE CUSTOMERS SET SALARY = SALARY * 0.25\n" +
                    "  WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )");
        finder = new TablesNamesFinderExt();
        System.out.println("UPDATE - " + update.getTables());
        finder.getTableList(update);
        System.out.println("SELECT - " + finder.getSelectTableList());

        System.out.println("eg3");
        update = (Update)CCJSqlParserUtil.parse("UPDATE CUSTOMERS SET SALARY = SALARY * 0.25\n" +
                    "WHERE AGE IN (SELECT AGE FROM CUSTOMERS WHERE AGE >= 27 )");
        finder = new TablesNamesFinderExt();
        System.out.println("UPDATE - " + update.getTables());
        finder.getTableList(update);
        System.out.println("SELECT - " + finder.getSelectTableList());
    }

    static class TablesNamesFinderExt extends TablesNamesFinder {
        List<String> mySelectTableList = new ArrayList<>();
        boolean inSelect = true;
        /**
         * To solve JSqlParsers Problem in getting tablenames from subselect using an Insert
         * statement.
         *
         * @param insert
         * @return
         */
        @Override
        public List<String> getTableList(Insert insert) {
            List<String> list = super.getTableList(insert);
            if (insert.getSelect() != null) {
                insert.getSelect().getSelectBody().accept(this);
            }
            return list;
        }

        @Override
        public void visit(SubSelect subSelect) {
            inSelect = true;
            super.visit(subSelect);
        }

        @Override
        public void visit(Table tableName) {
            super.visit(tableName); 
            if (inSelect && !mySelectTableList.contains(tableName.getFullyQualifiedName()))
                mySelectTableList.add(tableName.getFullyQualifiedName());
        }

        public List<String> getSelectTableList() {
            return mySelectTableList;
        }

    }
}