Parsing SQL INSERT easily using JSQLParser

Parsing SQL query to get the objects used, columns selected, getting database names, etc from the query is not common using programming languages. The use-case depends are what you are going to do after parsing and extracting the objects from the query. You may want to modify the table name and simply load the copy into multiple tables, you may want to fetch data from two different tables during runtime but want to maintain only one template for the insert query, etc.

In the following example, we will see how we can perform all of these using the JSQLParser library. This library is created with the help of Javacc. A grammar file will be used to define the parsing cases and java classes will be extracted out of it.

JSQLParser 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>

The following JSQLParser example will help you to understand how we can parse an insert SQL statement using java.

Parsing SQL Insert 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.Table;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;

public class InsertSqlParser {

    public static void main(String[] args) throws JSQLParserException {

        System.out.println("Parsing SQL INSERT statement");
        String insertSQL = "Insert into Database.TargetTable " +
                "select col1, col2, col3 from Database.SourceTable";

        try {
            Insert insertObj = (Insert) CCJSqlParserUtil.parse(insertSQL);

            System.out.println("Original Insert SQL details");
            System.out.println("---------------------------");
            System.out.println("Target Database: " + insertObj.getTable().getSchemaName());
            System.out.println("Target Table: " + insertObj.getTable().getName());
            System.out.println("Select statement of insert: " + insertObj.getSelect().getSelectBody().toString());

            //Modifying the target table details
            Table newTable = new Table();

            newTable.setName("NewTargetTable");
            newTable.setSchemaName("NewDatabase");
            insertObj.setTable(newTable);
            System.out.println("Full insert query:");
            System.out.println(insertObj.toString());

            System.out.println("\nModified Insert SQL details");
            System.out.println("---------------------------");
            System.out.println("Target Database: " + insertObj.getTable().getSchemaName());
            System.out.println("Target Table: " + insertObj.getTable().getName());
            System.out.println("Select statement of insert: " + insertObj.getSelect().toString());


            //Modifying the source table for select in insert
            PlainSelect selectBody = (PlainSelect) insertObj.getSelect().getSelectBody();
            ((Table) selectBody.getFromItem()).setName("NewSourceTable");
            ((Table) selectBody.getFromItem()).setSchemaName("NewDatabase");

            Select newSelect = new Select();
            newSelect.setSelectBody(selectBody);
            insertObj.setSelect(newSelect);
            System.out.println("Modified Select statement of insert: " + insertObj.getSelect().toString());
            System.out.println("Full insert query:");
            System.out.println(insertObj.toString());
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }

    }
}

Expected Output

The output of the above JSQLParser example program is shown below. The database name, table name and the select statement using in the insert query are extracted.

Parsing SQL INSERT sql statement
Original Insert SQL details
---------------------------
Target Database: Database
Target Table: TargetTable
Select statement of insert: SELECT col1, col2, col3 FROM Database.SourceTable
Full insert query:
INSERT INTO NewDatabase.NewTargetTable SELECT col1, col2, col3 FROM Database.SourceTable

Modified Insert SQL details
---------------------------
Target Database: NewDatabase
Target Table: NewTargetTable
Select statement of insert: SELECT col1, col2, col3 FROM Database.SourceTable
Modified Select statement of insert: SELECT col1, col2, col3 FROM NewDatabase.NewSourceTable
Full insert query:
INSERT INTO NewDatabase.NewTargetTable SELECT col1, col2, col3 FROM NewDatabase.NewSourceTable

Also, check “Parsing SELECT sql using jsqlparser“, “Parsing CREATE sql using jsqlparser“