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

mkdir -p /var/tmp/jooq && (
cd /var/tmp/jooq
wget -qc "https://www.jooq.org/download/license-accepted?type=oss&file=jOOQ-3.15.1.zip"
wget -qc https://repo1.maven.org/maven2/javax/xml/bind/jaxb-api/2.3.1/jaxb-api-2.3.1.jar
wget -qc https://jdbc.postgresql.org/download/postgresql-42.2.23.jar
for i in *.zip ; do unzip -qo $i ; done
) && export CLASSPATH=.:$(find /var/tmp/jooq -name "*.jar" | paste -sd:)

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

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://franck:[email protected]:5433/yb_demo_northwind

(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="http://www.jooq.org/xsd/jooq-codegen-3.14.0.xsd">
  <jdbc>
    <driver>org.postgresql.Driver</driver>
    <url>jdbc:postgresql://yb1.pachot.net:5433/yb_demo_northwind</url>
    <user>franck</user>
    <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>northwind.generated</packageName>
      <directory>.</directory>
    </target>
  </generator>
</configuration>

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

[opc@C jooq]$ java org.jooq.codegen.GenerationTool northwind.xml

The result is Java classes to access my schema:

[opc@C jooq]$ tree northwind 
northwind
└── generated
    ├── DefaultCatalog.java
    ├── Keys.java
    ├── Public.java
    ├── tables
    │   ├── Categories.java
    │   ├── CustomerCustomerDemo.java
    │   ├── CustomerDemographics.java
    │   ├── Customers.java
    │   ├── Employees.java
    │   ├── EmployeeTerritories.java
    │   ├── OrderDetails.java
    │   ├── Orders.java
    │   ├── Products.java
    │   ├── records
    │   │   ├── CategoriesRecord.java
    │   │   ├── CustomerCustomerDemoRecord.java
    │   │   ├── CustomerDemographicsRecord.java
    │   │   ├── CustomersRecord.java
    │   │   ├── EmployeesRecord.java
    │   │   ├── EmployeeTerritoriesRecord.java
    │   │   ├── OrderDetailsRecord.java
    │   │   ├── OrdersRecord.java
    │   │   ├── ProductsRecord.java
    │   │   ├── RegionRecord.java
    │   │   ├── ShippersRecord.java
    │   │   ├── SuppliersRecord.java
    │   │   ├── TerritoriesRecord.java
    │   │   └── UsStatesRecord.java
    │   ├── Region.java
    │   ├── Shippers.java
    │   ├── Suppliers.java
    │   ├── Territories.java
    │   └── UsStates.java
    └── Tables.java

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:

Jul 19, 2021 2:27:05 PM org.jooq.tools.JooqLogger info
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:
 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:

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