previous : next

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>

        
        
      </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")
      )
    )
  )

See the configuration XSD and programmatic code generation for more details.

// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
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"
        }
      }
    }
  }
}

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 11 support,
           org.jooq.pro-java-8   for commercial editions with Java 8 support,
           org.jooq.pro-java-6   for commercial editions with Java 6 support,
           org.jooq.trial        for the free trial edition with Java 11 support,
           org.jooq.trial-java-8 for the free trial edition with Java 8 support,
           org.jooq.trial-java-6 for the free trial edition with Java 6 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.10.8</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-8     for commercial editions with Java 8 support,
    //     org.jooq.pro-java-6     for commercial editions with Java 6 support,
    //     org.jooq.trial          for the free trial edition with Java 17 support,
    //     org.jooq.trial-java-8   for the free trial edition with Java 8 support,
    //     org.jooq.trial-java-6   for the free trial edition with Java 6 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.10.8")
}
dependencies {
    // Use org.jooq                for the Open Source Edition
    //     org.jooq.pro            for commercial editions with Java 17 support,
    //     org.jooq.pro-java-8     for commercial editions with Java 8 support,
    //     org.jooq.pro-java-6     for commercial editions with Java 6 support,
    //     org.jooq.trial          for the free trial edition with Java 17 support,
    //     org.jooq.trial-java-8   for the free trial edition with Java 8 support,
    //     org.jooq.trial-java-6   for the free trial edition with Java 6 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.10.8"
}

previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo