Generate jOOQ classes from pure Java

Question

Is there a way to generate jOOQ classes from pure Java code? If not, what would be the closest alternative? Ideally, I'd like to do this in a gradle build.

I have found this answer, which links to this blog post. The essence of that post is this:

1. Start with a JPA Model
2. Convert it to a DDL script (.sql file full of CREATE statements)
3. Create a fresh HSQLDB file, and fill it with tables by running that DDL script on it. Save the resulting DB to disk.
4. Load that DB from disk and run jOOQ code generation on it
5. use the resulting generated jOOQ classes.

There are three things that bother me about this approach:

• That blog post does everything in Maven (& Ant). I'd like to use Gradle, but I can work around this.
• I have to start with JPA. I'd prefer starting with a pure jOOQ definition of my schema. Does jOOQ have a schema definition DSL/API?
• The intermediate steps (DDL script, and HSQLDB creation) are not needed as an end product. Instead, they are only needed to convert the JPA model into something jOOQ understands as an input. There should be a tidier (java-only) way, even if jOOQ doesn't have its own schema definition API. Is there one?

Answer 1

Jooq needs some tables to be created before hand. You can use Flyway for that (and you should use its migrations too).

Once you have all your tables, you can use this snippet to have Jooq generate the source files for your tables:

import org.jooq.util.GenerationTool;
import org.jooq.util.jaxb.*;

Configuration configuration = new Configuration()
    .withJdbc(new Jdbc()
        .withDriver("org.postgresql.Driver")
        .withUrl("jdbc:postgresql://localhost/your_database")
        .withUser("username")
        .withPassword("password"))
    .withGenerator(new Generator()
        .withName("org.jooq.util.DefaultGenerator")
        .withDatabase(new Database()
                .withName("org.jooq.util.postgres.PostgresDatabase")
                .withIncludes(".*")
                .withSchemata(new Schema().withInputSchema("your_schema"))
        )
        .withTarget(new Target()
            .withPackageName("jooq.generate")
            .withDirectory("src/main/java")));
try {
  GenerationTool.generate(configuration);
} catch (Exception e) {
  e.printStackTrace();
}

This will leave some .java files on src/main/java path. Configure the snippet with your database settings, etc.

This is roughly the way we do things in our projects at work. Jooq plays very nicely with Flyway. You can check Jooq's documentation about Code generation.

There is a plugin for maven that we don't use because we have a multi-tenant setup that requires some runtime configuration on database migrations and code generation.

I guess that once you have prepared your Jooq generation in a static main method on some class, you could run it from a gradle task. I can only point you to Jooq's documentation on running it from gradle here

Edit: After taking a look into the documentation and my own generated code after curiosity, I see that you could extend Record and TableImpl<Record> classes to produce your manually created schema classes.

You can see here a small example snippet of the important parts on Record and Table classes.

Anyway, take into account that Jooq (and Flyway) don't want you to avoid SQL but embrace it. The normal way to do things is

1. you create database objects using SQL scripts
2. you use Jooq generator to produce your schema classes
3. you use them with Jooq's DSL context.

Answer 2

Based on @ggalmazor's answer, I ended up using mostly Java code, with a minimal core of SQL scripts. Now my code generation process consists of 3 simple steps:

1. Write SQL DDL scripts (i.e. CREATE TABLE statements)
2. Run Flyway, create a H2 in-memory database.
3. Run jOOQ on the very same database to generate Java code.

Both Flyway, and jOOQ are fully configured in Java code; no xml config files are needed.

This is my code:

public class Main {

    public static void main(String... args) {
        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
        ds.setUser("u");
        ds.setPassword("p");

        // Flyway
        Flyway flyway = new Flyway();
        flyway.setDataSource(ds);
        flyway.setLocations("classpath:com.example.datadefinition.flyway.migrations");
        flyway.migrate();

        // jOOQ
        try (Connection c = ds.getConnection()) {
            Configuration configuration = new Configuration()
                .withGenerator(new Generator()
                    .withName(MyOwnGenerator.class.getCanonicalName())
                    .withDatabase(new Database()
                        .withName(H2Database.class.getCanonicalName())
                        .withIncludes(".*")
                        .withExcludes("")
                        .withInputSchema("PUBLIC")
                    )
                    .withTarget(new Target()
                        .withPackageName("com.example.lib.data")
                        // jOOQ will create package folders for com.example.lib.data
                        .withDirectory("../sibling-project/src/main")
                    )
                );

            GenerationTool tool = new GenerationTool();
            tool.setConnection(c);
            tool.run(configuration);
        } catch (SQLException e) {
            // sql connection problems
            e.printStackTrace();
        } catch (Exception e) {
            // run failed
            e.printStackTrace();
        }
    }
}

public class MyOwnGenerator extends JavaGenerator {

    public SwsGenerator() {
        setStrategy(new MyOwnGeneratorStrategy());
    }

}

public class MyOwnGeneratorStrategy extends DefaultGeneratorStrategy {

}