jOOQ - DML Statements

jOOQ supports various SQL Statements. These SQL Statements are created from the object of DSLContext. It will return the result based on the type of query. In this tutorial, we are going to learn about DML Statements and how to create and execute them in jOOQ.

What is DML?

DML stands for Data Manipulation Language. If you are familiar with SQL, you may already know about its commands. DML is a set of SQL commands that allow you to modify the data in a given database. The most common DML operations are:

• SELECT: This operation will retrieve the data from the specified table.

• INSERT: It will insert new data in the given table.

• UPDATE: It is used to modify existing data in a table.

• DELETE: It removes data from the specified table.

jOOQ Methods for Creating DML Statements

In jOOQ, the DSL class provides a set of methods to create DML statements which are listed in the below table −

Example of DML Statement in jOOQ

In this example, we will perform the INSERT operation to add a new row. Suppose, the existing table employee stores the following records −

To insert a new record using jOOQ, 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.

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);

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

       // Insert a new employee record into the table
       create.insertInto(employee, id, name, jobTitle)
          .values(2, "Shriansh", "Software Engineer")
          .execute();
	   // Fetch the values from the employee table
       Result<Record3<Integer, String, String>> result = create.select(id, name, jobTitle)
          .from(employee)
          .fetch();

       // Print the results
       for (Record record : result) {
          Integer employeeId = record.get(id);
          String employeeName = record.get(name);
          String employeeJobTitle = record.get(jobTitle);
	    
          System.out.println("ID: " + employeeId + ", 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 print the updated table −

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