0

I am using jsqlprocessor 4.6 for SQL query parsing.

<!-- https://mvnrepository.com/artifact/com.github.jsqlparser/jsqlparser -->
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.6</version>
</dependency>

There are different select queries and would like to retrieve the list of tables, tables being joined and the join condition, where conditions by table

Query 1:

SELECT * 
  FROM ((((SUB_PR_GRP LEFT OUTER JOIN APP_USER ON (SUB_PR_GRP.PR1_ID = APP_USER.NBR)) 
     LEFT OUTER JOIN APP_USER app_USER ON (SUB_PR_GRP.PR_ID = app_USER.NBR)) 
    LEFT OUTER JOIN APP_USER app_USER_ALIAS_0 ON (SUB_PR_GRP.SPGM_ID = app_USER_ALIAS_0.NBR)) 
    LEFT OUTER JOIN ((PRC_GRP LEFT OUTER JOIN APP_USER app_USER_ALIAS_1 ON (PRC_GRP.DR_ID = app_USER_ALIAS_1.NBR)) 
    LEFT OUTER JOIN APP_USER usr_ALIAS_2 ON (PRC_GRP.PGM_ID = usr_ALIAS_2.NBR)) ON (SUB_PR_GRP.PRC_GRP_ID = PRC_GRP.PRC_GRP_ID)) 
    LEFT OUTER JOIN APP_REQ_LOG PQR ON (APP_REQ_LOG.PRC_GRP_ID = PRC_GRP.PRC_GRP_ID) 
    WHERE (PRC_GRP.PRC_GRP_ID IS NOT NULL) ORDER BY PRC_GRP.NAME DESC

For the above query input, the expected output is:

Left table = SUB_PR_GRP, Right Table = APP_USER, Left column = PR1_ID, Right column = NBR
Left table = SUB_PR_GRP, Right Table = APP_USER, Left column = PR_ID, Right column = NBR
Left table = SUB_PR_GRP, Right Table = APP_USER, Left column = DR_ID, Right column = NBR

Code:

Statement statement = CCJSqlParserUtil.parse(query);
    TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
    Set<String> tableNames = new HashSet<>(tablesNamesFinder.getTableList(statement));
    tableNames.forEach(tableName -> {
        System.out.println("Table Name is ===> "+tableName);
    });

The code below returns where condition, but not the corresponding table name. Also, it doesn't work for the complex table.

public static void processJoinsList(List<Join> joinsList) {
        // Get join columns
        joinsList.forEach(join -> {
            Table rightTable = (Table)join.getRightItem();
            
            System.out.println("RIGHT Table is ====>>>> "+ rightTable.getName());
            
            Collection<Expression> onExpressions = ((Join) join).getOnExpressions();
            onExpressions.forEach(onExpression -> {
                EqualsTo equalsTo = (EqualsTo) onExpression;
                equalsTo.getLeftExpression();
                equalsTo.getRightExpression();
            });
        });
    }

    public static void main(String args[]) throws JSQLParserException {
    
        
        String sqlStr="select * from comp rm, project ra where rm.table_id = ra.table_id and rm.filter_1 = 100 and ra.filter_2 = 'test'";

        Statement statement = CCJSqlParserUtil.parse(sqlStr);
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        Set<String> tableNames = new HashSet<>(tablesNamesFinder.getTableList(statement));
        tableNames.forEach(tableName -> {
            System.out.println("Table Name is ===> "+tableName);
        });
        if (statement instanceof Select) {
            Select select = (Select) statement;
            
            processWithItemsList(select.getWithItemsList());
            SelectBody selectBody = select.getSelectBody();
            
            PlainSelect plainSelect = (PlainSelect)  select.getSelectBody();
            
            List<SelectItem> selectItems = plainSelect.getSelectItems();

            FromItem fromItem = plainSelect.getFromItem();
            if(fromItem instanceof Table) {
                Table table = (Table) plainSelect.getFromItem();
                System.out.println(" FROM table is ===>>> "+table.getName());
            } else if(fromItem instanceof SubJoin) {
                SubJoin subJoin = (SubJoin) plainSelect.getFromItem();
                System.out.println(" FROM table is ===>>> "+subJoin.getAlias());
            }
            
            processJoinsList (plainSelect.getJoins());
            
            
            Expression whereExpression = plainSelect.getWhere();
            whereExpression.accept(new ExpressionVisitorAdapter() {

                @Override
                protected void visitBinaryExpression(BinaryExpression expr) {
                    if (expr instanceof ComparisonOperator) {
                        System.out.println("left=" + expr.getLeftExpression() + "  op=" +  expr.getStringExpression() + "  right=" + expr.getRightExpression());
                    } 
                    super.visitBinaryExpression(expr); 
                }
            });
        }
    }
halfer
  • 19,824
  • 17
  • 99
  • 186
user1578872
  • 7,808
  • 29
  • 108
  • 206

0 Answers0