jOOQ on YugabyteDB
Franck Pachot
Posted on July 19, 2021
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/
Posted on July 19, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related