上一节 : 下一节

可用版本： Dev (3.21) | 最新 (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11

DDLDatabase：从 SQL 文件生成代码

适用于 ✅ 开源版   ✅ 专业版   ✅ 企业版

在许多情况下，模式以 SQL 脚本的形式定义，该脚本可以与 Flyway 或其他数据库迁移工具一起使用。

如果在一个文件中包含完整的模式定义，或者可能是一组增量文件，这些文件可以在任何 SQL 方言中重现您的模式，那么 DDLDatabase 可能是您的正确选择。 它在内部使用 SQL 解析器，并将您的所有 DDL 增量应用于内存中的 H2 数据库，以便在再次使用普通代码生成对其进行反向工程之前，生成模式的副本。

例如，可以使用以下 database.sql 脚本（本手册中的示例数据库）

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
);

虽然该脚本使用了非常标准的 SQL 构造，但您也可以使用一些特定于供应商的扩展，甚至在两者之间使用 DML 语句来设置您的模式 - 这无关紧要。 您只需按如下方式设置您的代码生成配置

XML（独立和 Maven）
编程方式
Gradle (Kotlin)
Gradle (Groovy)
Gradle（第三方）
<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>

有关更多详细信息，请参见 配置 XSD，独立代码生成和 maven 代码生成。

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

有关更多详细信息，请参见 配置 XSD 和 编程式代码生成。

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"
        }
      }
    }
  }
}

有关更多详细信息，请参见 配置 XSD 和 gradle 代码生成。

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"
        }
      }
    }
  }
}

有关更多详细信息，请参见 配置 XSD 和 gradle 代码生成。

// The jOOQ-codegen-gradle plugin has been introduced in version 3.19.
// Please use the official plugin instead of the third party plugin that was recommended before.

附加属性

附加属性包括

  • logExecutedQueries：是否应记录 DDLDatabase 执行的查询，以进行调试和审计。
  • logExecutionResults：是否应记录 DDLDatabase 执行查询后获得的结果，以进行调试和审计。

忽略不支持的内容

jOOQ 解析器 支持解析可以通过 jOOQ API 表示的所有内容，并忽略一些众所周知的供应商特定语法。 但是 RDBMS 有很多 jOOQ 不了解的功能和语法。 在这种情况下，您可以指定两个注释标记，围绕 jOOQ 应该忽略的 SQL 语法。 这些标记位于普通的单行或多行注释中，因此它们不会以任何其他方式影响您的 DDL 脚本。 例如

-- [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] */

可以使用以下属性覆盖标记，也可以完全关闭该功能

XML（独立和 Maven）
编程方式
Gradle (Kotlin)
Gradle (Groovy)
Gradle（第三方）
<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>

有关更多详细信息，请参见 配置 XSD，独立代码生成和 maven 代码生成。

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

有关更多详细信息，请参见 配置 XSD 和 编程式代码生成。

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]"
        }
      }
    }
  }
}

有关更多详细信息，请参见 配置 XSD 和 gradle 代码生成。

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]"
        }
      }
    }
  }
}

有关更多详细信息，请参见 配置 XSD 和 gradle 代码生成。

// The jOOQ-codegen-gradle plugin has been introduced in version 3.19.
// Please use the official plugin instead of the third party plugin that was recommended before.

Ad-hoc SQL

您可以提供添加到脚本中以初始化 DDLDatabase 的其他 SQL，以防它可以解释它。 这也可以代替提供脚本用于快速代码生成测试用例

XML（独立和 Maven）
编程方式
Gradle (Kotlin)
Gradle (Groovy)
Gradle（第三方）
<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>

有关更多详细信息，请参见 配置 XSD，独立代码生成和 maven 代码生成。

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

有关更多详细信息，请参见 配置 XSD 和 编程式代码生成。

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"
        }
      }
    }
  }
}

有关更多详细信息，请参见 配置 XSD 和 gradle 代码生成。

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"
        }
      }
    }
  }
}

有关更多详细信息，请参见 配置 XSD 和 gradle 代码生成。

// The jOOQ-codegen-gradle plugin has been introduced in version 3.19.
// Please use the official plugin instead of the third party plugin that was recommended before.

依赖

请注意，org.jooq.meta.extensions.ddl.DDLDatabase 类位于外部依赖项中，需要将其放置在 jOOQ 代码生成器的类路径中。 例如，使用 Maven

Maven
Gradle (Kotlin)
Gradle (Groovy)
<dependency>
              <!-- Use org.jooq                for the Open Source Edition
                       org.jooq.pro            for commercial editions with Java 21 support,
                       org.jooq.pro-java-17    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 21 support,
                       org.jooq.trial-java-17  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://jooq.org.cn/download/support-matrix-jdk -->

  <groupId>org.jooq</groupId>
  <artifactId>jooq-meta-extensions</artifactId>
  <version>3.20.5</version>
</dependency>

dependencies {
             // Use org.jooq                for the Open Source Edition
             //     org.jooq.pro            for commercial editions with Java 21 support,
             //     org.jooq.pro-java-17    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 21 support,
             //     org.jooq.trial-java-17  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://jooq.org.cn/download/support-matrix-jdk

    jooqCodegen("org.jooq:jooq-meta-extensions:3.20.5")
}
dependencies {
             // Use org.jooq                for the Open Source Edition
             //     org.jooq.pro            for commercial editions with Java 21 support,
             //     org.jooq.pro-java-17    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 21 support,
             //     org.jooq.trial-java-17  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://jooq.org.cn/download/support-matrix-jdk

    jooqCodegen "org.jooq:jooq-meta-extensions:3.20.5"
}

上一节 : 下一节

引用此页

反馈

您对此页面有任何反馈吗？ 我们很乐意听到！

↑ 返回顶部

社区

支持

法律

文档

The jOOQ Logo