Deconstruct, Modify, Rebuild an SQL statement(Java version, part 2)
\npart 2, use low level APIs<\/p>\n
General sql parser provides some low level APIs to allow user re-construct any elements of a parsed SQL statement. Here are 2 samples illustrate how to rebuild SQL statement in different cases:<\/p>\n
We need to add a new reference column “CEL_NEWID” after “CEL_COD”
\nin this sql:<\/p>\n
\r\nALTER TABLE P_CAP \r\nADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL \r\nFOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD);\r\n<\/pre>\nwhich will turn sql into this one:<\/p>\n
\r\nALTER TABLE P_CAP \r\nADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL \r\nFOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD,CEL_NEWID);\r\n<\/pre>\nSteps how to achieve this:<\/p>\n
1. Find the node or token that new sql element will be appended.<\/p>\n
2. set the text of this node to include new value<\/p>\n
Here is the Java code:<\/p>\n
\r\n TGSqlParser lcparser = new TGSqlParser(EDbVendor.dbvoracle);\r\n lcparser.sqltext = \"ALTER TABLE P_CAP \\n\" +\r\n \"ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL \\n\" +\r\n \"FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD);\";\r\n\r\n TAlterTableStatement at = (TAlterTableStatement) lcparser.sqlstatements.get(0);\r\n TAlterTableOption alterTableOption = at.getAlterTableOptionList().getAlterTableOption(0);\r\n TConstraint constraint = alterTableOption.getConstraintList().getConstraint(0);\r\n assertTrue(constraint.getConstraint_type() == EConstraintType.foreign_key);\r\n TObjectName refColumn = constraint.getReferencedColumnList().getObjectName(0);\r\n refColumn.setString(refColumn.toString()+\",CEL_NEWID\");\r\n System.out.println(at.toString());\r\n<\/pre>\nCase 2:<\/h2>\n
Add a new where clause in a select statement.<\/p>\n
original SQL:<\/p>\n
SELECT * FROM TABLE_X<\/pre>\nNew SQL:<\/p>\n
SELECT * FROM TABLE_X where f > 0<\/pre>\nSteps how to achieve this:<\/p>\n
1. Find the node or token that new sql element will be appended.<\/p>\n
2. create a new node and set text of this node.<\/p>\n
3. append all tokens of this new created node after existing node.<\/p>\n
4. adjust all end tokens of existing node if necessary.<\/p>\n
Here is the Java code:<\/p>\n
\r\n TGSqlParser lcparser = new TGSqlParser(EDbVendor.dbvoracle);\r\n lcparser.sqltext = \"SELECT * FROM TABLE_X\";\r\n assertTrue(lcparser.parse() == 0);\r\n\r\n \/\/step 1\r\n TSelectSqlStatement select = (TSelectSqlStatement)lcparser.sqlstatements.get(0);\r\n TSourceToken prevToken = select.joins.getEndToken();\r\n\r\n \/\/step 2 \r\n TDummy dummy = new TDummy();\r\n dummy.setGsqlparser(lcparser);\r\n dummy.setString(\" where f > 0\");\r\n \r\n \/\/step 3 \r\ndummy.addAllMyTokensToTokenList(prevToken.container,prevToken.posinlist + 1 );\r\n\r\n \/\/step 4\r\n for(int i=0;i <prevToken.getNodesEndWithThisToken().size();i++){\r\n TParseTreeNode node = prevToken.getNodesEndWithThisToken().getElement(i);\r\n if (!((node instanceof TJoinList)\r\n ||(node instanceof TJoin)\r\n ||(node instanceof TJoinItemList)\r\n ||(node instanceof TJoinItem)\r\n ))\r\n {\r\n \/\/ change all end token of parse tree node except the table from join clause\r\n node.setEndToken(dummy.getEndToken());\r\n }\r\n }\r\n\r\n System.out.println(select.toString());\r\n\r\n<\/pre>\n