Get Table names and Query types from sql statement in JAVA

Question

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 typeINSERT.

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 typeUPDATE.

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 typeUPDATE.
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 1

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

    }
}

Answer 2

If you just need table names form SQLs, you need an ultra light, ultra fast libray (full SQL parser would be over kill)

Just add the following in your pom

<dependency>
    <groupId>com.github.mnadeem</groupId>
    <artifactId>sql-table-name-parser</artifactId>
    <version>0.0.1</version>
</dependency>

And use the following instruction

new TableNameParser(sql).tables()

For more details refer the project

Disclaimer : I am the owner