Mind is Software

Ying’s thoughts about software and business

jOOQ Introduction

This is the part 1 of a study note based on the official jOOQ document. It describes the reasons for being of jOOQ and a simple sample.

Reason for Being

SQL is big, complex and important enough to deserve a JAVA API just for it.

Why not JDBC and plain SQL:

  • No typesafety
  • No syntax safety
  • No bind value index safety
  • Verbose SQL String concatenation
  • Boring bind value indexing techniques
  • Verbose resource and exception handling in JDBC
  • A very “stateful”, not very object-oriented JDBC API, which is hard to use

Various best practices have been implemented in jOOQ

  • Typesafe database objects
  • Typesafe DSL for SQL construction
  • Covenient execution and result fetching
  • SQL dialect abstraction
  • SQL logging and debugging

jOOQ can work with Hibernate, JDBC and Spring Data. In those scenarios, it can be used as a SQL builder with or without code generation. It can execute jOOQ or plain SQL queries. It support CRUD operations.

Additionally, jOOQ ships with a lot of tools including execute listener, logging, stored procedures, batch execution, data importing and exporting.

A Quick Tutorial

We use Java 11 to create a simple sample console application.

Step 1: Database and Data

Run the following SQL to create database, table and insert data.

CREATE DATABASE `library`;
USE `library`;
CREATE TABLE `author` (
`id` int NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);

INSERT INTO author (id, first_name, last_name)
VALUES (1 , 'George' , 'Orwell');
INSERT INTO author (id, first_name, last_name)
VALUES (2 , 'Paulo' , 'Coelho');

Step 2: Generator Config

Create a library.xml file with the following content:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.11.0.xsd">
<!-- Configure the database connection here -->
<jdbc>
    <driver>com.mysql.jdbc.Driver</driver>
    <url>jdbc:mysql://localhost:3306/library</url>
    <user>root</user>
    <password>mysql-password</password>
</jdbc>
<generator>
    <name>org.jooq.codegen.JavaGenerator</name>
    <database>
        <name>org.jooq.meta.mysql.MySQLDatabase</name>
        <inputSchema>library</inputSchema>
        <includes>.*</includes>
        <excludes></excludes>
    </database>
    <target>
        <!-- The package of generated classes within the destination directory) -->
        <packageName>test.generated</packageName>
        <!-- The directory of your generated classes. Using Maven directory layout here -->
        <directory>/path/to/your/project/src/main/java</directory>
    </target>
</generator>
</configuration>

Step 3: Code Generation

Download jOOQ and MySQL Connector/J. However, to use Java 11, we need to download and use some old modules. This migration guide and this SO thread have the details.

For code generation, it uses the following two:

  • JAXB in the packages javax.xml.bind.*
  • the JavaBeans Activation Framework (JAF) in javax.activation

All can be downloaded from mvn repository. Put all required jars (see the following command) in a single folder.

java -cp jooq-3.11.11.jar:jooq-meta-3.11.11.jar:jooq-codegen-3.11.11.jar:mysql-connector-java-8.0.16.jar:jaxb-api.jar:jaxb-core.jar:jaxb-impl.jar:activation.jar:. org.jooq.codegen.GenerationTool library.xml

It should generate a set of Java files based on the database data structure.

Step 4: The Project File

The build.gradle file has the following dependencies:

implementation group: 'org.jooq', name: 'jooq', version: '3.11.11'
implementation group: 'org.jooq', name: 'jooq-meta', version: '3.11.11'
implementation group: 'org.jooq', name: 'jooq-codegen', version: '3.11.11'

implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.16'


// for Java 11 and jOOQ 3.11.11 or before
implementation group: 'javax.xml.bind', name: 'jaxb-api', version: '2.3.1'
implementation group: 'javax.activation', name: 'javax.activation-api', version: '1.2.0'
implementation group: 'javax.annotation', name: 'javax.annotation-api', version: '1.3.2'

Step 5: The Application

After coping generated files to the project, the sample application should be up and running with the following content.

package test.jooq

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

import static test.generated.Tables.*;
import java.sql.*;

public class Main {

    public static void main(String[] args) throws SQLException {
        String userName = "root";
        String password = "mysql-password";
        String url = "jdbc:mysql://localhost:3306/library";

        // Connection is the only JDBC resource that we need
        // PreparedStatement and ResultSet are handled by jOOQ, internally
        Connection conn = DriverManager.getConnection(url, userName, password);
        try (conn) {
            var create = DSL.using(conn, SQLDialect.MYSQL);
            var result = create.select().from(AUTHOR).fetch();
            for (var r : result) {
                Integer id = r.getValue(AUTHOR.ID);
                String firstName = r.getValue(AUTHOR.FIRST_NAME);
                String lastName = r.getValue(AUTHOR.LAST_NAME);
                System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
            }
        }
        // For the sake of this tutorial, let's keep exception handling simple
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Share