How to add Primary key in the Create Table statement using Parser?

Parser

Parser is a program which understand the grammar of the sequence of words/sentences and produce the syntactical tree representation. It is being used in many areas such as programming languages, Natural language processing, SQL and so on. To demonstrate the use of Parser, we are going to refer the open source JSqlParser in this tutorial.

JSqlParser

JSqlParser is a SQL statement parser which is available as open source in Github. It parses an SQL statement and translate into a hierarchy of Java classes. In the following example, we are going to parse the CREATE TABLE statement using JSqlParser. Let’s write a Java program to parse the CREATE TABLE statement and add the primary key in it.

CREATE TABLE statement

In the above CREATE TABLE statement, we want to add the Column Student_id as a Primary key.

Java program to parse the CREATE TABLE SQL using JSqlParser

Step 1: Adding JSqlParser as dependency

We are creating a maven project to add the JSqlParser as a dependency. Let’s add the below dependency in the pom.xml file.

Step 2: Assign the input CREATE TABLE statement to a variable

This is our input CREATE TABLE statement which doesn’t have primary key. The new line character \n is optional. We can give the complete SQL in a single line also. For readability purpose, we are giving the input in multi line.

Step 3 : Parse the SQL using JSqlParser

Using Parser util class CCJSqlParserUtil, we are calling the parse method with the argument of CREATE TABLE sql.

After executing the parse method, the Statement object createTable have the hierarchy of Java classes as below.

JSqlParser output as hierarchy of Java classes
JSqlParser output as hierarchy of Java classes

Step 4 : Explore the parser output

If the parse method is executed successfully, we can get all the information about the CREATE TABLE statement. Let’s get the database/table name and column definition of the statement. Also we are changing the database and table name in the create table statement.

Step 5 : Create primary key constraint as index

We can set the different index to the Sql such as Check constraint, Exclude constraint, Foreign key and Named constraint. For primary key, we need to create a Named constraint as below.

Let’s set the Primary key of Student_id as Named constraint in the index.

Step 6: Set the index in the CREATE TABLE statement

We need to set the index list in the create table as below so that CREATE table statement will be generated with the Primary key.

Complete Java program to add the primary key in the CREATE TABLE SQL

Output

As we shown below, new CREATE TABLE is generated with the primary key of Student_id. Also database and table name is changed to College_db_bk.Students_bkup.