{"id":1565,"date":"2013-05-25T04:47:35","date_gmt":"2013-05-25T09:47:35","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1565"},"modified":"2013-05-25T04:49:06","modified_gmt":"2013-05-25T09:49:06","slug":"deconstruct-modify-rebuild-an-sql-statementjava-version-part-2","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-overview\/deconstruct-modify-rebuild-an-sql-statementjava-version-part-2\/","title":{"rendered":"Deconstruct, Modify, Rebuild an SQL statement(Java version, part 2)"},"content":{"rendered":"

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

## Case 1:<\/h2>\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>\n1. Find the node or token that new sql element will be appended.<\/p>\n2. set the text of this node to include new value<\/p>\nHere 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>\nAdd a new where clause in a select statement.<\/p>\noriginal SQL:<\/p>\nSELECT * FROM TABLE_X<\/pre>\nNew SQL:<\/p>\nSELECT * FROM TABLE_X where f > 0<\/pre>\nSteps how to achieve this:<\/p>\n1. Find the node or token that new sql element will be appended.<\/p>\n2. create a new node and set text of this node.<\/p>\n3. append all tokens of this new created node after existing node.<\/p>\n4. adjust all end tokens of existing node if necessary.<\/p>\nHere 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
    \nDeconstruct, Modify, Rebuild an SQL statement(Java version, part 1)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"Deconstruct, Modify, Rebuild an SQL statement(Java version, part 2) part 2, use low level APIs 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: Case 1: We need to add a new reference column “CEL_NEWID” after “CEL_COD” in this sql: ALTER TABLE P_CAP ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD); which will turn sql into this one: ALTER TABLE P_CAP ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD,CEL_NEWID); Steps how to achieve this: 1.\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":298,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n