A demo application using CrateDB with jOOQ and the PostgreSQL JDBC driver. It uses the Gradle Build Tool and the Gradle plugin for jOOQ code generation.

It is intended as a basic example to demonstrate what currently works, and as a testing rig for eventually growing a full-fledged CrateDB extension. Contributions are welcome.

The idea of jOOQ is to generate typesafe code based on an SQL schema. Then, accessing a database table using the jOOQ DSL API looks like this:

// Fetch records, with filtering and sorting.
Result<Record> result = db.select()
        .from(AUTHOR)
        .where(AUTHOR.NAME.like("Ja%"))
        .orderBy(AUTHOR.NAME)
        .fetch();

// Iterate and display records.
for (Record record : result) {
    Integer id = record.getValue(AUTHOR.ID);
    String name = record.getValue(AUTHOR.NAME);
    System.out.println("id: " + id + ", name: " + name);
}

The code examples will demonstrate a few of the different use cases for jOOQ. That is, how to use the jOOQ DSL API based on code generated with jOOQ's code generator to take your database schema and reverse-engineer it into a set of Java classes, as well how to use the Dynamic SQL API by using jOOQ as a SQL builder without code generation.

In many cases, the schema is defined in the form of SQL scripts, which can be used with a database schema migration framework like Flyway, Liquibase, Bytebase, etc.

The DDLDatabase - Code generation from SQL files feature can be used to effectively reflect the database schema from SQL DDL files, without needing a database instance at all. The code provided within the src/generated directory has been generated like this.

Contributions to resolve any of those items will be welcome, see also backlog.

• jOOQ's code generator currently does not work with directly connecting to a real CrateDB database instance and reflecting the schema from there. With corresponding improvements to CrateDB, this can be made work in the future, see issue #10 - with reflection from the database. Right now, this example uses code generation based on SQL DDL files, which is also supported by jOOQ.
• Applying code generation based on the database schema (directly, or via SQL DDL) is only supported for schema definitions which use field types compatible with standard PostgreSQL, and understood by jOOQ. jOOQ does not know anything about any other special data types supported by CrateDB, and does not support it. When using special field types, like OBJECT, ARRAY, or IP, it will trip the code generator.
• Most of the jOOQ examples use uppercase letters for the database, table, and field names. Within this setup, we have only been able to make it work using lowercase letters.

1. Make sure Java 17 is installed.

2. Run CrateDB:

   docker run -it --rm --publish=4200:4200 --publish=5432:5432 \
       crate:latest -Cdiscovery.type=single-node

3. Invoke demo application:

   ./gradlew run

3. Invoke software tests:

   ./gradlew test

4. Generate the jOOQ sources from the main jOOQ configuration, see jooq.gradle:

   ./gradlew generateJooq