Generate MySQL DDL Programmatically

This example shows you how to generate SQL data definition statements programmatically for MySQL. You can learn to create database tables and define foreign key relationships using CreateTable and CreateForeignKey objects, simplifying schema design and modification. This tool ensures error-free DDL query construction, enabling use cases such as rapid prototyping, database version control, and dynamic schema modifications in Java applications.

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import com.northconcepts.datapipeline.jdbc.sql.SqlPart;
import com.northconcepts.datapipeline.sql.mysql.*;

public class GenerateMySqlDdlProgrammatically {
    public static void main(String[] args) {
        MySqlPart createUsersTable = new CreateTable("employee")
                .addColumn(new CreateTableColumn("id", TableColumnType.INT).setAutoIncrement(true).setPrimaryKey(true))
                .addColumn(new CreateTableColumn("name", TableColumnType.VARCHAR).setNullable(false).setLength(100))
                .addColumn(new CreateTableColumn("email", TableColumnType.VARCHAR).setUnique(true).setLength(50))
                .addColumn(new CreateTableColumn("department_id", TableColumnType.INT))
                .setPretty(true);

        SqlPart createAddressTable = new CreateTable("department")
                .addColumn(new CreateTableColumn("id", TableColumnType.INT).setAutoIncrement(true).setPrimaryKey(true))
                .addColumn(new CreateTableColumn("name", TableColumnType.VARCHAR).setLength(100))
                .addColumn(new CreateTableColumn("description", TableColumnType.VARCHAR).setLength(255))
                .addColumn(new CreateTableColumn("manager_id", TableColumnType.INT))
                .setPretty(true);

        SqlPart addDepartmentIdForeignKey = new CreateForeignKey("fk_employee_department", "department", "employee")
                .setForeignKeyColumnNames("department_id")
                .setPrimaryKeyColumnNames("id")
                .setOnDeleteAction(ForeignKeyAction.SET_NULL)
                .setPretty(true);

        SqlPart addManagerIdForeignKey = new CreateForeignKey("fk_department_manager", "employee", "department")
                .setForeignKeyColumnNames("manager_id")
                .setPrimaryKeyColumnNames("id")
                .setPretty(true);

        SqlPart addIndex = new CreateIndex("ix_employee_name", "employee", "name");

        System.out.println(createUsersTable.getSqlFragment());
        System.out.println(createAddressTable.getSqlFragment());
        System.out.println(addDepartmentIdForeignKey.getSqlFragment());
        System.out.println(addManagerIdForeignKey.getSqlFragment());
        System.out.println(addIndex.getSqlFragment());
    }
}

Code Walkthrough

1. CreateTable instance is used to create new tables employee and department.
2. The columns are added via CreateTableColumn objects within addColumn() method. The corresponding constraints such as nullable, length, and unique are also specified in those objects.
3. Next, foreign key constraints are written using CreateForeignKey instance.
4. Then, an index is added to name column in the employee table.
5. getSqlFragment() retrieves the SQL fragment of the SqlPart objects, which represent the generated query. Each query is printed on the console one by one.

Console Output

CREATE TABLE IF NOT EXISTS employee (
  id int AUTO_INCREMENT,
  name varchar (100) NOT NULL,
  email varchar (50) UNIQUE,
  department_id int,
  PRIMARY KEY (id)
)
CREATE TABLE IF NOT EXISTS department (
  id int AUTO_INCREMENT,
  name varchar (100),
  description varchar (255),
  manager_id int,
  PRIMARY KEY (id)
)
ALTER TABLE employee
  ADD CONSTRAINT fk_employee_department FOREIGN KEY (department_id)
  REFERENCES department (id)
  ON DELETE SET NULL
ALTER TABLE department
  ADD CONSTRAINT fk_department_manager FOREIGN KEY (manager_id)
  REFERENCES employee (id)
CREATE INDEX ix_employee_name ON employee (name)