1. Introduction
In this tutorial, we’re going to take a quick tour of running an application with jOOQ (Java Object Orientated Query). This library generates Java classes based on the database tables and lets us create type-safe SQL queries through its fluent API.
We’ll cover the whole setup, PostgreSQL database connection, and a few examples of CRUD operations.
2. Maven Dependencies
For the jOOQ library, we’ll need the following three jOOQ dependencies:
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.19.0</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>3.19.0</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>3.19.0</version>
</dependency>
We’ll also need one dependency for the PostgreSQL driver:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.5.4</version>
<scope>runtime</scope>
</dependency>
3. Database Structure
Before we start, let’s create a simple DB schema for our examples. We’ll use a simple Author and an Article relationship:
create table AUTHOR
(
ID integer PRIMARY KEY,
FIRST_NAME varchar(255),
LAST_NAME varchar(255),
AGE integer
);
create table ARTICLE
(
ID integer PRIMARY KEY,
TITLE varchar(255) not null,
DESCRIPTION varchar(255),
AUTHOR_ID integer
CONSTRAINT fk_author_id REFERENCES AUTHOR
);
4. Database Connection
Now, let’s take a look at how we’ll be connecting to our database.
Firstly, we need to provide the user, password, and a full URL to the database. We’ll use these properties to create a Connection object by using the DriverManager and its getConnection method:
String userName = "user";
String password = "pass";
String url = "jdbc:postgresql://db_host:5432/baeldung";
Connection conn = DriverManager.getConnection(url, userName, password);
Next, we need to create an instance of DSLContext. This object will be our entry point for jOOQ interfaces:
DSLContext context = DSL.using(conn, SQLDialect.POSTGRES);
In our case, we’re passing the POSTGRES dialect, but there are a few other ones available like H2, MySQL, SQLite, and more.
5. Code Generation
To generate Java classes for our database tables, we’ll need the following jooq-config.xml file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
<jdbc>
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://db_url:5432/baeldung_database</url>
<user>username</user>
<password>password</password>
</jdbc>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<database>
<name>org.jooq.meta.postgres.PostgresDatabase</name>
<inputSchema>public</inputSchema>
<includes>.*</includes>
<excludes></excludes>
</database>
<target>
<packageName>com.baeldung.jooq.model</packageName>
<directory>C:/projects/baeldung/tutorials/jooq-examples/src/main/java</directory>
</target>
</generator>
</configuration>
The custom configuration requires changes in the
To execute the jOOQ code generation tool, we need to run the following code:
GenerationTool.generate(
Files.readString(
Path.of("jooq-config.xml")
)
);
After the generation is complete, we’ll get the two following classes, each corresponding to its database table:
com.baeldung.model.generated.tables.Article;
com.baeldung.model.generated.tables.Author;
6. CRUD Operations
Now, let’s take a look at a few basic CRUD operations that we can perform with the jOOQ library.
6.1. Creating
Firstly, let’s create a new Article record. To do so, we need to invoke the newRecord method with a proper table reference as a parameter:
ArticleRecord article = context.newRecord(Article.ARTICLE);
The Article.ARTICLE variable is a reference instance to the ARTICLE database table. It was automatically created by jOOQ during code generation.
Next, we can set values for all needed properties:
article.setId(2);
article.setTitle("jOOQ examples");
article.setDescription("A few examples of jOOQ CRUD operations");
article.setAuthorId(1);
Finally, we need to invoke the store method on the record to save it in the database:
article.store();
6.2. Reading
Now, let’s see how we can read values from the database. As an example, let’s select all the authors:
Result<Record> authors = context.select()
.from(Author.AUTHOR)
.fetch();
Here, we’re using the select method combined with from clause to indicate from which table we want to read. Invoking the fetch method executes the SQL query and returns the generated result.
The Result object implements the Iterable interface, so it’s easy to iterate over each element. And while having access to a single record, we can get its parameters by using the getValue method with a proper field reference:
authors.forEach(author -> {
Integer id = author.getValue(Author.AUTHOR.ID);
String firstName = author.getValue(Author.AUTHOR.FIRST_NAME);
String lastName = author.getValue(Author.AUTHOR.LAST_NAME);
Integer age = author.getValue(Author.AUTHOR.AGE);
System.out.printf("Author %s %s has id: %d and age: %d%n", firstName, lastName, id, age);
});
We can limit the select query to a set of specific fields. Let’s fetch only the article ids and titles:
Result<Record2<Integer, String>> articles = context.select(Article.ARTICLE.ID, Article.ARTICLE.TITLE)
.from(Author.AUTHOR)
.fetch();
We can also select a single object with the fetchOne method. The parameters for this one are the table reference and a condition to match the proper record.
In our case, let’s just select an Author with an id equal to 1:
AuthorRecord author = context.fetchOne(Author.AUTHOR, Author.AUTHOR.ID.eq(1))
If no record matches the condition, the fetchOne method will return null.
6.3. Updating
To update a given record, we can use the update method from the DSLContext object combined with a set method invocations for every field we need to change. This statements should be followed by a where clause with a proper match condition:
context.update(Author.AUTHOR)
.set(Author.AUTHOR.FIRST_NAME, "David")
.set(Author.AUTHOR.LAST_NAME, "Brown")
.where(Author.AUTHOR.ID.eq(1))
.execute();
The update query will run only after we call the execute method. As a return value, we’ll get an integer equal to the number of records that were updated.
It’s also possible to update an already fetched record by executing its store method:
ArticleRecord article = context.fetchOne(Article.ARTICLE, Article.ARTICLE.ID.eq(1));
article.setTitle("A New Article Title");
article.store();
The store method will return 1 if the operation was successful or 0 if the update was not necessary. For example, nothing was matched by the condition.
6.4. Deleting
To delete a given record, we can use the delete method from the DSLContext object. The delete condition should be passed as a parameter in the following where clause:
context.delete(Article.ARTICLE)
.where(Article.ARTICLE.ID.eq(1))
.execute();
The delete query will run only after we call the execute method. As a return value, we’ll get an integer equal to the number of deleted records.
It’s also possible to delete an already fetched record by executing its delete method:
ArticleRecord articleRecord = context.fetchOne(Article.ARTICLE, Article.ARTICLE.ID.eq(1));
articleRecord.delete();
The delete method will return 1 if the operation was successful or 0 if deletion was not necessary. For example, when nothing was matched by the condition.
7. Conclusion
In this article, we’ve learned how to configure and create a simple CRUD application using the jOOQ framework. As usual, all source code is available over on GitHub.