jOOQ and Vertabelo – Getting Started

Patrycja Dybka, Community Manager at Vertabelo

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: August 14, 2015

jOOQ and Vertabelo perfectly complement each other in the application development lifecycle. With Vertabelo you can easily design a database structure that saves you from writing DDL code manually, while jOOQ handles the generation of Java classes corresponding to database tables, and allows to construct SQL queries through its fluent API.

The integration of Vertabelo and jOOQ has been the subject of several articles published on our blog. Unfortunately, all of them, including “The easiest ERD + ORM integration ever: Vertabelo and jOOQ”, are intended for users having some previous experience with jOOQ. That’s why this time we decided to present a step-by-step tutorial for those who need some more explanation about generating jOOQ classes.

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

Generate jOOQ Classes via Vertabelo XML

  1. Design or open your database model in Vertabelo.

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




  2. Download your model as an XML file

    Download database model as an XML file

  3. Download jOOQ from its official site.

    The easiest way is to copy sufficient jar files. They are located in the jOOQ-lib directory.

    jOOQ code generation files

    For our purpose, we need only four files:

    • jooq-3.6.2.jar
    • jooq-codegen-3.6.2.jar
    • jooq-meta-3.6.2.jar
    • jooq-meta-extensions-3.6.2.jar

    Copy them to the folder containing the previously downloaded Vertabelo XML file.

  4. Create code generation configuration file.

    • Create a codegen.xml that looks like this:

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd">
      	<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>library.xml</value>
                        </property>
                    </properties>
              </database>
              <target>
      
              <!-- The destination package of your generated classes (within the destination directory)-->
                   <packageName>library.generated</packageName>
      <!-- The destination directory of your generated classes-->
                   <directory>/path/to/your/dir</directory>
              </target>
        </generator>
      </configuration>
      
      

    • In the property tag, provide a database dialect (in our example it is POSTGRES) and the path for xml file.

    • Provide the name for the destination package of your generated classes.

    • Provide the path to the destination directory of your generated classes.

    More info about jOOQ’s code generator can be found here.

  5. Generate jOOQ classes.

  6. Type the following line on UNIX/Linux/Mac systems:

    java -classpath jooq-3.6.2.jar:jooq-meta-3.6.2.jar:jooq-meta-extensions-3.6.2.jar:jooq-codegen-3.6.2.jar:. org.jooq.util.GenerationTool codegen.xml

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


Generate jOOQ Classes via Vertabelo API

  1. Design or open your database model in Vertabelo.

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




  2. Download your model as an XML file.

    Download database model as an XML file

  3. Download jOOQ from its official site.

    The easiest way is to copy sufficient jar files. They are located in the jOOQ-lib directory.

    jOOQ code generation files

    For our purpose we need only four files:

    • jooq-3.6.2.jar
    • jooq-codegen-3.6.2.jar
    • jooq-meta-3.6.2.jar
    • jooq-meta-extensions-3.6.2.jar

    Copy them to the folder containing the previously downloaded Vertabelo XML file.

  4. Create code generation configuration file.

    • Create a codegen.xml that looks like this:

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd">
      	<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>
              <!-- The destination package of your generated classes (within the destination directory)-->
                   <packageName>library.generated</packageName>
      <!-- The destination directory of your generated classes-->
                   <directory>/path/to/your/dir</directory>
              </target>
          </generator>
      </configuration>
      

    • In the property tag, provide a database dialect (in our example it is POSTGRES).

    • Provide Vertabelo API token (read the tutorial to learn how to get it).

    • Provide model identifier – you can find it in the Model details panel:

      Vertabelo model identifier

    • Provide the name for the destination package of your generated classes.

    • Provide the path to the destination directory of your generated classes.

    More info about jOOQ’s code generator can be found here.


What Is Generated?

The generated code is located under the path provided in the codegen file.

The output looks like the following:

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: