jOOQ - Transactional Statements

What Is a Transaction?

A transaction is a sequence of one or more operations executed as a single unit of work. In case of failure of any tasks, the transaction fails. A transaction must satisfy the ACID properties, which are −

• Atomicity: A transaction is atomic, meaning that either all operations succeed, or none of them do.

• Consistency: It ensures that the database properly moves from one state to another after a successfully committed transaction.

• Isolation: Transactions are isolated from each other.

• Durability: Once a transaction is committed, the changes will persist, even if the system crashes.

Transactional Operation

Common transactional operations are −

• BEGIN: To start a new transaction.

• COMMIT: To make the changes permanent.

• ROLLBACK: It will undo the changes

Transactional Statements in jOOQ

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

Example of Transactional Statement in jOOQ

In this example, we will be grouping ALTER TABLE operation and INSERT operation together. We are going the use the following table for this operation −

Start with creating 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. To insert the data into table, we use insertInto() method. 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);

      // Wrap the operations inside a transaction
      create.transaction(configuration -> {
         // Create a DSLContext within the transaction context
         DSLContext ctx = DSL.using(configuration);

         // 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);
         // Rename the column 'id' to 'rank'
         ctx.alterTable("employee")
            .renameColumn("rank").to("id")
            .execute();

         // Insert a new employee record into the table
         ctx.insertInto(employee, id, name, jobTitle)
            .values(3, "Ansh", "Software Engineer")
            .execute();

         // Fetch the values from the employee table
         Result<Record3<Integer, String, String>> result = ctx.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 change the "Rank" column to "id" and also it will insert one more row to the specified table −

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