{"id":222,"date":"2019-07-18T03:49:56","date_gmt":"2019-07-17T22:19:56","guid":{"rendered":"https:\/\/techieshouts.com\/?p=222"},"modified":"2022-08-09T19:07:53","modified_gmt":"2022-08-09T13:37:53","slug":"parsing-sql-select-query-using-jsqlparser","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/parsing-sql-select-query-using-jsqlparser\/","title":{"rendered":"Parsing SQL SELECT query using JSQLParser"},"content":{"rendered":"\n

Update<\/strong>: A new version of this article is available here<\/a><\/p>\n\n\n\n

In the other article, we saw how to parse an insert query<\/a> and modify the objects in the query with the help of the JSQL Parser. In this article, we will see how to use the JSQL Parser for parsing a simple select query with the basic condition and multiple select queries.<\/p>\n\n\n\n

Imagine a scenario where you are creating the SQL query dynamically in your application for your users. Suppose, if you have to add or remove additional columns based on the case then you may have to hardcode that in your application.<\/p>\n\n\n\n

Instead, you can use the JSQL Parser to parse the Select query and add or remove columns based on the need.<\/p>\n\n\n\n

Maven dependency<\/h2>\n\n\n\n

You need to create a new maven project and add the following dependency in your pom.xml file to access this parsing library<\/p>\n\n\n\n

<dependency>\n       <groupId>com.github.jsqlparser<\/groupId>\n       <artifactId>jsqlparser<\/artifactId>\n       <version>1.4<\/version>\n<\/dependency><\/pre>\n\n\n\n
Java code to parse SELECT query<\/h2>\n\n\n\n
import net.sf.jsqlparser.JSQLParserException;\nimport net.sf.jsqlparser.expression.Expression;\nimport net.sf.jsqlparser.expression.operators.conditional.AndExpression;\nimport net.sf.jsqlparser.parser.CCJSqlParserUtil;\nimport net.sf.jsqlparser.schema.Column;\nimport net.sf.jsqlparser.schema.Table;\nimport net.sf.jsqlparser.statement.Statement;\nimport net.sf.jsqlparser.statement.insert.Insert;\nimport net.sf.jsqlparser.statement.select.*;\nimport net.sf.jsqlparser.util.SelectUtils;\n\nimport java.util.List;\n\npublic class SelectSqlParser {\n\n    public static void main(String[] args) {\n\n        System.out.println(\"Program to parse SELECT sql statement\");\n        String selectSQL = \"Select id, name, location from Database.UserTable \" +\n                \"where created_dt >= current_date- 180\";\n\n        try {\n            Statement select = (Statement) CCJSqlParserUtil.parse(selectSQL);\n            \/\/Simple Select query parsing\n            System.out.println(\"Simple single select with where condition\\n\");\n\n            System.out.println(\"List of  columns in select query\");\n            System.out.println(\"--------------------------------\");\n            List<SelectItem> selectCols = ((PlainSelect) ((Select) select).getSelectBody()).getSelectItems();\n\n            for (SelectItem selectItem : selectCols)\n                System.out.println(selectItem.toString());\n\n            System.out.println(\"Where condition: \" + ((PlainSelect) ((Select) select).getSelectBody()).getWhere().toString());\n\n            SelectUtils.addExpression((Select) select, new Column(\"newColumnName\"));\n            System.out.println(\"\\nModified select with additional column\");\n            System.out.println(\"----------------------------------\");\n            System.out.println(select.toString());\n\n            ((Table) ((PlainSelect) ((Select) select).getSelectBody()).getFromItem()).setName(\"NewSourceTable\");\n            ((Table) ((PlainSelect) ((Select) select).getSelectBody()).getFromItem()).setSchemaName(\"NewSourceTable\");\n\n            System.out.println(\"\\nModified select with new table and database\");\n            System.out.println(\"-------------------------------------\");\n            System.out.println(select.toString());\n\n            selectSQL = \"Select w.id, w.name, w.location from Database.WebLogs w \" +\n                    \"union Select m.id, m.name, m.location from Database.MobileLogs m \";\n\n            Statement newSQL = (Statement) CCJSqlParserUtil.parse(selectSQL);\n            List<SelectBody> selectList = ((SetOperationList) ((Select) newSQL).getSelectBody()).getSelects();\n            System.out.println(\"\\nListing all selects from the query\");\n            System.out.println(\"----------------------------------\");\n            for (SelectBody selectBody : selectList)\n                System.out.println(selectBody.toString());\n\n        } catch (JSQLParserException e) {\n            e.printStackTrace();\n        }\n    }\n}<\/pre>\n\n\n\n
Expected output<\/h2>\n\n\n\n
Program to parse SELECT sql statement\nSimple single select with where condition\n\nList of  columns in select query\n--------------------------------\nid\nname\nlocation\nWhere condition: created_dt >= current_date - 180\n\nModified select with additional column\n----------------------------------\nSELECT id, name, location, newColumnName FROM Database.UserTable WHERE created_dt >= current_date - 180\n\nModified select with new table and database\n-------------------------------------\nSELECT id, name, location, newColumnName FROM NewSourceTable.NewSourceTable WHERE created_dt >= current_date - 180\n\nListing all selects from the query\n----------------------------------\nSELECT w.id, w.name, w.location FROM Database.WebLogs w\nSELECT m.id, m.name, m.location FROM Database.MobileLogs m<\/pre>\n\n\n\n
Also check “Parsing INSERT sql using jsqlparser<\/a>“,”Parsing CREATE sql using jsqlparser<\/a>“<\/p>\n","protected":false},"excerpt":{"rendered":"
Update: A new version of this article is available here In the other article, we saw how to parse an insert query and modify the objects in the query with the help of the JSQL Parser. In this article, we will see how to use the JSQL Parser for parsing a simple select query with\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":276,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[195,190,237,193,192,194,236],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/222"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=222"}],"version-history":[{"count":9,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/222\/revisions"}],"predecessor-version":[{"id":1163,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/222\/revisions\/1163"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media\/276"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=222"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}