The below code snippet and description helps on how to use the Triniti SQL Parser API to split an SQL into multiple clauses like Select, From, Where, Group By, Having, Starts With, Connect by Order by... etc.
This helps the developers to use the Triniti SQL Parser with all its available fields.
TrinitiSQLParser sqlParser = new TrinitiSQLParser();
TrinitiSQLQuery sqlQuery = sqlParser.parseSQL("SELECT APP.APPLICATION_ID ID, APP.APPLICATION_NAME NAME FROM T_GPS_APPLICATIONS APP WHERE APP.APPLICATION_ID > 1000 AND UPPER(APP.VISIBLE_FLAG) = 'TRUE' AND (APP.APPLICATION_ID > 1001 OR UPPER(APP.VISIBLE_FLAG) <> 'FALSE' AND APP.CREATION_DATE BETWEEN SYSDATE - 100 AND SYSDATE - 1) AND APP.APPLICATION_ID NOT IN (1,100) ORDER BY APP.APPLICATION_ID DESC");
The API can validate the SQL to check if the SQL contains SELECT and FROM clause by using below method. If the SQL does not contain those clauses, it returns false.
boolean isValidSQL = sqlQuery.isValidSQL();
The API returns the error message if the validation fails in the above step.
String errorMessage = sqlQuery.getInvalidSQLMessage());
The API returns a list of TrinitiSQLQuery instances wherein each represents an SQL query separated by the SET operators(Ex : UNION ALL, UNION, MINUS, INTERSECT.... etc). If there are no set operators used, it returns single SQL query instance.
List<TrinitiSQLQuery> sqlsList = sqlQuery.getTrinitiSQLQueriesList();
Let us take an example of single TrinitiSQLQuery instance
TrinitiSQLQuery eachSQLQuery = (TrinitiSQLQuery) sqlsList.get(eachSQLIndex);
Below logic is to specify to include the other clauses following WHERE caluse, in WHERE clause itself
eachSQLQuery.setIncludeFurtherClausesInWhereClause(true);
If this is set as false, the WHERE clause will not contains the other clauses in it.
We can extract different clause from the TrinitiSQLQuery instance.
System.out.println("SELECT : " + eachSQLQuery.getSelectClause());
SELECT APP.APPLICATION_ID ID , APP.APPLICATION_NAME NAME
System.out.println("FROM : " + eachSQLQuery.getFromClause());
FROM T_GPS_APPLICATIONS APP
System.out.println("WHERE : " + eachSQLQuery.getWhereClause());
WHERE APP.APPLICATION_ID > 1000 AND UPPER ( APP.VISIBLE_FLAG ) = 'TRUE' AND ( APP.APPLICATION_ID > 1001 OR UPPER ( APP.VISIBLE_FLAG ) <> 'FALSE' ) AND APP.CREATION_DATE BETWEEN SYSDATE - 100 AND SYSDATE - 1 AND APP.APPLICATION_ID NOT IN ( 1 , 100 ) ORDER BY APP.APPLICATION_ID DESC
System.out.println("START WITH : " + eachSQLQuery.getStartWithClause());
System.out.println("CONNECT BY : " + eachSQLQuery.getConnectByClause());
System.out.println("GROUP BY : " + eachSQLQuery.getGroupByClause());
System.out.println("HAVING : " + eachSQLQuery.getHavingClause());
System.out.println("ORDER BY : " + eachSQLQuery.getOrderByClause());
ORDER BY APP.APPLICATION_ID DESC
The API returns the list of Select Clause elements(columns) as shown below.
System.out.println("SELECT LIST : " + eachSQLQuery.getSelectColumnsList());
[APP.APPLICATION_ID, APP.APPLICATION_NAME]
The API returns the list of aliases of Select Clause elements(columns) as shown below.
System.out.println("ALIAS LIST : " + eachSQLQuery.getSelectColumnsAliasList());
[ID, NAME]
The API returns the list of From clause elements(Tables, Select Queries... etc) as shown below.
System.out.println("FROM LIST : " + eachSQLQuery.getFromSourcesList());
[T_GPS_APPLICATIONS]
The API returns the list of aliases of From clause elements(Tables, Select Queries... etc) as shown below.
System.out.println("FROM ALIAS LIST : " + eachSQLQuery.getFromSourcesAliasList());
[APP]
The API returns the list of where conditions with details information like linking condition(WHERE/ AND/ OR, Left Column Name, NOT Operator, Condition Operator, Right Column Name as shown below.
System.out.println("Where Conditions List : " + eachSQLQuery.getWhereConditionsList());
[
{linkingCondition=WHERE, leftColumnName=APP.APPLICATION_ID, conditionOperator=>, rightColumnName=1000},
{linkingCondition=AND, leftColumnName=UPPER ( APP.VISIBLE_FLAG ), conditionOperator==, rightColumnName='TRUE'},
{linkingCondition=AND, leftColumnName=APP.APPLICATION_ID, conditionOperator=>, rightColumnName=1001},
{linkingCondition=OR, leftColumnName=UPPER ( APP.VISIBLE_FLAG ), conditionOperator=<>, rightColumnName='FALSE'},
{linkingCondition=AND, leftColumnName=APP.CREATION_DATE, conditionOperator=BETWEEN, rightColumnName1=SYSDATE - 100, rightColumnName2=SYSDATE - 1},
{linkingCondition=AND, leftColumnName=APP.APPLICATION_ID, notOperator=NOT, conditionOperator=IN, defaultValue=( 1 , 100 )},
{linkingCondition=ORDER BY, leftColumnName=APP.APPLICATION_ID, conditionOperator=DESC}
]
The API returns the list of grouped condition details. In the above SQL, there is a group of conditions(AND (APP.APPLICATION_ID > 1001 OR UPPER(APP.VISIBLE_FLAG)<>'FALSE'))
These 3rd and 4th conditions are grouped in the SQLs. This information can be retrieved as shown below.
System.out.println("Where Conditions List : " + eachSQLQuery.getGroupedConditionDetailsList());
[{groupNo=0, leftSubGroup=2, rightSubGroup=3}]
These 3rd and 4th conditions are indicated with index number 2 and 3 in the above result.