Last active
July 9, 2024 10:58
-
-
Save piyusht007/d3946045caa60abd839d4355f79624b5 to your computer and use it in GitHub Desktop.
SQL parsing in java using Apache-Calcite SQL parser.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class BaseQueryValidator { | |
private static List<String> extractTableAliases(SqlNode node) { | |
final List<String> tables = new ArrayList<>(); | |
// If order by comes in the query. | |
if (node.getKind().equals(SqlKind.ORDER_BY)) { | |
// Retrieve exact select. | |
node = ((SqlSelect) ((SqlOrderBy) node).query).getFrom(); | |
} else { | |
node = ((SqlSelect) node).getFrom(); | |
} | |
if (node == null) { | |
return tables; | |
} | |
// Case when only 1 data set in the query. | |
if (node.getKind().equals(SqlKind.AS)) { | |
tables.add(((SqlBasicCall) node).operand(1).toString()); | |
return tables; | |
} | |
// Case when there are more than 1 data sets in the query. | |
if (node.getKind().equals(SqlKind.JOIN)) { | |
final SqlJoin from = (SqlJoin) node; | |
// Case when only 2 data sets are in the query. | |
if (from.getLeft().getKind().equals(SqlKind.AS)) { | |
tables.add(((SqlBasicCall) from.getLeft()).operand(1).toString()); | |
} else { | |
// Case when more than 2 data sets are in the query. | |
SqlJoin left = (SqlJoin) from.getLeft(); | |
// Traverse until we get a AS. | |
while (!left.getLeft().getKind().equals(SqlKind.AS)) { | |
tables.add(((SqlBasicCall) left.getRight()).operand(1).toString()); | |
left = (SqlJoin) left.getLeft(); | |
} | |
tables.add(((SqlBasicCall) left.getLeft()).operand(1).toString()); | |
tables.add(((SqlBasicCall) left.getRight()).operand(1).toString()); | |
} | |
tables.add(((SqlBasicCall) from.getRight()).operand(1).toString()); | |
return tables; | |
} | |
return tables; | |
} | |
private static Map<String, String> extractWhereClauses(SqlNode node) { | |
final Map<String, String> tableToPlaceHolder = new HashMap<>(); | |
// If order by comes in the query. | |
if (node.getKind().equals(SqlKind.ORDER_BY)) { | |
// Retrieve exact select. | |
node = ((SqlOrderBy) node).query; | |
} | |
if (node == null) { | |
return tableToPlaceHolder; | |
} | |
final SqlBasicCall where = (SqlBasicCall) ((SqlSelect) node).getWhere(); | |
if (where != null) { | |
// Case when there is only 1 where clause | |
if (where.operand(0).getKind().equals(SqlKind.IDENTIFIER) | |
&& where.operand(1).getKind().equals(SqlKind.LITERAL)) { | |
tableToPlaceHolder.put(where.operand(0).toString(), | |
where.operand(1).toString()); | |
return tableToPlaceHolder; | |
} | |
final SqlBasicCall sqlBasicCallRight = where.operand(1); | |
SqlBasicCall sqlBasicCallLeft = where.operand(0); | |
// Iterate over left until we get a pair of identifier and literal. | |
while (!sqlBasicCallLeft.operand(0).getKind().equals(SqlKind.IDENTIFIER) | |
&& !sqlBasicCallLeft.operand(1).getKind().equals(SqlKind.LITERAL)) { | |
tableToPlaceHolder.put(((SqlBasicCall) sqlBasicCallLeft.operand(1)).operand(0).toString(), | |
((SqlBasicCall) sqlBasicCallLeft.operand(1)).operand(1).toString()); | |
sqlBasicCallLeft = sqlBasicCallLeft.operand(0); // Move to next where condition. | |
} | |
tableToPlaceHolder.put(sqlBasicCallLeft.operand(0).toString(), | |
sqlBasicCallLeft.operand(1).toString()); | |
tableToPlaceHolder.put(sqlBasicCallRight.operand(0).toString(), | |
sqlBasicCallRight.operand(1).toString()); | |
return tableToPlaceHolder; | |
} | |
return tableToPlaceHolder; | |
} | |
public static void main(String[] args) throws SqlParseException { | |
final String query = "SELECT e.first_name AS FirstName, s.salary AS Salary from employee AS e join salary AS s on e.emp_id=s.emp_id where e.organization = 'Tesla' and s.organization = 'Tesla'"; | |
final SqlParser parser = SqlParser.create(baseQuery); | |
final SqlNode sqlNode = parser.parseQuery(); | |
final SqlSelect sqlSelect = (SqlSelect) sqlNode; | |
final SqlJoin from = (SqlJoin) sqlSelect.getFrom(); | |
// Extract table names/data sets, For above SQL query : [e, s] | |
final List<String> tables = extractTableNames(from); | |
// Extract where clauses, For above SQL query : [e.organization -> 'Tesla', s.organization -> 'Tesla'] | |
final Map<String, String> whereClauses = extractWhereClauses(sqlSelect); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
我这报类型转换错误...