This tutorial guides you through writing type-safe queries in Java using jOOQ (Java Object Oriented Query). You’ll get a quick tour of jOOQ by running an application and seeing how it generates database tables from Java classes. With its smooth API, jOOQ also helps you create type-safe SQL queries.
jOOQ vs JPA
jOOQ is a type-safe SQL DSL (Domain-Specific Language) that allows you to write SQL queries in Java code. With jOOQ, you write SQL queries using Java syntax, and jOOQ generates the corresponding SQL code. Projects, where performance is a critical factor, should adopt the jOOQ.
On the other hand, JPA is a higher-level ORM (Object-Relational Mapping) framework that maps Java objects to database tables. JPA provides an abstraction layer between the application and the database, this means you write Java code that interacts with database entities, and JPA generates the SQL code.
In short, jOOQ is a more SQL-centric approach that provides better control over the SQL code, whereas JPA is a more object-oriented approach that provides higher-level abstractions and simpler code.
For more comparisons between jOOQ and JPA, you can visit this link for more details on the official website.
Is jOOQ open-source and free to use?
Yes, jOOQ is an open-source library released under the Apache 2.0 license, which means it is free to use, modify, and distribute for any purpose, including commercial applications.
How to add jOOQ to my maven-based Java project?
Add the jOOQ dependencies to your project’s pom.xml file. You’ll need to include the jOOQ core library and the appropriate jOOQ code generation library for your database, such as jooq-codegen-mysql for MySQL. Here’s an example:
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.8.3</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>3.8.3</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>3.8.3</version>
</dependency>
For different Java Jdk versions, you can select different dependencies as suggested on official docs.
We also need one dependency for the MySQL connector driver.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
The pom.xml file for the project will look like following
<project>
<groupId>com.example</groupId>
<artifactId>my-college</artifactId>
<version>1.0.0</version>
<dependencies>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.8.3</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>3.8.3</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>3.8.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.15.3</version>
<executions>
<execution>
<id>generate-jooq-sources</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
</dependencies>
<configuration>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<inputSchema>college</inputSchema>
<includes>.*</includes>
<excludes></excludes>
</database>
<target>
<packageName>test.generated</packageName>
<directory>C:/Desktop/MySQLTest/src/main/java</directory>
</target>
</generator>
</configuration>
</plugin>
</plugins>
</build>
</project>
Creating Database
We will create a database called “college” and a corresponding “student” table. Connect to MySQL via your command line client or MySQL workbench and type the following:
CREATE DATABASE `college`; USE `college`; CREATE TABLE `student` ( `id` int NOT NULL, `first_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) );
Code Generation
In this step, we’re going to look at how we can map the student table we just created with the Java classes. To generate schema, you need the MySQL Connector jar file and the jOOQ jar files to generate schema. These files allow your program to communicate with a MySQL database and provide an easy way to interact with SQL databases. Create a college.xml that looks like the following:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration>
<jdbc>
<driver>com.mysql.cj.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/college</url>
<user>root</user>
<password></password>
</jdbc>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<database>
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<inputSchema>college</inputSchema>
<includes>.*</includes>
<excludes></excludes>
</database>
<target>
<packageName>test.generated</packageName>
<directory>C:/Desktop/MySQLTest/src/main/java</directory>
</target>
</generator>
</configuration>
The username (<user/>)should be replaced with whatever user has the appropriate privileges to query the database. Besides these, you will also want to look at the other values and replace them as necessary, such as <packageName/> – set this to the parent package you want to create for the generated classes. <directory/> – where the output of the generated classes will appear. The college.xml should be placed in the file in the same directory as the pom.xml file, or in a subdirectory named src/main/resources in your Maven project.
Connecting to Database
To establish the connection between the MySQL and application, we write the following code in Java:
import static test.generated.Tables.*;
import static org.jooq.impl.DSL.*;
import java.sql.*;
public class Main {
public static void main(String[] args) {
String userName = "root";
String password = "";
String url = "jdbc:mysql://localhost:3306/college";
try (Connection conn = DriverManager.getConnection(url, userName, password)) {
// ...
}
catch (Exception e) {
e.printStackTrace();
}
}
}
When using jOOQ, we only need to specify the JDBC resource for connecting to the database. The jOOQ library takes care of creating prepared statements and handling result sets internally, so we don’t need to worry about those details.
Querying with jOOQ
To construct a jOOQ query, we first get an instance of DSLContext so we can generate a simple query.
DSLContext selectQuery = DSL.using(conn, SQLDialect.MYSQL); Result<Record> studentResult = selectQuery.select().from(student).fetch();
To DSL we pass the instance of the MySQL connection. The DSLContext doesn’t close the connection, so we have to close it ourselves. We then use jOOQ’s query DSL to return an instance of Result.
Iterating
After extracting the result by using the jOOQ query, we can also do the other manipulation along with iterating over the result as follow:
for (Record r : studentResult) {
Integer id = r.getValue(student.ID);
String firstName = r.getValue(AUTHOR.FIRST_NAME);
System.out.println("ID: " + id + " first name: " + firstName);
}
Conclusion
jOOQ lets Java Programmers write type-safe SQL queries by accessing the database. jOOQ generates the SQL statements from Java code with the help of their fluent API for building the queries. jOOQ comes up with the support of a wide range of databases, including MySQL, Oracle, PostgreSQL, and SQL servers,s and provides advanced features.