DEV Community

Cover image for jOOQ on YugabyteDB
Franck Pachot for YugabyteDB

Posted on • Updated on

jOOQ on YugabyteDB

I know jOOQ for a while, and I've recommended it many times to database developers because it overcomes two of the major problems of SQL:

  • SQL queries being embedded as character strings, without compile-time validation
  • SQL syntax being dependent on the database engine

But, as I'm not a Java developer, I actually never used it myself. This was in my to-do for a long time, so here is my first jOOQ program 😎 The occasion is there: verify that it works with YugabyteDB. Even without a specific dialect, I expect it to work seamlessly, because YugabyteDB is using the same query layer as PostgreSQL.

I'm running on a 4 vCPU (Arm) Oracle Cloud Developer Image which is free and contains all developer tools. I'll use GraalVM for my JDK:

[opc@C jooq]$ sudo update-alternatives --config java <<<6

There are 6 programs which provide 'java'.

  Selection    Command
   1           java-1.8.0-openjdk.aarch64 (/usr/lib/jvm/java-1.8.0-openjdk-
   2           java-11-openjdk.aarch64 (/usr/lib/jvm/java-11-openjdk-
   3           /usr/java/jdk1.8.0_291-aarch64/bin/java
*  4           /usr/java/jdk-
   5           /usr/java/jdk-
 + 6           /usr/lib64/graalvm/graalvm21-ee-java11/bin/java

[opc@C jooq]$ java --version
java 11.0.11 2021-04-20 LTS
Java(TM) SE Runtime Environment GraalVM EE 21.1.0 (build 11.0.11+9-LTS-jvmci-21.1-b05)
Java HotSpot(TM) 64-Bit Server VM GraalVM EE 21.1.0 (build 11.0.11+9-LTS-jvmci-21.1-b05, mixed mode, sharing)
[opc@C jooq]$
Enter fullscreen mode Exit fullscreen mode


I'll not build a Maven project for this simple test. Just get the libraries I need in a directory and build my classpath from there:

mkdir -p /var/tmp/jooq && (
cd /var/tmp/jooq
wget -qc ""
wget -qc
wget -qc
for i in *.zip ; do unzip -qo $i ; done
) && export CLASSPATH=.:$(find /var/tmp/jooq -name "*.jar" | paste -sd:)

Enter fullscreen mode Exit fullscreen mode

At this point CLASSPATH includes my current directory and the .jar downloaded above. I have downloaded the latest jOOQ (which is free for Open Source databases, and both PostgreSQL and YugabyteDB are free in this context).

I have added JAXB APIs because of the following:

java.lang.NoClassDefFoundError: javax/xml/bind/annotation/XmlSchema
Enter fullscreen mode Exit fullscreen mode

And I've added PostgreSQL JDBC driver as I'll connect to YugabyteDB.

I have a YugabyteDB database with the Northwind demo schema:

psql postgres://
Enter fullscreen mode Exit fullscreen mode

(I leave it opened publicly so that you can copy-paste all the code, play with it but don't break it please)

Code Generator

Here is my configuration for code generation:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="">
Enter fullscreen mode Exit fullscreen mode

which I save in a northwind.xml file, and run:

[opc@C jooq]$ java org.jooq.codegen.GenerationTool northwind.xml
Enter fullscreen mode Exit fullscreen mode

The result is Java classes to access my schema:

[opc@C jooq]$ tree northwind 

└── generated
    ├── tables
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   ├── records
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   ├──
    │   │   └──
    │   ├──
    │   ├──
    │   ├──
    │   ├──
    │   └──

Enter fullscreen mode Exit fullscreen mode

I'll not go into the details, I just followed some bits of jOOQ documentation and used to write some SQL and see the translation into the jOOQ "output dialect".

Query with jOOQ

So my goal was to run a simple query with SELECT ... FROM ... JOIN ... WHERE ... ORDER BY in order to list some orders per products, with the following output:

Jul 19, 2021 2:27:05 PM info

jOOQ tip of the day: While you don't have to use jOOQ's code generator, there are *lots* of awesome features you're missing out on if you're not using it!

Connected to version version:
 dialect detected:

 Order Product                                  Quantity Date

 10403 Chocolade                                      70 1997-01-03
 10704 Chocolade                                      24 1997-10-14
 10453 Chocolade                                      15 1997-02-21
 10507 Chocolade                                      15 1997-04-15
 10814 Chocolade                                       8 1998-01-05
 10604 Chocolade                                       6 1997-07-18
Enter fullscreen mode Exit fullscreen mode

Here is my code:

// JDBC imports
import java.sql.*;

// jOOQ imports
import org.jooq.*;
import org.jooq.impl.DSL;

// generated code for the schema
import static northwind.generated.Tables.*;
import        northwind.generated.tables.*;

public class Northwind {

 public static void main( String[] args ) {

  // connection to my database

  try (Connection conn = DriverManager.getConnection(
   "jdbc:postgresql://", "franck", "Yugabyte"
  )) {

  // jOOQ context

   DSLContext create = DSL.using(conn);
   System.out.println( "Connected to version version:\n "
    + "\n dialect detected:\n "

   // Declaring SQL query aliases

   Products p ="p");
   Orders o ="o");
   OrderDetails d ="d");

   // Here is the SQL query

   Result<Record> result = create

   // print the header

    String.format("%6s %-40s %-6s %-20s"

   // print the rows fetched

   for(Record r:result) {
     String.format("%6d %-40s   %6d %-20s"

  catch (Exception e) { e.printStackTrace(); System.exit(255); }

Enter fullscreen mode Exit fullscreen mode

This is really nice: all the power of the SQL declarative language natively embedded in the Java procedural language. Want to test? Just copy paste in and run javac && java Northwind with the CLASSPATH above (current directory where I have this Northwind.class and the generated directory, as well as the downloaded JARs.

My connection string here goes on my database on a very limited free VM. Want to try it with your databases? have a look at our cloud with a free tier: (currently in beta). You can also install it where you want:

Top comments (0)