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