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:YugabyteDB@yb1.pachot.net: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/

    50

    This website collects cookies to deliver better user experience

    jOOQ on YugabyteDB