jOOQ - DDL Statements

What are DDL Statements?

DDL stands for Data Definition Language. While DML Statements are used to query the data in a given database table, DDL statements are used to define, modify, or remove database objects like tables, views, indexes, and schemas. The most common DDL operations are:

• CREATE: It creates a new table, view or index.

• ALTER: This operation modifies an existing table.

• DROP: It is used to delete a database object like table, view or index.

• TRUNCATE: Deletes all rows from a table.

DDL Statements in jOOQ

In jOOQ, the following method of the DSL class is used to create DDL statements −

Example of DDL Statement in jOOQ

In this example, we will perform the ALTER TABLE operation to rename "ID" to "Rank". We are going the use the following table for this operation −

For ALTER TABLE operation, create a package inside src -> main -> java -> com.example.demo. The name of the com.example.demo folder will depend on the name of your project. Inside this package create a Java class.

We have named the package service and Java class EmployeeService.java. You can give any name of your choice.

In jOOQ, we use the alterTable() method to modify the table structure and the renameColumn() method to rename the column. Copy and paste the following code snippet inside EmployeeService.java file.

package com.example.demo.service;

import org.jooq.*;
import org.jooq.Record;
import org.jooq.impl.DSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;

@Service
public class EmployeeService {

   private final DataSource dataSource;

   @Autowired
   public EmployeeService(DataSource dataSource) {
      this.dataSource = dataSource;
   }

   public void run() {
      // Create a DSLContext using the DataSource
      DSLContext create = DSL.using(dataSource, SQLDialect.MYSQL);

      // Rename the column 'id' to 'rank'
      create.alterTable("employee")
         .renameColumn("id").to("rank")
         .execute();

      // Manually define the table and columns after renaming
      Table<?> employee = DSL.table("employee");
      Field<Integer> rank = DSL.field("rank", Integer.class);  
      Field<String> name = DSL.field("name", String.class);
      Field<String> jobTitle = DSL.field("job_title", String.class);

      // Fetch the values from the employee table
      Result<Record3<Integer, String, String>> result = create.select(rank, name, jobTitle)
         .from(employee)
         .fetch();

      // Print the results
      for (Record record : result) {
         Integer employeeRank = record.get(rank);  
         String employeeName = record.get(name);
         String employeeJobTitle = record.get(jobTitle);

         System.out.println("Rank: " + employeeRank + ", Name: " + employeeName + ", Job Title: " + employeeJobTitle);
      }
   }
}

Now, navigate to DemoApplication.java file inside com.example.demo folder. And, write the code given below −

package com.example.demo;

import com.example.demo.service.EmployeeService;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

   private final EmployeeService demoApplication;
      
   public DemoApplication(EmployeeService demoApplication) {
      this.demoApplication = demoApplication;
   }
      
   public static void main(String[] args) {
      SpringApplication.run(DemoApplication.class, args);
   }
      
   @Override
   public void run(String... args) throws Exception {
      demoApplication.run();
   }
}

When you run this code, it will change the "id" column to "Rank" as shown below −

Rank: 1, Name: Aman, Job Title: Technical Writer
Rank: 2, Name: Shriansh, Job Title: Software Engineer
Rank: 4, Name: Vivek, Job Title: Developer