jOOQ 代码生成工具的使用说明

1、命令行工具生成代码

1.1 下载 jar 包

代码生成是开源免费的，需要4个 jar 包，从 Maven Central 下载：

• jooq-3.10.8.jar ： 核心包，需要在项目中引用；
• jooq-meta-3.10.8.jar ： 模型解析包；
• jooq-codegen-3.10.8.jar ：生成数据库模型对应代码的工具包
• postgresql-42.2.5.jar： 所需的 JDBC Driver

1.2 配置数据源

首先，在目标数据库中创建所需的数据表，例如：

CREATE TABLE `author` (
 `id` int NOT NULL,
 `first_name` varchar(255) DEFAULT NULL,
 `last_name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

这里以 PostgreSQL 为例，配置文件（jooq-config.xml）如下：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.10.0.xsd">
  <!-- Configure the database connection here -->
  <jdbc>
    <driver>org.postgresql.Driver</driver>
    <url>jdbc:postgresql://localhost:5432/mydb</url>
    <user>admin</user>
    <password>123456</password>
  </jdbc>

  <generator>
    <database>
      <!-- The database dialect from jooq-meta. Available dialects are
           named org.jooq.util.[database].[database]Database.
            
           Natively supported values are:
    
               org.jooq.util.ase.ASEDatabase
               org.jooq.util.cubrid.CUBRIDDatabase
               org.jooq.util.db2.DB2Database
               org.jooq.util.derby.DerbyDatabase
               org.jooq.util.firebird.FirebirdDatabase
               org.jooq.util.h2.H2Database
               org.jooq.util.hana.HANADatabase
               org.jooq.util.hsqldb.HSQLDBDatabase
               org.jooq.util.informix.InformixDatabase
               org.jooq.util.ingres.IngresDatabase
               org.jooq.util.mariadb.MariaDBDatabase
               org.jooq.util.mysql.MySQLDatabase
               org.jooq.util.oracle.OracleDatabase
               org.jooq.util.postgres.PostgresDatabase
               org.jooq.util.redshift.RedshiftDatabase
               org.jooq.util.sqlite.SQLiteDatabase
               org.jooq.util.sqlserver.SQLServerDatabase
               org.jooq.util.sybase.SybaseDatabase
            
           This value can be used to reverse-engineer generic JDBC DatabaseMetaData (e.g. for MS Access)
           
               org.jooq.util.jdbc.JDBCDatabase
                
           This value can be used to reverse-engineer standard jOOQ-meta XML formats
            
               org.jooq.util.xml.XMLDatabase

           This value can be used to reverse-engineer schemas defined by SQL files (requires jooq-meta-extensions dependency)

               org.jooq.util.ddl.DDLDatabase
                   
           This value can be used to reverse-engineer schemas defined by JPA annotated entities (requires jooq-meta-extensions dependency)
                   
               org.jooq.util.jpa.JPADatabase
               
           You can also provide your own org.jooq.util.Database implementation
           here, if your database is currently not supported -->
      <name>org.jooq.util.postgres.PostgresDatabase</name>

      <!-- All elements that are generated from your schema (A Java regular expression.
           Use the pipe to separate several expressions) Watch out for
           case-sensitivity. Depending on your database, this might be
           important!
           
           You can create case-insensitive regular expressions using this syntax: (?i:expr)
           
           Whitespace is ignored and comments are possible.
           -->
      <includes>.*</includes>

      <!-- All elements that are excluded from your schema (A Java regular expression.
           Use the pipe to separate several expressions). Excludes match before
           includes, i.e. excludes have a higher priority -->
      <excludes>
           UNUSED_TABLE                # This table (unqualified name) should not be generated
         | PREFIX_.*                   # Objects with a given prefix should not be generated
         | SECRET_SCHEMA\.SECRET_TABLE # This table (qualified name) should not be generated
         | SECRET_ROUTINE              # This routine (unqualified name) ...
      </excludes>

      <!-- The schema that is used locally as a source for meta information.
           This could be your development schema or the production schema, etc
           This cannot be combined with the schemata element.

           If left empty, jOOQ will generate all available schemata. See the
           manual's next section to learn how to generate several schemata -->
      <inputSchema>public</inputSchema>
    </database>

    <generate>
      <!-- Generation flags: See advanced configuration properties -->
    </generate>

    <target>
      <!-- The destination package of your generated classes (within the
           destination directory)
           
           jOOQ may append the schema name to this package if generating multiple schemas,
           e.g. org.jooq.your.packagename.schema1
                org.jooq.your.packagename.schema2 -->
      <packageName>com.gnetna.db</packageName>

      <!-- The destination directory of your generated classes -->
      <directory>./</directory>
    </target>
  </generator>
</configuration>

• inputSchema： 当根据数据库中的表来生成代码时，配置目标 schema，生成该 schema 下的所有表对应的代码，如果不填 public 将会生成很多垃圾代码，需要注意；

1、有几种 code genarator？

• org.jooq.codegen.JavaGenarator
• org.jooq.codegen.ScalaGenarator

可以创建自己的 genarator，定制代码风格。

2、genarator 的配置

• jdbc：配置 driver，url，user，password
• genarator：name, database, inputSchema

1.3 执行命令

将所需的 4 个 jar 包放置在特定路径下，执行命令：

java -cp jooq-3.10.8.jar;jooq-meta-3.10.8.jar;jooq-codegen-3.10.8.jar;postgresql-42.2.5.jar;. org.jooq.util.GenerationTool /jooq-config.xml

jOOQ 会从 classpath 中加载 jooq-config.xml 文件，如果没找到，就当前目录中查找。

1.4 数据访问

public static void main(String[] args) {
	 String userName = "admin";
	 String password = "123456";
	 String url = "jdbc:postgresql://localhost:5432/mydb";
	
	 try (Connection conn = DriverManager.getConnection(url, userName, password)) {
		 DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
		 Result<Record> result = create.select().from(AUTHOR).fetch();
		 for (Record r : result) {
			 Integer id = r.getValue(AUTHOR.ID);
			 String firstName = r.getValue(AUTHOR.FIRST_NAME);
			 String lastName = r.getValue(AUTHOR.LAST_NAME);
			 System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
		 }
	 } catch (Exception e) {
	 	e.printStackTrace();
	 }
 }

2、Maven 插件

使用 jOOQ-codegen-maven 插件生成代码：

<plugin>

  <!-- Specify the maven code generator plugin -->
  <!-- Use org.jooq            for the Open Source Edition
           org.jooq.pro        for commercial editions, 
           org.jooq.pro-java-6 for commercial editions with Java 6 support,
           org.jooq.trial      for the free trial edition 
         
       Note: Only the Open Source Edition is hosted on Maven Central. 
             Import the others manually from your distribution -->
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>3.11.10</version>

  <!-- The plugin should hook into the generate goal -->
  <executions>
    <execution>
      <goals>
        <goal>generate</goal>
      </goals>
    </execution>
  </executions>

  <!-- Manage the plugin's dependency. In this example, we'll use a PostgreSQL database -->
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.4.1212</version>
    </dependency>
  </dependencies>

  <!-- Specify the plugin configuration.
       The configuration format is the same as for the standalone code generator -->
  <configuration>

    <!-- JDBC connection parameters -->
    <jdbc>
      <driver>org.postgresql.Driver</driver>
      <url>jdbc:postgresql:postgres</url>
      <user>postgres</user>
      <password>test</password>
    </jdbc>

    <!-- Generator parameters -->
    <generator>
      <database>
        <name>org.jooq.meta.postgres.PostgresDatabase</name>
        <includes>.*</includes>
        <excludes></excludes>
        <!-- In case your database supports catalogs, e.g. SQL Server:
        <inputCatalog>public</inputCatalog>
          -->
        <inputSchema>public</inputSchema>
      </database>
      <target>
        <packageName>org.jooq.codegen.maven.example</packageName>
        <directory>target/generated-sources/jooq</directory>
      </target>
    </generator>
  </configuration>
</plugin>

Be sure, both jooq-3.11.10.jar and your generated package (see configuration) are located on your classpath. Once this is done, you can execute SQL statements with your generated classes.

3、DDL

http://www.jooq.org/doc/3.10/manual/code-generation/codegen-ddl/

多数情况，schema 都是通过 sql 脚本的形式定义，这种形式能方便 Flyway 等迁移工具的使用。如果项目中的 schema 完整的定义在 sql 文件中，那么 org.jooq.util.ddl.DDLDatabase 可能是更好的选择。

例如，schema.sql :

CREATE TABLE author (
  id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INT,
  address VARCHAR(50),

  CONSTRAINT pk_t_author PRIMARY KEY (ID)
);

sql 文件中可以使用标准的 SQL，也可以使用数据库提供商扩展的 SQL。

配置 jooq-config.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.10.0.xsd">
  <generator>
    <database>
      <name>org.jooq.util.ddl.DDLDatabase</name>
      <properties>
            <property>
              <key>scripts</key>
              <value>F:/jooq/schema.sql</value>
            </property>
            <property>
              <key>sort</key>
              <value>semantic</value>
            </property>
      </properties>
    </database>

    <target>
      <packageName>com.gnetna</packageName>
      <directory>./</directory>
    </target>
  </generator>
</configuration>

备注：新版本中 DDLDatabase 的全路径为 org.jooq.meta.extensions.ddl.DDLDatabase

DDLDatabase 依赖 jooq-meta-extensions 模块，该模块属于非开源版本，在 Maven 上没有，需要自己构建。

具体操作步骤：

1、将所需的所有 jar 放在特定目录下，比如 F:\jooq

• h2-1.4.199.jar
• jooq-3.10.7.jar
• jooq-codegen-3.10.7.jar
• jooq-meta-3.10.7.jar
• jooq-meta-extensions-3.10.7.jar
• jooq-config.xml
• schema.sql

2、执行命令：

java -cp jooq-3.10.7.jar;jooq-meta-3.10.7.jar;jooq-codegen-3.10.7.jar;jooq-meta-extensions-3.10.7.jar;h2-1.4.199.jar;. org.jooq.util.GenerationTool /config.xml

3、查看生成的 代码

示例

jooq-config.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.10.0.xsd">
<!-- Configure the database connection here -->
<jdbc>
    <driver>org.postgresql.Driver</driver>
    <url>jdbc:postgresql://gnetna.com:5432/chorusdb</url>
    <user>chorus</user>
    <password>xxxxxx</password>
</jdbc>

<generator>
    <database>
        <name>org.jooq.util.postgres.PostgresDatabase</name>

        <includeTables>true</includeTables>
        <includeRoutines>false</includeRoutines>
        <includePackages>false</includePackages>
        <includePackageRoutines>false</includePackageRoutines>
        <includePackageUDTs>false</includePackageUDTs>
        <includePackageConstants>false</includePackageConstants>
        <includeUDTs>false</includeUDTs>
        <includeSequences>false</includeSequences>
        <includePrimaryKeys>false</includePrimaryKeys>
        <includeUniqueKeys>false</includeUniqueKeys>
        <includeForeignKeys>false</includeForeignKeys>
        <includeIndexes>false</includeIndexes>
        <excludes>
            UNUSED_TABLE                # This table (unqualified name) should not be generated
            | PREFIX_.*                   # Objects with a given prefix should not be generated
            | SECRET_SCHEMA\.SECRET_TABLE # This table (qualified name) should not be generated
            | SECRET_ROUTINE              # This routine (unqualified name) ...
        </excludes>
        <inputSchema>public</inputSchema>
    </database>

    <generate>
        <daos>true</daos>
        <!--<jpaAnnotations>true</jpaAnnotations>-->
        <!--<jpaVersion>2.2</jpaVersion>-->
        <!--<validationAnnotations>true</validationAnnotations>-->
        <!--<springAnnotations>true</springAnnotations>-->
    </generate>

    <target>
        <packageName>com.platform.chorus</packageName>
        <directory>./</directory>
    </target>
</generator>
</configuration>

填写正确的数据库信息！

执行命令：

java -cp jooq-3.10.7.jar;jooq-meta-3.10.7.jar;jooq-codegen-3.10.7.jar;jooq-meta-extensions-3.10.7.jar;postgresql-42.2.5.jar;. org.jooq.util.GenerationTool /jooq-config.xml