Parsing SQL SELECT query using JSQLParser

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 the basic condition and multiple select queries.

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.

Instead, you can use the JSQL Parser to parse the Select query and add or remove columns based on the need.

Maven dependency

You need to create a new maven project and add the following dependency in your pom.xml file to access this parsing library

<dependency>
       <groupId>com.github.jsqlparser</groupId>
       <artifactId>jsqlparser</artifactId>
       <version>1.4</version>
</dependency>

Java code to parse SELECT query

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.util.SelectUtils;

import java.util.List;

public class SelectSqlParser {

    public static void main(String[] args) {

        System.out.println("Program to parse SELECT sql statement");
        String selectSQL = "Select id, name, location from Database.UserTable " +
                "where created_dt >= current_date- 180";

        try {
            Statement select = (Statement) CCJSqlParserUtil.parse(selectSQL);
            //Simple Select query parsing
            System.out.println("Simple single select with where condition\n");

            System.out.println("List of  columns in select query");
            System.out.println("--------------------------------");
            List<SelectItem> selectCols = ((PlainSelect) ((Select) select).getSelectBody()).getSelectItems();

            for (SelectItem selectItem : selectCols)
                System.out.println(selectItem.toString());

            System.out.println("Where condition: " + ((PlainSelect) ((Select) select).getSelectBody()).getWhere().toString());

            SelectUtils.addExpression((Select) select, new Column("newColumnName"));
            System.out.println("\nModified select with additional column");
            System.out.println("----------------------------------");
            System.out.println(select.toString());

            ((Table) ((PlainSelect) ((Select) select).getSelectBody()).getFromItem()).setName("NewSourceTable");
            ((Table) ((PlainSelect) ((Select) select).getSelectBody()).getFromItem()).setSchemaName("NewSourceTable");

            System.out.println("\nModified select with new table and database");
            System.out.println("-------------------------------------");
            System.out.println(select.toString());

            selectSQL = "Select w.id, w.name, w.location from Database.WebLogs w " +
                    "union Select m.id, m.name, m.location from Database.MobileLogs m ";

            Statement newSQL = (Statement) CCJSqlParserUtil.parse(selectSQL);
            List<SelectBody> selectList = ((SetOperationList) ((Select) newSQL).getSelectBody()).getSelects();
            System.out.println("\nListing all selects from the query");
            System.out.println("----------------------------------");
            for (SelectBody selectBody : selectList)
                System.out.println(selectBody.toString());

        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }
}

Expected output

Program to parse SELECT sql statement
Simple single select with where condition

List of  columns in select query
--------------------------------
id
name
location
Where condition: created_dt >= current_date - 180

Modified select with additional column
----------------------------------
SELECT id, name, location, newColumnName FROM Database.UserTable WHERE created_dt >= current_date - 180

Modified select with new table and database
-------------------------------------
SELECT id, name, location, newColumnName FROM NewSourceTable.NewSourceTable WHERE created_dt >= current_date - 180

Listing all selects from the query
----------------------------------
SELECT w.id, w.name, w.location FROM Database.WebLogs w
SELECT m.id, m.name, m.location FROM Database.MobileLogs m

Also check “Parsing INSERT sql using jsqlparser“,”Parsing CREATE sql using jsqlparser“