{"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":"<p>This article shows how to use General SQL Parser to split an SQL statement into it&#8217;s constituent parts and then add\/remove select list, add more criteria, an order by clause, etc. <\/p>\n<p>This feature is quite useful if you want to reconstruct an existing SQL statement dynamically in your program.<\/p>\n<p>Take this SQL for example:<\/p>\n<pre>\r\nSELECT A as A_Alias, B AS B_Alias FROM TABLE_X\r\n<\/pre>\n<p>If you need to remove the second column &#8220;B AS B_Alias&#8221; from the select list, just do something like this:<\/p>\n<pre>\r\ncolumns.removeResultColumn(1); \/\/ 0 is the first column, and 1 is the second column\r\n<\/pre>\n<p>You will get this new SQL(the , was removed automatically):<\/p>\n<pre>\r\nSELECT A as A_Alias FROM TABLE_X\r\n<\/pre>\n<p>OK, now you want to add a new column, let say &#8220;x&#8221;, just do something like this:<\/p>\n<pre>\r\ncolumns.addResultColumn(\"x\");\r\n<\/pre>\n<p>This time, the new SQL become this:<\/p>\n<pre>\r\nSELECT A as A_Alias,x  FROM TABLE_X\r\n<\/pre>\n<h4><strong>Replace a column<\/strong><\/h4>\n<p>original SQL:<\/p>\n<pre>\r\nSELECT * FROM TABLE_X\r\n<\/pre>\n<p>New SQL:<\/p>\n<pre>\r\nSELECT TABLE_X.* FROM TABLE_X\r\n<\/pre>\n<p>Java code use to replace a column name which is reasonably straightforward.:<\/p>\n<pre>\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>\n<h4><strong>Add criteria (where clause)<\/strong><\/h4>\n<p>original SQL:<\/p>\n<pre>\r\nSELECT * FROM TABLE_X where f > 0\r\n<\/pre>\n<p>New SQL:<\/p>\n<pre>\r\nSELECT * FROM TABLE_X where f > 0 and c>1\r\n<\/pre>\n<p>Java code use to add criteria :<\/p>\n<pre>\r\nassertTrue(parser.parse() == 0);\r\nTSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);\r\nselect.addWhereClause(\"c>1\");\r\n<\/pre>\n<p>Here is a more advanced demo illustrates <a href=\"http:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/remove-sql-variables-in-where-clause-without-broken-condition-expression\/\">how to modify\/remove user defined variable in where condition<\/a>.<\/p>\n<h4><strong>Add Order by clause<\/strong><\/h4>\n<p>original SQL:<\/p>\n<pre>\r\nSELECT * FROM TABLE_X order by a\r\n<\/pre>\n<p>New SQL:<\/p>\n<pre>\r\nSELECT * FROM TABLE_X order by a,b\r\n<\/pre>\n<p>Java code use to add order by item:<\/p>\n<pre>\r\nassertTrue(parser.parse() == 0);\r\nTSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);\r\nselect.getOrderbyClause().addOrderByItem(\"b\");\r\n<\/pre>\n<p>Here is a <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/java\/test\/testModifySql.java\">Java demo<\/a> with more samples. <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/c\/modifySqlDemo\/modifySqlDemo.zip\">ANSI C demo<\/a><\/p>\n<p><a href=\"http:\/\/www.dpriver.com\/blog\/general-sql-parser-overview\/deconstruct-modify-rebuild-an-sql-statementjava-version-part-2\/\"><br \/>\nDeconstruct, Modify, Rebuild an SQL statement(Java version, part 2), use low level APIs<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article shows how to use General SQL Parser to split an SQL statement into it&#8217;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 &#8220;B AS B_Alias&#8221; 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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Deconstruct, Modify, Rebuild an SQL statement(Java version)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Deconstruct, Modify, Rebuild an SQL statement(Java version)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2013-07-13T13:23:51+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2011-02-08T12:34:19+00:00\",\"dateModified\":\"2013-07-13T13:23:51+00:00\",\"description\":\"Deconstruct, Modify, Rebuild an SQL statement(Java version)\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Deconstruct, Modify, Rebuild an SQL statement(Java version)\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Deconstruct, Modify, Rebuild an SQL statement(Java version)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Deconstruct, Modify, Rebuild an SQL statement(Java version)","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/","og_site_name":"SQL and Data Blog","article_modified_time":"2013-07-13T13:23:51+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2011-02-08T12:34:19+00:00","dateModified":"2013-07-13T13:23:51+00:00","description":"Deconstruct, Modify, Rebuild an SQL statement(Java version)","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/deconstruct-modify-rebuild-an-sql-statementjava-version\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Deconstruct, Modify, Rebuild an SQL statement(Java version)"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/737"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=737"}],"version-history":[{"count":30,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/737\/revisions"}],"predecessor-version":[{"id":1598,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/737\/revisions\/1598"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}