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:

There are 6 programs which provide 'java'.  Selection    Command
-----------------------------------------------
1 java-1.8.0-openjdk.aarch64 (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.292.b10-1.el8_4.aarch64/jre/bin/java)
2 java-11-openjdk.aarch64 (/usr/lib/jvm/java-11-openjdk-11.0.11.0.9-2.el8_4.aarch64/bin/java)
3 /usr/java/jdk1.8.0_291-aarch64/bin/java
* 4 /usr/java/jdk-16.0.1.0.1/bin/java
5 /usr/java/jdk-11.0.11.0.1/bin/java
+ 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]$

Libraries

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:

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:

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

I have a YugabyteDB database with the Northwind demo schema:

(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:

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

The result is Java classes to access my schema:

I’ll not go into the details, I just followed some bits of jOOQ documentation and used https://www.jooq.org/translate 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:

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:
11.2-YB-2.6.0.0-b0
dialect detected:
POSTGRES
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

Here is my code:

// 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://yb1.pachot.net:5433/yb_demo_northwind", "franck", "Yugabyte"
)) {
// jOOQ context DSLContext create = DSL.using(conn);
System.out.println( "Connected to version version:\n "
+conn.getMetaData().getDatabaseProductVersion()
+ "\n dialect detected:\n "
+create.dialect()
);
// Declaring SQL query aliases Products p = PRODUCTS.as("p");
Orders o = ORDERS.as("o");
OrderDetails d = ORDER_DETAILS.as("d");
// Here is the SQL query Result<Record> result = create
.select()
.from(p)
.join(d).on(p.PRODUCT_ID.eq(d.PRODUCT_ID))
.join(o).on(d.ORDER_ID.eq(o.ORDER_ID))
.where(p.PRODUCT_NAME.eq("Chocolade"))
.orderBy(d.QUANTITY.desc())
.fetch();
// print the header System.out.println(
String.format("%6s %-40s %-6s %-20s"
,"Order"
,"Product"
,"Quantity"
,"Date"
)
);
System.out.println();
// print the rows fetched for(Record r:result) {
System.out.println(
String.format("%6d %-40s %6d %-20s"
,r.getValue(d.ORDER_ID)
,r.getValue(p.PRODUCT_NAME)
,r.getValue(d.QUANTITY)
,r.getValue(o.ORDER_DATE)
)
);
}
}
catch (Exception e) { e.printStackTrace(); System.exit(255); }
System.exit(0);
}
}

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 Northwind.java and run javac Northwind.java && 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: https://www.yugabyte.com/cloud/ (currently in beta). You can also install it where you want: https://docs.yugabyte.com/latest/quick-start/

Developer Advocate at Yugabyte, Open Source distributed SQL database. Incidentally Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member