jOOQ - DSL API

The DSL stands for Domain Specific Language. It is less complex than a general-purpose language like Java or C. The DSL is designed for a specific class of problems.

For instance, it is used with jOOQ to write SQL queries in Java. Although these queries are written in Java language, they almost look like SQL statements. Let's understand this statement with an example.

See this simple SQL query −

SELECT *
FROM AUTHOR a
JOIN BOOK b ON a.ID = b.AUTHOR_ID
WHERE a.YEAR_OF_BIRTH > 1920
   AND a.LAST_NAME = 'Kanetkar'
ORDER BY b.TITLE;

The above SQL query selects all columns from the AUTHOR table (aliased as a) and joins it with the BOOK table (aliased as b) on the condition that the ID column in the AUTHOR table matches the AUTHOR_ID column in the BOOK table. It then filters the results to include only those authors born after 1920 and with the last name "Kanetkar" and orders the results by the title of the books.

The below code snippet shows how the same SQL query would look in jOOQ's DSL −

Result<Record> result = create.select()
   .from(AUTHOR.as("a"))
   .join(BOOK.as("b")).on(a.ID.eq(b.AUTHOR_ID))
   .where(a.YEAR_OF_BIRTH.gt(1920)
   .and(a.LAST_NAME.eq("Kanetkar")))
   .orderBy(b.TITLE)
   .fetch();

SQL vs DSL

SQL or Structured Query Language is used to manage relational databases by means of SQL statements. And, the SQL statements are the commands that allow users to retrieve, insert, update and delete data.

On the other hand, DSL is designed to be used within a specific domain or application area. It provides special syntax and semantics for the needs of that domain.

For example, DSL in jOOQ is designed to simplify SQL query creation within Java applications.

The domain specific languages are always dependent on a general-purpose language. In the case of jOOQ, it needs to be integrated with Java. However, the SQL is independent of any programming language.

DSL in jOOQ

As mentioned earlier, DSL is the primary way to write SQL queries in jOOQ. We have also seen how it is similar to the SQL statements. Additionally, one of the key benefits of using jOOQ's DSL is its BNF-based interface hierarchy. It prevents bad query syntax from compiling which may not allow incorrect queries to compile and prevent runtime errors.

In JOOQ, the DSL class and the DSLContext interface work together. The DSL class is a utility class that provides factory methods for creating tables, fields, and queries. However, you cannot execute queries without using DSLContext, because the DSLContext is responsible for managing the database connection and executing the queries.

Example

In the previous chapter, we created "employee" table inside "mydatabase" database. Let's see a query that prints contents of the "employee" table.

// Fetch the values from the employee table
Result<Record> 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);
}

Commonly used jOOQ DSL method

Common methods of the jOOQ DSL class are −

• using(): This method creates a DSLContext instance from a JDBC connection and a SQL dialect.

• select(): It is used to create a Select query.

• insertInto(): This is used to create an Insert query.

• update(): It is used for creating an Update query.

• deleteFrom(): It is used to create a Delete query.