How to add Primary key in the Create Table statement using Parser?
Contents
- 1 Parser
- 1.1 JSqlParser
- 1.2 Java program to parse the CREATE TABLE SQL using JSqlParser
- 1.2.1 Step 1: Adding JSqlParser as dependency
- 1.2.2 Step 2: Assign the input CREATE TABLE statement to a variable
- 1.2.3 Step 3 : Parse the SQL using JSqlParser
- 1.2.4 Step 4 : Explore the parser output
- 1.2.5 Step 5 : Create primary key constraint as index
- 1.2.6 Step 6: Set the index in the CREATE TABLE statement
- 1.3 Complete Java program to add the primary key in the CREATE TABLE SQL
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
|
1 2 3 4 5 6 |
CREATE TABLE College_db.Students ( Student_id int, Name varchar(255), Course varchar(255), Join_date DATE ); |
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.
|
1 2 3 4 5 |
<dependency> <groupId>com.github.jsqlparser</groupId> <artifactId>jsqlparser</artifactId> <version>4.2</version> </dependency> |
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.
|
1 2 3 4 5 6 |
String createTableSql = "CREATE TABLE College_db.Students (\n" + " Student_id int,\n" + " Name varchar(255),\n" + " Course varchar(255),\n" + " Join_date DATE\n" + ");"; |
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.
|
1 |
Statement createTable = CCJSqlParserUtil.parse(createTableSql); |
After executing the parse method, the Statement object createTable have the hierarchy of Java classes as below.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
System.out.println("Table Name from query: " + ((CreateTable) createTable).getTable().getName()); System.out.println("Database Name from query: " + ((CreateTable) createTable).getTable().getSchemaName()); System.out.println("\nColumns in the given insert query"); System.out.println("---------------------------------\n"); for(ColumnDefinition col: ((CreateTable) createTable).getColumnDefinitions()) { System.out.println(col.getColumnName() + " - " + col.getColDataType().toString()); } //Changing the DB and table name ((CreateTable) createTable).getTable().setSchemaName("College_db_bk"); ((CreateTable) createTable).getTable().setName("Students_bkup"); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
//Creating Primary Key constraint NamedConstraint namedConstraint = new NamedConstraint(); namedConstraint.setType("PRIMARY KEY"); //Adding column names for the Primary Key List<Index.ColumnParams> columns = new ArrayList<>(); Index.ColumnParams columnParams = new Index.ColumnParams("Student_id"); columns.add(columnParams); //Setting columns in the Primary Key constraint namedConstraint.setColumns(columns); |
Let’s set the Primary key of Student_id as Named constraint in the index.
|
1 2 3 |
//Setting the Primary Key constraint in Index list List<Index> indexList = new ArrayList<>(); indexList.add(namedConstraint); |
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.
|
1 |
((CreateTable) createTable).setIndexes(indexList); |
Complete Java program to add the primary key in the CREATE TABLE SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.create.table.ColumnDefinition; import net.sf.jsqlparser.statement.create.table.CreateTable; import net.sf.jsqlparser.statement.create.table.Index; import net.sf.jsqlparser.statement.create.table.NamedConstraint; import java.util.ArrayList; import java.util.List; public class AddPrimaryKey { public static void main(String[] args) { System.out.println("Adding Primary key in the CREATE TABLE statement"); System.out.println("-------------------------------------\n"); //Assign the input CREATE TABLE sql String createTableSql = "CREATE TABLE College_db.Students (\n" + " Student_id int,\n" + " Name varchar(255),\n" + " Course varchar(255),\n" + " Join_date DATE\n" + ");"; try { //Parsing Create table statement using JSqlParser Statement createTable = CCJSqlParserUtil.parse(createTableSql); //Getting the table and database name from Create table System.out.println("Table Name from query: " + ((CreateTable) createTable).getTable().getName()); System.out.println("Database Name from query: " + ((CreateTable) createTable).getTable().getSchemaName()); System.out.println("\nColumns in the given insert query"); System.out.println("---------------------------------\n"); for (ColumnDefinition col : ((CreateTable) createTable).getColumnDefinitions()) { System.out.println(col.getColumnName() + " - " + col.getColDataType().toString()); } //Changing the DB and table name ((CreateTable) createTable).getTable().setSchemaName("College_db_bk"); ((CreateTable) createTable).getTable().setName("Students_bkup"); //Creating Primary Key constraint NamedConstraint namedConstraint = new NamedConstraint(); namedConstraint.setType("PRIMARY KEY"); //Adding column names for the Primary Key List<Index.ColumnParams> columns = new ArrayList<>(); Index.ColumnParams columnParams = new Index.ColumnParams("Student_id"); columns.add(columnParams); //Setting columns in the Primary Key constraint namedConstraint.setColumns(columns); //Setting the Primary Key constraint in Index list List<Index> indexList = new ArrayList<>(); indexList.add(namedConstraint); //Setting the indexes in Create table statement ((CreateTable) createTable).setIndexes(indexList); System.out.println("New CREATE TABLE statement with Primary Key"); System.out.println("---------------------------------"); System.out.println(createTable + ";"); } catch (JSQLParserException e) { throw new RuntimeException(e); } } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Adding Primary key in the CREATE TABLE statement ------------------------------------- Table Name from query: Students Database Name from query: College_db Columns in the given insert query --------------------------------- Student_id - int Name - varchar (255) Course - varchar (255) Join_date - DATE New CREATE TABLE statement with Primary Key --------------------------------- CREATE TABLE College_db_bk.Students_bkup (Student_id int, Name varchar (255), Course varchar (255), Join_date DATE, PRIMARY KEY (Student_id)); |
Recommended Articles
