Getting Started with jOOQ – Building SQL Queries in Java

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

JPA and Hibernate are a great fit to implement persist and update use cases, and simple queries. But most applications need a lot more than that. You need to be able to use the full feature set of SQL to implement your queries. That’s why JPA supports native queries. But it’s not comfortable to use. You probably don’t want to provide your query as a simple String or to handle the differences between the various database dialects yourself.

Other libraries are much better suited to implement complex SQL queries. One of them is jOOQ. It provides you with a Java DSL that enables you to build SQL queries in a comfortable and type-safe way. It abstracts the technical difficulties of plain JDBC and handles the subtle differences of the various SQL dialects.

In this post, I will give you a basic introduction to jOOQ, before I show you how to integrate it with Hibernate in my next post.

So, let’s get started.

Dependencies

Before you can use jOOQ in your project, you need to add a few dependencies to it. The following code snippet shows the maven dependencies of the jOOQ community edition, which you can use with open-source databases. If you use other databases, like Oracle or SQL Server, you need to get one of jOOQ’s commercial licenses.

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>${version.jooq}</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta</artifactId>
  <version>${version.jooq}</version>
</dependency>
<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen</artifactId>
  <version>${version.jooq}</version>
</dependency>

Code Generation

The code generation step is optional but I highly recommend it. If you want to improve developer productivity and write your queries in a type-safe way, you should use jOOQ’s code generator. It creates Java classes that map your tables, sequences, stored procedures and more. You can use these classes to define your queries and to process the selected results. But more about that later.

jOOQ provides you with a set of code generators that you can use on the command line, within Eclipse and as a Maven plugin. They can generate jOOQ’s metamodel classes based on an existing database, an SQL script or your entity mappings.

Generating jOOQ classes based on a database

Let’s take a look at an example. I use a PostgreSQL with a simple test database containing the tables author, book, book_author and publisher.

Here is an example of a Maven build configuration that calls the code generator within Maven’s generate goal. The generator connects to the public schema of the jooq database on a PostgreSQL server on localhost. It writes the generated classes to the package org.thoughts.on.java.db in the folder target/generated-sources/jooq.

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>${version.jooq}</version>
 
  <executions>
    <execution>
      <goals>
        <goal>generate</goal>
      </goals>
    </execution>
  </executions>
 
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.4.1208</version>
    </dependency>
  </dependencies>
 
  <configuration>
    <jdbc>
      <driver>org.postgresql.Driver</driver>
      <url>jdbc:postgresql:jOOQ</url>
      <user>postgres</user>
      <password>postgres</password>
    </jdbc>

    <generator>
      <database>
        <name>org.jooq.util.postgres.PostgresDatabase</name>
        <includes>.*</includes>
        <excludes></excludes>
        <inputSchema>public</inputSchema>
      </database>
      <target>
        <packageName>org.thoughts.on.java.db</packageName>
        <directory>target/generated-sources/jooq</directory>
      </target>
    </generator>
  </configuration>
</plugin>

After you run the Maven build, you can find a set of classes in the packages org.thoughts.on.java.db, org.thoughts.on.java.db.tables and org.thoughts.on.java.db.tables.records.

The classes in the org.thoughts.on.java.db package provide convenient access to the schema and all tables, sequences, keys, and indexes. We don’t need these classes in this example.

We will only use the classes in the org.thoughts.on.java.db.tables package to reference tables and their columns in SQL queries and classes in the org.thoughts.on.java.db.tables.records package to handle the results of these queries.

Implementing Queries with jOOQ

The great thing about jOOQ is that the DSL is very similar to SQL’s syntax. So, if you’re familiar with SQL, you will have no problems to write your queries with jOOQ.

It all starts with the creation of a DSLContext which you need to initialize with a JDBC Connection and the SQLDialect you want to use. In this example, I’m using a PostgreSQL 9.4 database on localhost.

String user = "postgres";
String pass = "postgres";
String url = "jdbc:postgresql:jOOQ";

// Create a JDBC Connection
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
	// Create a context for your database
	DSLContext ctx = DSL.using(conn, SQLDialect.POSTGRES_9_4);
	
	// Do something useful ...

} catch (Exception e) {
	e.printStackTrace();
}

You can then use the DSLContext to create your queries.

A Simple Query

Let’s start with a simple query that retrieves all records from the book table.

Result<Record> result = ctx.select().from(BOOK).fetch();
for (Record r : result) {
	Long id = r.get(BOOK.ID);
	String title = r.get(BOOK.TITLE);
	Date publishingDate = r.get(BOOK.PUBLISHINGDATE);
	log.info("Book: id="+id+" title="+title+ " publishingDate="+publishingDate);
}

As you can see, the definition of such a query is easy, and the code looks almost like SQL. The parameter-less select method on the DSLContext defines a projection that includes all columns and the from method defines from which database table these records shall be selected.

Here you can see an advantage of the code generation. Instead of providing the name of the book table as a String, you can use a static attribute of the generated Book class. That class represents the book table and provides you with a strongly typed, static attribute for each column of the table. That enables code completion in your IDE. And if you integrate the code generation step into your build process, you can also be sure that your code always matches your table model. In case the table model changes, the code generator will update the Java classes, and your IDE will show you compile time errors at all code snippets you need to update.

After you have defined your query, you need to execute it and retrieve the result. In this example, I do that by calling the fetch method.

In the next step, you can process the result. The query returned a collection of Record interfaces. Each of them represents a record of the query result. As you can see in the code snippet, you can retrieve the value of each field in the result record by calling the get method with an attribute of the generated Book class that references one of the selected database columns. This attribute also provides the required type information to avoid any type casts. If you don’t use the code generator or use an alias in your projection, you can also provide a String with the name of the result set column.

A Slightly Less Simple Query

This time, I want to select the first name, last name and the number of books written by all authors whose last name starts with “Jan” and ends with “en”.

Result<Record3<String, String, Integer>> result = 
		ctx.select(
				AUTHOR.FIRSTNAME, 
				AUTHOR.LASTNAME, 
				DSL.count(BOOK_AUTHOR.BOOKID).as("bookCount"))
			.from(AUTHOR)
				.leftJoin(BOOK_AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
			.where(AUTHOR.LASTNAME.like("Jan%en"))
			.groupBy(AUTHOR.FIRSTNAME, AUTHOR.LASTNAME)
			.fetch();
for (Record r : result) {
	String firstName = r.get(AUTHOR.FIRSTNAME);
	String lastName = r.get(AUTHOR.LASTNAME);
	Integer bookCount = r.get("bookCount", int.class);
	System.out.println(firstName + " " + lastName + " wrote " + bookCount + " book(s).");
}

You can see again, that the Java code looks extremely similar to the SQL statement you want to create.

The select method defines the projection. I use the generated Author class to reference the firstname and lastname columns of the author table. jOOQ’s DSL class provides lots of methods that enable you to call SQL functions. I use it here to call SQL’s count function and define the alias bookCount for that field.

Then I define the FROM clause of the query. The from method returns a SelectJoinStep interface which enables you to define different types of join clauses or to combine the results of multiple queries with set operators, like UNION or INTERSECT. This is a huge advantage compared to JPA’s JPQL queries which I normally show on this blog. jOOQ enables you to use all SQL features so that you can benefit from the powerful query capabilities provided by your database.

Let’s continue by specifying the WHERE clause. You can do that by calling the where method with a String, an SQL query part, or one or more Conditions. I prefer to define my query in a type-safe way, so I use the generated Author class to reference the lastname column and to define the like expression. As you can see in the code snippet, I don’t define a bind parameter and just set the String “Jan%en” as its value. jOOQ automatically adds a bind parameter to the query and sets the provided value as the bind parameter value.

OK, almost done. We just need to add a GROUP BY clause for the columns firstname and lastname. Similar to the definition of the pervious clauses, you can do that by calling the groupBy method with references to the 2 database columns.

That’s all you need to do to define the query. The call of the fetch method executes the query and returns a Result interface which contains a collection of strongly typed Record interfaces. As in the previous example, you can then use that interface and jOOQ’s generated classes to process the query result.

Summary

As you have seen, jOOQ enables you to implement your queries with a type-safe DSL that looks pretty similar to SQL. One of its main benefits is that it abstracts the difficulties of JDBC and the differences of the SQL dialects without limiting you to a small subset of SQL.

In this post, I only showed you a few basic queries. But you can also build very complex SQL queries using recursion, window functions, and other advanced SQL features.

And I have good news for you, if you’re using Hibernate and want to start writing your more complex queries with jOOQ. Hibernate and jOOQ work really fine together. I show you how to integrate them in Hibernate & jOOQ – A Match Made in Heaven.