jOOQ - DSLContext API

What is DSLContext in jOOQ?

DSLContext is an interface that configures behavior of the jOOQ when executing queries. Its reference or object manages the actual interaction with the database. This contextual DSL is the main entry point from where your jOOQ code accesses classes and functionality that are related to Query execution.

DSL is used for creating SQL queries whereas, DSLContext is used to execute these queries and interact with the database.

How to Create a DSLContext Object?

You can create an object or reference of DSLContext with the help of using() method which is a part of the DSL class. In this method, you can pass either pre-existing configuration or ad-hoc arguments like connection and dialect to create DSLContext object.

// Create it from a pre-existing configuration
DSLContext create = DSL.using(configuration);

// Create it from ad-hoc arguments
DSLContext create = DSL.using(connection, dialect);

Example of jOOQ DSLContext

Let's see how we can use DSLContext API to execute a query that reads values from a table. Suppose the table name is employee and contains the following values −

To get this 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);

       // 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 call the EmployeeService.java file and print the following result −

ID: 1, Name: Aman, Job Title: Technical Writer
ID: 4, Name: Vivek, Job Title: Developer