Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
DDLDatabase: Code generation from SQL files
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
In many cases, the schema is defined in the form of a SQL script, which can be used with Flyway, or some other database migration tool.
If you have a complete schema definition in a single file, or perhaps a set of incremental files that can reproduce your schema in any SQL dialect, then the DDLDatabase might be the right choice for you. It uses the SQL parser internally and applies all your DDL increments to an in-memory H2 database, in order to produce a replica of your schema prior to reverse engineering it again using ordinary code generation.
For example, the following database.sql script (the sample database from this manual) could be used:
CREATE TABLE language ( id NUMBER(7) NOT NULL PRIMARY KEY, cd CHAR(2) NOT NULL, description VARCHAR2(50) ); CREATE TABLE author ( id NUMBER(7) NOT NULL PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50) NOT NULL, date_of_birth DATE, year_of_birth NUMBER(7), distinguished NUMBER(1) ); CREATE TABLE book ( id NUMBER(7) NOT NULL PRIMARY KEY, author_id NUMBER(7) NOT NULL, title VARCHAR2(400) NOT NULL, published_in NUMBER(7) NOT NULL, language_id NUMBER(7) NOT NULL, CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(id), CONSTRAINT fk_book_language FOREIGN KEY (language_id) REFERENCES language(id) ); CREATE TABLE book_store ( name VARCHAR2(400) NOT NULL UNIQUE ); CREATE TABLE book_to_book_store ( name VARCHAR2(400) NOT NULL, book_id INTEGER NOT NULL, stock INTEGER, PRIMARY KEY(name, book_id), CONSTRAINT fk_b2bs_book_store FOREIGN KEY (name) REFERENCES book_store (name) ON DELETE CASCADE, CONSTRAINT fk_b2bs_book FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE );
While the script uses pretty standard SQL constructs, you may well use some vendor-specific extensions, and even DML statements in between to set up your schema - it doesn't matter. You will simply need to set up your code generation configuration as follows:
XML (standalone and maven)
Programmatic
Gradle (Kotlin)
Gradle (Groovy)
Gradle (third party)
<configuration>
<generator>
<database>
<name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
<properties>
<!-- Specify the location of your SQL script.
You may use ant-style file matching, e.g. /path/**/to/*.sql
Where:
- ** matches any directory subtree
- * matches any number of characters in a directory / file name
- ? matches a single character in a directory / file name -->
<property>
<key>scripts</key>
<value>src/main/resources/database.sql</value>
</property>
<!-- The sort order of the scripts within a directory, where:
- semantic: sorts versions, e.g. v-3.10.0 is after v-3.9.0 (default)
- alphanumeric: sorts strings, e.g. v-3.10.0 is before v-3.9.0
- flyway: sorts files the same way as flyway does
- none: doesn't sort directory contents after fetching them from the directory -->
<property>
<key>sort</key>
<value>semantic</value>
</property>
<!-- The default schema for unqualified objects:
- public: all unqualified objects are located in the PUBLIC (upper case) schema
- none: all unqualified objects are located in the default schema (default)
This configuration can be overridden with the schema mapping feature -->
<property>
<key>unqualifiedSchema</key>
<value>none</value>
</property>
<!-- The default name case for unquoted objects:
- as_is: unquoted object names are kept unquoted
- upper: unquoted object names are turned into upper case (most databases)
- lower: unquoted object names are turned into lower case (e.g. PostgreSQL) -->
<property>
<key>defaultNameCase</key>
<value>as_is</value>
</property>
</properties>
</database>
</generator>
</configuration>
See the configuration XSD, standalone code generation, and maven code generation for more details.
new org.jooq.meta.jaxb.Configuration()
.withGenerator(new Generator()
.withDatabase(new Database()
.withName("org.jooq.meta.extensions.ddl.DDLDatabase")
.withProperties(
// Specify the location of your SQL script.
// You may use ant-style file matching, e.g. /path/**/to/*.sql
//
// Where:
// - ** matches any directory subtree
// - * matches any number of characters in a directory / file name
// - ? matches a single character in a directory / file name
new Property()
.withKey("scripts")
.withValue("src/main/resources/database.sql"),
// The sort order of the scripts within a directory, where:
//
// - semantic: sorts versions, e.g. v-3.10.0 is after v-3.9.0 (default)
// - alphanumeric: sorts strings, e.g. v-3.10.0 is before v-3.9.0
// - flyway: sorts files the same way as flyway does
// - none: doesn't sort directory contents after fetching them from the directory
new Property()
.withKey("sort")
.withValue("semantic"),
// The default schema for unqualified objects:
//
// - public: all unqualified objects are located in the PUBLIC (upper case) schema
// - none: all unqualified objects are located in the default schema (default)
//
// This configuration can be overridden with the schema mapping feature
new Property()
.withKey("unqualifiedSchema")
.withValue("none"),
// The default name case for unquoted objects:
//
// - as_is: unquoted object names are kept unquoted
// - upper: unquoted object names are turned into upper case (most databases)
// - lower: unquoted object names are turned into lower case (e.g. PostgreSQL)
new Property()
.withKey("defaultNameCase")
.withValue("as_is")
)
)
)
See the configuration XSD and programmatic code generation for more details.
import org.jooq.meta.jaxb.*
configuration {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Specify the location of your SQL script.
// You may use ant-style file matching, e.g. /path/**/to/*.sql
//
// Where:
// - ** matches any directory subtree
// - * matches any number of characters in a directory / file name
// - ? matches a single character in a directory / file name
property {
key = "scripts"
value = "src/main/resources/database.sql"
}
// The sort order of the scripts within a directory, where:
//
// - semantic: sorts versions, e.g. v-3.10.0 is after v-3.9.0 (default)
// - alphanumeric: sorts strings, e.g. v-3.10.0 is before v-3.9.0
// - flyway: sorts files the same way as flyway does
// - none: doesn't sort directory contents after fetching them from the directory
property {
key = "sort"
value = "semantic"
}
// The default schema for unqualified objects:
//
// - public: all unqualified objects are located in the PUBLIC (upper case) schema
// - none: all unqualified objects are located in the default schema (default)
//
// This configuration can be overridden with the schema mapping feature
property {
key = "unqualifiedSchema"
value = "none"
}
// The default name case for unquoted objects:
//
// - as_is: unquoted object names are kept unquoted
// - upper: unquoted object names are turned into upper case (most databases)
// - lower: unquoted object names are turned into lower case (e.g. PostgreSQL)
property {
key = "defaultNameCase"
value = "as_is"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
configuration {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Specify the location of your SQL script.
// You may use ant-style file matching, e.g. /path/**/to/*.sql
//
// Where:
// - ** matches any directory subtree
// - * matches any number of characters in a directory / file name
// - ? matches a single character in a directory / file name
property {
key = "scripts"
value = "src/main/resources/database.sql"
}
// The sort order of the scripts within a directory, where:
//
// - semantic: sorts versions, e.g. v-3.10.0 is after v-3.9.0 (default)
// - alphanumeric: sorts strings, e.g. v-3.10.0 is before v-3.9.0
// - flyway: sorts files the same way as flyway does
// - none: doesn't sort directory contents after fetching them from the directory
property {
key = "sort"
value = "semantic"
}
// The default schema for unqualified objects:
//
// - public: all unqualified objects are located in the PUBLIC (upper case) schema
// - none: all unqualified objects are located in the default schema (default)
//
// This configuration can be overridden with the schema mapping feature
property {
key = "unqualifiedSchema"
value = "none"
}
// The default name case for unquoted objects:
//
// - as_is: unquoted object names are kept unquoted
// - upper: unquoted object names are turned into upper case (most databases)
// - lower: unquoted object names are turned into lower case (e.g. PostgreSQL)
property {
key = "defaultNameCase"
value = "as_is"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
generationTool {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Specify the location of your SQL script.
// You may use ant-style file matching, e.g. /path/**/to/*.sql
//
// Where:
// - ** matches any directory subtree
// - * matches any number of characters in a directory / file name
// - ? matches a single character in a directory / file name
property {
key = "scripts"
value = "src/main/resources/database.sql"
}
// The sort order of the scripts within a directory, where:
//
// - semantic: sorts versions, e.g. v-3.10.0 is after v-3.9.0 (default)
// - alphanumeric: sorts strings, e.g. v-3.10.0 is before v-3.9.0
// - flyway: sorts files the same way as flyway does
// - none: doesn't sort directory contents after fetching them from the directory
property {
key = "sort"
value = "semantic"
}
// The default schema for unqualified objects:
//
// - public: all unqualified objects are located in the PUBLIC (upper case) schema
// - none: all unqualified objects are located in the default schema (default)
//
// This configuration can be overridden with the schema mapping feature
property {
key = "unqualifiedSchema"
value = "none"
}
// The default name case for unquoted objects:
//
// - as_is: unquoted object names are kept unquoted
// - upper: unquoted object names are turned into upper case (most databases)
// - lower: unquoted object names are turned into lower case (e.g. PostgreSQL)
property {
key = "defaultNameCase"
value = "as_is"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
Additional properties
Additional properties include:
-
logExecutedQueries: Whether queries that are executed by the DDLDatabase should be logged for debugging purposes and auditing purposes. -
logExecutionResults: Whether results that are obtained after executing queries by the DDLDatabase should be logged for debugging and auditing purposes.
Ignoring unsupported content
The jOOQ parser supports parsing everything that is representable through the jOOQ API, as well as ignores some well known vendor specific syntax. But RDBMS have a lot more features and syntax that are not known to jOOQ. In this case, you can specify two comment tokens around the SQL syntax that jOOQ should ignore. The tokens are located in ordinary single line or multi line comments, so they do not affect your DDL scripts in any other way. For example:
-- [jooq ignore start] -- Anything between these two tokens is ignored by the jOOQ parser CREATE EXTENSION postgis; -- [jooq ignore stop] CREATE TABLE a (i INT); CREATE TABLE b (i INT); /* [jooq ignore start] */ -- This table will not be generated by jOOQ: CREATE TABLE c (i INT); /* [jooq ignore stop] */
The tokens can be overridden, or the feature can be turned off entirely using the following properties:
XML (standalone and maven)
Programmatic
Gradle (Kotlin)
Gradle (Groovy)
Gradle (third party)
<configuration>
<generator>
<database>
<name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
<properties>
<!-- Turn on/off ignoring contents between such tokens. Defaults to true -->
<property>
<key>parseIgnoreComments</key>
<value>true</value>
</property>
<!-- Change the starting token -->
<property>
<key>parseIgnoreCommentStart</key>
<value>[jooq ignore start]</value>
</property>
<!-- Change the stopping token -->
<property>
<key>parseIgnoreCommentStop</key>
<value>[jooq ignore stop]</value>
</property>
</properties>
</database>
</generator>
</configuration>
See the configuration XSD, standalone code generation, and maven code generation for more details.
new org.jooq.meta.jaxb.Configuration()
.withGenerator(new Generator()
.withDatabase(new Database()
.withName("org.jooq.meta.extensions.ddl.DDLDatabase")
.withProperties(
// Turn on/off ignoring contents between such tokens. Defaults to true
new Property()
.withKey("parseIgnoreComments")
.withValue(true),
// Change the starting token
new Property()
.withKey("parseIgnoreCommentStart")
.withValue("[jooq ignore start]"),
// Change the stopping token
new Property()
.withKey("parseIgnoreCommentStop")
.withValue("[jooq ignore stop]")
)
)
)
See the configuration XSD and programmatic code generation for more details.
import org.jooq.meta.jaxb.*
configuration {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Turn on/off ignoring contents between such tokens. Defaults to true
property {
key = "parseIgnoreComments"
isValue = true
}
// Change the starting token
property {
key = "parseIgnoreCommentStart"
value = "[jooq ignore start]"
}
// Change the stopping token
property {
key = "parseIgnoreCommentStop"
value = "[jooq ignore stop]"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
configuration {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Turn on/off ignoring contents between such tokens. Defaults to true
property {
key = "parseIgnoreComments"
value = true
}
// Change the starting token
property {
key = "parseIgnoreCommentStart"
value = "[jooq ignore start]"
}
// Change the stopping token
property {
key = "parseIgnoreCommentStop"
value = "[jooq ignore stop]"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
generationTool {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Turn on/off ignoring contents between such tokens. Defaults to true
property {
key = "parseIgnoreComments"
value = true
}
// Change the starting token
property {
key = "parseIgnoreCommentStart"
value = "[jooq ignore start]"
}
// Change the stopping token
property {
key = "parseIgnoreCommentStop"
value = "[jooq ignore stop]"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
Ad-hoc SQL
You can provide additional SQL that is prepended to your scripts to initialise the DDLDatabase, in case it can interpret it. This can also be used instead of providing scripts for quick code generation testing use-cases:
XML (standalone and maven)
Programmatic
Gradle (Kotlin)
Gradle (Groovy)
Gradle (third party)
<configuration>
<generator>
<database>
<name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
<properties>
<!-- Add additional SQL that is interpreted *before* any scripts -->
<property>
<key>sql</key>
<value>create table t (i int primary key);</value>
</property>
<!-- The usual scripts -->
<property>
<key>scripts</key>
<value>src/main/resources/database.sql</value>
</property>
</properties>
</database>
</generator>
</configuration>
See the configuration XSD, standalone code generation, and maven code generation for more details.
new org.jooq.meta.jaxb.Configuration()
.withGenerator(new Generator()
.withDatabase(new Database()
.withName("org.jooq.meta.extensions.ddl.DDLDatabase")
.withProperties(
// Add additional SQL that is interpreted *before* any scripts
new Property()
.withKey("sql")
.withValue("create table t (i int primary key);"),
// The usual scripts
new Property()
.withKey("scripts")
.withValue("src/main/resources/database.sql")
)
)
)
See the configuration XSD and programmatic code generation for more details.
import org.jooq.meta.jaxb.*
configuration {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Add additional SQL that is interpreted *before* any scripts
property {
key = "sql"
value = "create table t (i int primary key);"
}
// The usual scripts
property {
key = "scripts"
value = "src/main/resources/database.sql"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
configuration {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Add additional SQL that is interpreted *before* any scripts
property {
key = "sql"
value = "create table t (i int primary key);"
}
// The usual scripts
property {
key = "scripts"
value = "src/main/resources/database.sql"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
generationTool {
generator {
database {
name = "org.jooq.meta.extensions.ddl.DDLDatabase"
properties {
// Add additional SQL that is interpreted *before* any scripts
property {
key = "sql"
value = "create table t (i int primary key);"
}
// The usual scripts
property {
key = "scripts"
value = "src/main/resources/database.sql"
}
}
}
}
}
See the configuration XSD and gradle code generation for more details.
Dependencies
Note that the org.jooq.meta.extensions.ddl.DDLDatabase class is located in an external dependency, which needs to be placed on the classpath of the jOOQ code generator. E.g. using Maven:
Maven
Gradle (Kotlin)
Gradle (Groovy)
<dependency>
<!-- Use org.jooq for the Open Source Edition
org.jooq.pro for commercial editions with Java 17 support,
org.jooq.pro-java-11 for commercial editions with Java 11 support,
org.jooq.pro-java-8 for commercial editions with Java 8 support,
org.jooq.trial for the free trial edition with Java 17 support,
org.jooq.trial-java-11 for the free trial edition with Java 11 support,
org.jooq.trial-java-8 for the free trial edition with Java 8 support
Note: Only the Open Source Edition is hosted on Maven Central.
Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk -->
<groupId>org.jooq</groupId>
<artifactId>jooq-meta-extensions</artifactId>
<version>3.19.16</version>
</dependency>
dependencies {
// Use org.jooq for the Open Source Edition
// org.jooq.pro for commercial editions with Java 17 support,
// org.jooq.pro-java-11 for commercial editions with Java 11 support,
// org.jooq.pro-java-8 for commercial editions with Java 8 support,
// org.jooq.trial for the free trial edition with Java 17 support,
// org.jooq.trial-java-11 for the free trial edition with Java 11 support,
// org.jooq.trial-java-8 for the free trial edition with Java 8 support
//
// Note: Only the Open Source Edition is hosted on Maven Central.
// Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
// See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk
implementation("org.jooq:jooq-meta-extensions:3.19.16")
}
dependencies {
// Use org.jooq for the Open Source Edition
// org.jooq.pro for commercial editions with Java 17 support,
// org.jooq.pro-java-11 for commercial editions with Java 11 support,
// org.jooq.pro-java-8 for commercial editions with Java 8 support,
// org.jooq.trial for the free trial edition with Java 17 support,
// org.jooq.trial-java-11 for the free trial edition with Java 11 support,
// org.jooq.trial-java-8 for the free trial edition with Java 8 support
//
// Note: Only the Open Source Edition is hosted on Maven Central.
// Install the others locally using the provided scripts, or access them from here: https://repo.jooq.org
// See the JDK version support matrix here: https://www.jooq.org/download/support-matrix-jdk
implementation "org.jooq:jooq-meta-extensions:3.19.16"
}
Feedback
Do you have any feedback about this page? We'd love to hear it!