Generate jOOQ Classes With Vertabelo and Maven

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: September 2, 2015

jOOQ provides an easy way to automate the process of generating the java classes that represent database tables, records, etc. via the jooq-codegen-maven plugin. This time we let Maven worry about downloading the required JAR files and generating Java code that lets you build typesafe SQL queries through jOOQ’s fluent API.

There are two ways to generate ready to use Java classes with Maven and Vertabelo:

Generate jOOQ Classes via Vertabelo XML

  1. Design your database model online in Vertabelo.

    Below is my previously prepared sample database model for PostgreSQL. Note that in Vertabelo you can also design databases for MySQL, Oracle, IBM DB2, Microsoft SQL Server, SQLite, and HSQLDB.




  2. Download the model as an XML file


    Generating xml file with your database model


  3. Create a pom.xml that looks like the following:

    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.example</groupId>
        <artifactId>vertabeloXML</artifactId>
        <version>1.0</version>
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <org.jooq.version>3.6.2</org.jooq.version>
            <maven.compiler.source>1.8</maven.compiler.source>
            <maven.compiler.target>1.8</maven.compiler.target>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.jooq</groupId>
                <artifactId>jooq</artifactId>
                <version>${org.jooq.version}</version>
            </dependency>
        </dependencies>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.jooq</groupId>
                    <artifactId>jooq-codegen-maven</artifactId>
                    <version>${org.jooq.version}</version>
    
                    <dependencies>
                        <dependency>
                            <groupId>org.jooq</groupId>
                            <artifactId>jooq-meta-extensions</artifactId>
                            <version>${org.jooq.version}</version>
                        </dependency>
                    </dependencies>
    
                    <executions>
                        <execution>
                            <id>generate-postgres</id>
                            <phase>generate-sources</phase>
                            <goals>
                                <goal>generate</goal>
                            </goals>
                            <configuration>
                                <generator>
                                    <name>org.jooq.util.DefaultGenerator</name>
                                    <database>
                                        <name>org.jooq.util.vertabelo.VertabeloXMLDatabase</name>
                                        <properties>
                                            <property>
                                                <key>dialect</key>
                                                <value>POSTGRES</value>
                                            </property>
                                            <property>
                                                <key>xml-file</key>
                                                <value>src/main/resources/Library.xml</value>
                                            </property>
                                        </properties>
                                    </database>
                                    <target>
                                        <packageName>com.example.db</packageName>
                                        <directory>target/generated-sources/jooq-postgres</directory>
                                    </target>
                                </generator>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>
    </project>
    

  4. Add jooq-codegen-maven as a plugin to plugins section.

  5. Add a jooq-meta-extensions as a dependency for this plugin.

  6. Create an execution which runs the generate goal of the jOOQ-codegen-maven plugin during the generate-sources Maven lifecycle phase.

    Configure the plugin:

    In database section:

    • Add org.jooq.util.vertabelo.VertabeloXMLDatabase as a name (this class will be used to parse the downloaded XML file)

    • Provide two properties: a database dialect (for our purpose is POSTGRES) and the path for xml file.

    • In the target section provide the destination package and the destination directory for the generated code

  7. Run the following Maven command

    mvn clean install


That’s all! Now, if you would like to know exactly what was generated, click here.


Generate jOOQ Classes via Vertabelo API

  1. Design your database model online in Vertabelo.

    Below is my previously prepared sample database model for PostgreSQL. Note that in Vertabelo you can also design databases for MySQL, Oracle, IBM DB2, Microsoft SQL Server, SQLite, and HSQLDB.




  2. Create a pom.xml that looks like the following:

    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.example</groupId>
        <artifactId>vertabeloAPI</artifactId>
        <version>1.0</version>
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <org.jooq.version>3.6.2</org.jooq.version>
            <maven.compiler.source>1.8</maven.compiler.source>
            <maven.compiler.target>1.8</maven.compiler.target>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.jooq</groupId>
                <artifactId>jooq</artifactId>
                <version>${org.jooq.version}</version>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.jooq</groupId>
                    <artifactId>jooq-codegen-maven</artifactId>
                    <version>${org.jooq.version}</version>
    
                    <dependencies>
                        <dependency>
                            <groupId>org.jooq</groupId>
                            <artifactId>jooq-meta-extensions</artifactId>
                            <version>${org.jooq.version}</version>
                        </dependency>
                    </dependencies>
    
                    <executions>
                        <execution>
                            <id>generate-postgres</id>
                            <phase>generate-sources</phase>
                            <goals>
                                <goal>generate</goal>
                            </goals>
                            <configuration>
                                <generator>
                                    <name>org.jooq.util.DefaultGenerator</name>
                                    <database>
                                        <name>org.jooq.util.vertabelo.VertabeloAPIDatabase</name>
                                        <properties>
                                            <property>
                                                <key>dialect</key>
                                                <value>POSTGRES</value>
                                            </property>
                                            <property>
                                                <key>api-token</key>
                                                <value>your-api-token</value>
                                            </property>
                                            <property>
                                                <key>model-id</key>
                                                <value>your-model-identifier</value>
                                            </property>
                                        </properties>
                                    </database>
                                    <target>
                                        <packageName>com.example.db</packageName>
                                        <directory>target/generated-sources/jooq-postgres</directory>
                                    </target>
                                </generator>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>
    </project>
    

  3. Add jooq-codegen-maven as a plugin to plugins section.

  4. Add a jooq-meta-extensions as a dependency for this plugin.

  5. Create an execution which runs the generate goal of the jOOQ-codegen-maven plugin during the generate-sources Maven lifecycle phase.

    Configure the plugin:

    In the database section:

    • Add org.jooq.util.vertabelo.VertabeloXMLDatabase as a name (this class will be used to parse the downloaded XML file).

    • provide three properties:

      • database dialect (for our purpose is POSTGRES)

      • Vertabelo API token (the following tutorial explains how to get the token)

      • model identifier – it can be found in the “Model details” panel:

        Vertabelo database model identifier

    • In the target section provide the destination package and destination directory for the generated code.

  6. Run the following Maven command:

    mvn clean install


What is generated?

The generated code is located under the path provided in target section in pom.xml.

Generated jooq classes

The class Keys.java contains UNIQUE, PRIMARY KEY and FOREIGN KEY definitions.

Classes Author.java and Book.java are table classes that describes the structure of the single database table.

Classes AuthorRecord.java and BookRecord.java are record classes. They contain information on a single table row.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Find out how to identify all the factors of a successful visualization: think like your audience, find the right chart type, and take out the meaning of the data. View course Discover our other courses: