Parsing SQL CREATE query using JSQLParser

Parsing SQL statements using programming languages are not common. In this article, we will cover how to parse the standard CREATE SQL statement using a Java-based open source JSQLParser

There are multiple use-cases for parsing a Create Statement. Suppose if you want to validate the table schema between the source and target system and make sure you are using the same table and column names, you can very well parse the Create statements of both the systems and generate a report or send an alert email with the observations. For Parsing SQLs in java using the JSQLParser library, we can use the below maven dependency in the project.

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 program will help you to understand how we can parse the CREATE SQL statement.

Parsing SQL Create query in Java

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.create.table.ColDataType;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.util.SelectUtils;

import java.util.ArrayList;
import java.util.List;

public class CreateSqlParser {

    public static void main(String[] args) {


        System.out.println("Program to parse CREATE sql statement");
        System.out.println("-------------------------------------");
        String createSQL = "CREATE Table User.TableName (" +
                "id INTEGER NOT NULL," +
                "name varchar(100) NOT NULL," +
                "CRE_TS TIMESTAMP(0) NOT NULL," +
                "UPD_TS TIMESTAMP(0) NOT NULL" +
                ")" ;

        try {
            Statement createTable =  CCJSqlParserUtil.parse(createSQL);

            System.out.println("Table Name from query: " + ((CreateTable) createTable).getTable().getName());
            System.out.println("Database Name from query: " + ((CreateTable) createTable).getTable().getSchemaName());
            String sqlTableName = ((CreateTable) createTable).getTable().getName();
            System.out.println("Columns in the given insert query");
            System.out.println("---------------------------------");
            for(ColumnDefinition col: ((CreateTable) createTable).getColumnDefinitions())
            {
                System.out.println(col.getColumnName() + " - " + col.getColDataType().toString());
            }

            //Modifying the tablename and DB
            ((CreateTable) createTable).getTable().setName("NewTable");
            ((CreateTable) createTable).getTable().setSchemaName("NewDatabase");


            //Adding a new column to the create statement
            ColumnDefinition newCol = new ColumnDefinition();
            newCol.setColumnName("processDate");
            ColDataType colDataType = new ColDataType();
            colDataType.setDataType("DATE");
            newCol.setColDataType(colDataType);
            List<String> colSpecStrings = new ArrayList<>();
            colSpecStrings.add("FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '1970-01-01'");
            newCol.setColumnSpecStrings(colSpecStrings);
            ((CreateTable) createTable).getColumnDefinitions().add(newCol);


            String newSql = createTable.toString();
            System.out.println("\nNew Create statement:");
            System.out.println("-----------------------");
            System.out.println(newSql);
            System.out.println("New Columns");
            System.out.println("-----------");
            for(ColumnDefinition col: ((CreateTable) createTable).getColumnDefinitions())
            {
                System.out.println(col.getColumnName() + " - " + col.getColDataType().toString());
            }

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

    }
}

Expected output

The output of the above JSQLParser example program is given below. You can see that we are able to extract the database name, table name, list of columns and the data types of each of the columns as well.

Program to parse CREATE sql statement
-------------------------------------
Table Name from query: TableName
Database Name from query: User
Columns in the given insert query
---------------------------------
id - INTEGER
name - varchar (100)
CRE_TS - TIMESTAMP (0)
UPD_TS - TIMESTAMP (0)

New Create statement:
-----------------------
CREATE TABLE NewDatabase.NewTable (id INTEGER NOT NULL, name varchar (100) NOT NULL, CRE_TS TIMESTAMP (0) NOT NULL, UPD_TS TIMESTAMP (0) NOT NULL, processDate DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '1970-01-01')
New Columns
-----------
id - INTEGER
name - varchar (100)
CRE_TS - TIMESTAMP (0)
UPD_TS - TIMESTAMP (0)
processDate - DATE

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