Generate jOOQ Classes With Vertabelo and Maven

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
• Generate jOOQ classes via Vertabelo API

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

   
   

   

   
   

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

   
   
       4.0.0
   
       com.example
       vertabeloXML
       1.0
       
           UTF-8
           3.6.2
           1.8
           1.8
       
   
       
           
               org.jooq
               jooq
               ${org.jooq.version}
           
       
       
           
               
                   org.jooq
                   jooq-codegen-maven
                   ${org.jooq.version}
   
                   
                       
                           org.jooq
                           jooq-meta-extensions
                           ${org.jooq.version}
                       
                   
   
                   
                       
                           generate-postgres
                           generate-sources
                           
                               generate
                           
                           
                               
                                   org.jooq.util.DefaultGenerator
                                   
                                       org.jooq.util.vertabelo.VertabeloXMLDatabase
                                       
                                           
                                               dialect
                                               POSTGRES
                                           
                                           
                                               xml-file
                                               src/main/resources/Library.xml
                                           
                                       
                                   
                                   
                                       com.example.db
                                       target/generated-sources/jooq-postgres
                                   
                               
                           
                       
                   
               
           
       
   
   

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:

   
   
       4.0.0
   
       com.example
       vertabeloAPI
       1.0
       
           UTF-8
           3.6.2
           1.8
           1.8
       
   
       
           
               org.jooq
               jooq
               ${org.jooq.version}
           
       
   
       
           
               
                   org.jooq
                   jooq-codegen-maven
                   ${org.jooq.version}
   
                   
                       
                           org.jooq
                           jooq-meta-extensions
                           ${org.jooq.version}
                       
                   
   
                   
                       
                           generate-postgres
                           generate-sources
                           
                               generate
                           
                           
                               
                                   org.jooq.util.DefaultGenerator
                                   
                                       org.jooq.util.vertabelo.VertabeloAPIDatabase
                                       
                                           
                                               dialect
                                               POSTGRES
                                           
                                           
                                               api-token
                                               your-api-token
                                           
                                           
                                               model-id
                                               your-model-identifier
                                           
                                       
                                   
                                   
                                       com.example.db
                                       target/generated-sources/jooq-postgres
                                   
                               
                           
                       
                   
               
           
       
   
   

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:

       

   • 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.

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.