All versions: 3.11 | 3.10 | 3.9 | 3.8 | Development versions: 3.12

When using jOOQ's code generator, a whole set of meta data is generated with the generated artefacts, such as schemas, tables, columns, data types, constraints, default values, etc.

This meta data can be used to generate DDL CREATE statements in any SQL dialect, in order to partially restore the original schema again on a new database instance. This is particularly useful, for instance, when working with an Oracle production database, and an H2 in-memory test database. The following code produces the DDL for a schema:

// SCHEMA is the generated schema that contains a reference to all generated tables
Queries ddl =
DSL.using(configuration)
   .ddl(SCHEMA);
   
for (Query query : ddl.queries()) {
    System.out.println(query);
}

When executing the above, you should see something like the following:

              
create table "PUBLIC"."T_AUTHOR"(
  "ID" int not null,
  "FIRST_NAME" varchar(50) null,
  "LAST_NAME" varchar(50) not null,
  ...
  constraint "PK_T_AUTHOR"
    primary key ("ID")
)
create table "PUBLIC"."T_BOOK"(
  "ID" int not null,
  "AUTHOR_ID" int not null,
  "TITLE" varchar(400) not null,
  ...
  constraint "PK_T_BOOK"
    primary key ("ID")
)
...
alter table "PUBLIC"."T_BOOK"
  add constraint "FK_T_BOOK_AUTHOR_ID"
    foreign key ("AUTHOR_ID")
    references "T_AUTHOR" ("ID")

Do note that these features only restore parts of the original schema. For instance, vendor-specific storage clauses that are not available to jOOQ's generated meta data cannot be reproduced this way.

The jOOQ Logo