{"id":737,"date":"2011-02-08T07:34:19","date_gmt":"2011-02-08T12:34:19","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=737"},"modified":"2013-07-13T08:23:51","modified_gmt":"2013-07-13T13:23:51","slug":"deconstruct-modify-rebuild-an-sql-statementjava-version","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/","title":{"rendered":"Deconstruct, Modify, Rebuild an SQL statement(Java version)"},"content":{"rendered":"

This article shows how to use General SQL Parser to split an SQL statement into it’s constituent parts and then add\/remove select list, add more criteria, an order by clause, etc. <\/p>\n

This feature is quite useful if you want to reconstruct an existing SQL statement dynamically in your program.<\/p>\n

Take this SQL for example:<\/p>\n

####

####

####

    \r\nSELECT A as A_Alias, B AS B_Alias FROM TABLE_X\r\n<\/pre>\nIf you need to remove the second column “B AS B_Alias” from the select list, just do something like this:<\/p>\n\r\ncolumns.removeResultColumn(1); \/\/ 0 is the first column, and 1 is the second column\r\n<\/pre>\nYou will get this new SQL(the , was removed automatically):<\/p>\n\r\nSELECT A as A_Alias FROM TABLE_X\r\n<\/pre>\nOK, now you want to add a new column, let say “x”, just do something like this:<\/p>\n\r\ncolumns.addResultColumn(\"x\");\r\n<\/pre>\nThis time, the new SQL become this:<\/p>\n\r\nSELECT A as A_Alias,x  FROM TABLE_X\r\n<\/pre>\nReplace a column<\/strong><\/h4>\noriginal SQL:<\/p>\n\r\nSELECT * FROM TABLE_X\r\n<\/pre>\nNew SQL:<\/p>\n\r\nSELECT TABLE_X.* FROM TABLE_X\r\n<\/pre>\nJava code use to replace a column name which is reasonably straightforward.:<\/p>\n\r\nTSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);\r\nTResultColumnList columns = select.getResultColumnList();\r\nif (columns.getResultColumn(0).toString().equalsIgnoreCase(\"*\")){\r\n    columns.getResultColumn(0).setString(\"TABLE_X.*\");\r\n}\r\n<\/pre>\nAdd criteria (where clause)<\/strong><\/h4>\noriginal SQL:<\/p>\n\r\nSELECT * FROM TABLE_X where f > 0\r\n<\/pre>\nNew SQL:<\/p>\n\r\nSELECT * FROM TABLE_X where f > 0 and c>1\r\n<\/pre>\nJava code use to add criteria :<\/p>\n\r\nassertTrue(parser.parse() == 0);\r\nTSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);\r\nselect.addWhereClause(\"c>1\");\r\n<\/pre>\nHere is a more advanced demo illustrates how to modify\/remove user defined variable in where condition<\/a>.<\/p>\nAdd Order by clause<\/strong><\/h4>\noriginal SQL:<\/p>\n\r\nSELECT * FROM TABLE_X order by a\r\n<\/pre>\nNew SQL:<\/p>\n\r\nSELECT * FROM TABLE_X order by a,b\r\n<\/pre>\nJava code use to add order by item:<\/p>\n\r\nassertTrue(parser.parse() == 0);\r\nTSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);\r\nselect.getOrderbyClause().addOrderByItem(\"b\");\r\n<\/pre>\nHere is a Java demo<\/a> with more samples. ANSI C demo<\/a><\/p>\n
    \nDeconstruct, Modify, Rebuild an SQL statement(Java version, part 2), use low level APIs<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"This article shows how to use General SQL Parser to split an SQL statement into it’s constituent parts and then add\/remove select list, add more criteria, an order by clause, etc. This feature is quite useful if you want to reconstruct an existing SQL statement dynamically in your program. Take this SQL for example: SELECT A as A_Alias, B AS B_Alias FROM TABLE_X If you need to remove the second column “B AS B_Alias” from the select list, just do something like this: columns.removeResultColumn(1); \/\/ 0 is the first column, and 1 is the second column You will get this\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"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