jOOQ – a short guide
You can use this guide to understand what jOOQ is, how to get started with it quickly and how jOOQ integrates with or compares to libraries like Spring and Hibernate.
What is jOOQ?
jOOQ is a popular Java database library, that lets you write typesafe SQL queries in Java. How does it work?
- You use jOOQ’s code generator to connect to your database and generate Java classes that model your database tables and columns.
- Instead of writing SQL String statements with plain JDBC, you will use those generated Java classes to write your SQL queries.
- jOOQ will conveniently turn these Java classes and queries into real SQL, execute them against the database and map the results back to Java code.
This is of course just the very high-level overview. If you want to know in more detail how jOOQ works: Read on.
jOOQ: Crash-Course
First: Your Database, Then: Your Java classes
Compared to other popular libraries like Hibernate, jOOQ takes a database-first or SQL-centric approach.
With Hibernate, you usually start writing your Java classes first and then let Hibernate or a tool like Liquibase or Flyway generate corresponding database tables.
With jOOQ, you start with your database. Your database and tables must already exist and you then use jOOQ’s code generator to generate Java classes for you.
Using jOOQ’s code generator
You can use jOOQ’s code generator in roughly three ways:
- Standalone: You need to download jOOQ from its website.
- Maven and jOOQ: You generate code with jOOQ’s Maven plugin.
- Gradle and jOOQ: You generate code with jOOQ’s Gradle plugin.
The generation process for all three options is always the same. Whereas in the standalone case you need to trigger generation manually, with Maven and Gradle plugins code generation will happen automatically as part of the build process.
In any case, you need a configuration file for your jOOQ’s code generator which is, by default, called library.xml. It looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.12.0.xsd">
<jdbc> // (1)
<driver>com.mysql.cj.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/library</url>
<user>root</user>
<password></password>
</jdbc>
<generator>
<database> // (2)
<!-- database specific options: What tables to schema/tables to include or exclujde ? What type of database? etc-->
<includes>.*</includes>
</database>
<target> // (3)
<!-- The destination package of your generated classes (within the destination directory) -->
<packageName>my.startup</packageName>
<!-- The destination directory of your generated classes. Using Maven directory layout here -->
<directory>c:/dev/myproject/src/main/java</directory>
</target>
</generator>
</configuration>
As you can see, the library.xml config file roughly boils down to:
- Your database url, username & password.
- Options like what tables to include/exclude in the schema generation.
- Where to put the generated classes.
Example:
Imagine your database has a USERS table. Running the code generator with the generator config will create (among many others) two classes:
- A my.startup.Users class, representing your Users database table.
- A my.startup.UsersRecord class, representing a row inside your Users table.
Let’s see how we can execute SQL queries with these generated classes:
jOOQ’s DSL
jOOQ has two classes, DSL and DSLContext (Domain Specific Language) that developers need to use to start writing SQL queries. You could also call these DSL classes SQL Builder classes, as they let you, well, build SQL queries.
As jOOQ is just a wrapper around JDBC, Java database basics apply and you need to get a database connection for jOOQ to work. You either open one yourself, or ask your connection pool to give you one.
Let’s have a look at a jOOQ DSL example, using Java’s DriverManager.
import java.sql.SQLException;
import static my.startup.Tables.*;
public class Main {
public static void main(String[] args) throws SQLException {
String userName = "root";
String password = "";
String url = "jdbc:mysql://localhost:3306/myhotstartup";
try (Connection conn = DriverManager.getConnection(url, userName, password)) { // (1)
DSLContext create = DSL.using(conn, SQLDialect.MYSQL); // (2)
Result<Record> result = create.select().from(USERS).fetch(); // (3)
for (Record r : result) { // (3)
Integer id = r.getValue(USERS.ID);
String username = r.getValue(USERS.USERNAME);
String email = r.getValue(USERS.EMAIL);
System.out.println("ID: " + id + " + email: " + email );
}
}
}
}
What’s happening here?
- You open up a database connection with plain JDBC. This is standard Java.
- You create jOOQ’s DSLContext that lets you execute queries against the database.
- You execute a simple select * from USERS with jOOQ and then iterate over the resulting rows/records, printing out user ids and emails.
That’s it.
jOOQ and CRUD queries
This short guide cannot give you a comprehensive explanation on every possible jOOQ query, so let’s have a look at some simple, common queries to get a basic understanding.
Do keep in mind, that jOOQ queries read almost exactly like the corresponding SQL query, so without strong SQL skills you will run into problems.
SQL select where
To execute a simple select * from USERS where id = :id you would execute this query:
UsersRecord record = dslContext.selectFrom(USERS)
.where(USERS.ID.eq(id))
.fetchAny();
// do something with record.getEmail()
With just a tiny change you would turn the query into select * from USERS where id in (:ids):
Result<UsersRecord> userRecords = dslContext.selectFrom(USERS)
.where(USERS.ID.in(ids))
.fetch();
// (for-loop over userRecords)
SQL Joins
Let’s have a look at joining two tables, like: select * from USERS u inner join PAYMENTS p on u.id = p.user_id:
Result<?> result = dslContext.select()
.from(USERS.join(PAYMENTS)
.on(PAYMENTS.USER_ID.eq(USERS.ID)))
.fetch();
// (for-loop over join records)
SQL update and delete
Finally, deletes (delete from USERS where id = 1) or updates (update USERS set email = :email, username = :username where id = 1) look like this:
dslContext.delete(USERS)
.where(USERS.ID.eq(1))
.execute();
dslContext.update(USERS)
.set(USERS.USERNAME, "John Rambo")
.set(USERS.EMAIL, "john@rambo.com")
.where(USERS.ID.eq(1))
.execute();
Learning jOOQ
If you like this article, you might also like my video course on jOOQ, recommended by its author, Lukas Eder.
Summary
Now you might understand why jOOQ calls itself typesafe database library. It lets you write Java code that looks like SQL. With the benefit that your generated DSL knows that e.g. user ids have to be numbers, user names have to be strings etc.
How does jOOQ integrate with Spring?
There’s is nothing stopping you from using jOOQ in combination with Spring. There are however various levels of integration between jOOQ and the vast Spring ecosystem.
@Transactional integration
In Spring applications you usually use the @Transactional annotation to define database transaction boundaries. To make jOOQ take part in these transactions, you’ll need to do some extra setup work.
Spring Boot integration
Spring Boot comes with a jOOQ autoconfiguration, which means it sets up the DSLContext and integrates jOOQ with Spring’s transaction handling for you – without you having to do anything apart from adding the following dependency to your Spring Boot project.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
<version>2.2.2.RELEASE</version> <!-- or your appropriate Spring boot version -->
</dependency>
Which then lets you do this:
import static my.startup.Tables.*;
@Service
public class UserService {
@Autowired
private DSLContext dslContext; // (1)
@Transactional // (2)
public boolean registerUser(String email) {
UsersRecord existingRecord = dslContext.selectFrom(USERS)
.where(USERS.EMAIL.eq(email))
.fetchAny();
if (existingRecord != null) {
return false;
}
// register user etc.
}
}
- Spring Boot creates the DSLContext automatically for you (dependent on a datasource).
- jOOQ will take part in the Spring transaction demarcated by @Transactional.
Spring Data
There is currently no native Spring Data project for jOOQ, like Spring Data JDBC or Spring Data JPA. Sorry.
How does jOOQ compare to other Java database frameworks?
jOOQ vs Hibernate
As already mentioned at the beginning of this guide, both are quite different. Hibernate takes a Java-first approach, whereas you (usually) write your Java classes and mappings first. Then you think about your database (tables).
jOOQ on the other hand is database or SQL first, it needs an existing database schema to work with and generate its Java classes from.
There is however nothing stopping you from using both libraries in the same project. For a quick intro on how to do that, see this excellent post from Thorben Janssen.
jOOQ vs MyBatis
MyBatis is a SQL templating language, where you write your SQL in XML files. For further information regarding the differences between the two, check out this stackoverflow thread.
MyBatis XML files look like this:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.example.UserMapper">
<select id="selectUser" resultType="User">
select * from Users where id = #{id}
</select>
</mapper>
Which lets you do this:
// sqlsessionfactory is a myBatis specific entry point
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectUser(1);
}
jOOQ vs QueryDSL
There’s a certain overlap in what jOOQ and QueryDSL do and QueryDSL also works with generated classes. Our example from above looks like this with QueryDSL:
// 'Q' classes are generated and let you access tables, columns etc QUser user = QUser.user; User john = queryFactory.selectFrom(user) .where(user.id.eq(1)) .fetchOne();
To sum it up, in JPA-(Lucene/Mongodb/JDO) based environments QueryDSL is often a good choice, in SQL-based environments jOOQ is the better choice.
Note however, that the QueryDSL has been quite stagnant for a while and is currently undergoing a project takeover.
Also have a look at this (older) article comparing the two: QueryDSL vs. jOOQ.