Generate jOOQ Classes With Vertabelo and Gradle

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: September 11, 2015

jOOQ is a Java Object-Relational Mapper that lets you build type safe SQL queries through its fluent API. Using built-in Vertabelo support we can generate sufficient code from the previously designed model. Generated Java classes correspond to database tables, records, etc. This time, we automate this process by using Gradle build system.

There are two ways to generate ready to use Java classes with 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.


    Export your database design as XML file


  3. Create build.gradle script.

    import javax.xml.bind.JAXB
    // Configure the Java plugin and the dependencies
    // ----------------------------------------------
    apply plugin: 'java'
    
    repositories {
        mavenLocal()
        mavenCentral()
    }
    
    dependencies {
        compile 'org.jooq:jooq:3.6.1'
    }
    
    buildscript {
        repositories {
            mavenLocal()
            mavenCentral()
        }
    
        dependencies {
            classpath 'org.jooq:jooq-codegen:3.6.1'
            classpath 'org.jooq:jooq-meta-extensions:3.6.1'
        }
    }
    
    // Use your favourite XML builder to construct the code generation configuration file
    // ----------------------------------------------------------------------------------
    def writer = new StringWriter()
    def xml = new groovy.xml.MarkupBuilder(writer)
            .configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd') {
    
        generator() {
    
            database() {
                name('org.jooq.util.vertabelo.VertabeloXMLDatabase')
                    properties() {
                        property() {
                            key('dialect')
                            value('POSTGRES')
                    }
                        property() {
                            key('xml-file')
                            value('library.xml')
                        }
                    }
                }
            generate() {}
            target() {
                packageName('com.example.postgres.db')
                directory('src/main/java')
            }
        }
    }
    
    
    // Run the code generator
    // ----------------------
    org.jooq.util.GenerationTool.generate(
            JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class)
    )
    

    • Add to dependencies block org.jooq:jooq:3.6.1 as a compile dependency:

      dependencies {
          compile 'org.jooq:jooq:3.6.1'
      }
      

    • Add libraries org.jooq:jooq-codegen:3.6.1 and org.jooq:jooq-meta-extensions:3.6.1 to the buildscript to the script's classpath:

      buildscript {
          repositories {
              mavenLocal()
              mavenCentral()
          }
      
          dependencies {
              classpath 'org.jooq:jooq-codegen:3.6.1'
              classpath 'org.jooq:jooq-meta-extensions:3.6.1'
          }
      }
      

    • Construct the code generation file:

      In database block set org.jooq.util.vertabelo.VertabeloXMLDatabase as the name and add two properties: a dialect and the path for xml file.

      For our purpose it should look as follows:

      database() {
                  name('org.jooq.util.vertabelo.VertabeloXMLDatabase')
                      properties() {
                          property() {
                              key('dialect')
                              value('POSTGRES')
                      }
                          property() {
                              key('xml-file')
                              value('Library.xml')
                          }
                      }
                  }
      

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

       target() {
                  packageName('com.example.postgres.db')
                  directory('src/main/java')
              }
      

      More information about using standalone jOOQ code generator with Gradle here.

  4. Run the following command:

    gradle build
    

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

    The final buil.gradle file and the example model are hosted on Github.

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 build.gradle script.

    import javax.xml.bind.JAXB
    
    
    // Configure the Java plugin and the dependencies
    // ----------------------------------------------
        apply plugin: 'java'
    
        repositories {
            mavenLocal()
            mavenCentral()
        }
    
        dependencies {
            compile 'org.jooq:jooq:3.6.1'
        }
    
        buildscript {
            repositories {
                mavenLocal()
                mavenCentral()
            }
    
            dependencies {
                classpath 'org.jooq:jooq-codegen:3.6.1'
                classpath 'org.jooq:jooq-meta-extensions:3.6.1'
            }
        }
    
    // Use your favourite XML builder to construct the code generation configuration file
    // ----------------------------------------------------------------------------------
        def writer = new StringWriter()
        def xml = new groovy.xml.MarkupBuilder(writer)
                .configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd') {
    
            generator() {
                database() {
                    name('org.jooq.util.vertabelo.VertabeloAPIDatabase')
                    properties() {
                        property() {
                            key('dialect')
                            value('POSTGRES')
                        }
                        property() {
                            key('api-token')
                            value('your-api-token')
                        }
                        property() {
                            key('model-id')
                            value('your-model-identifier')
                        }
                    }
                }
    
                generate() {}
                target() {
                    packageName('com.example.postgres.db')
                    directory('src/main/java')
                }
            }
    
        }
    
    // Run the code generator
    // ----------------------
        org.jooq.util.GenerationTool.generate(
                JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class)
        )
    

    • Add to dependencies block jooq:jooq:3.6.1 as a compile dependency:

      dependencies {
          compile 'org.jooq:jooq:3.6.1'
      }
      

    • Add libraries org.jooq:jooq-codegen:3.6.1 and org.jooq:jooq-meta-extensions:3.6.1 to the buildscript to the script's classpath:

      buildscript {
          repositories {
              mavenLocal()
              mavenCentral()
          }
      
          dependencies {
              classpath 'org.jooq:jooq-codegen:3.6.1'
              classpath 'org.jooq:jooq-meta-extensions:3.6.1'
          }
      }
      

    • Construct the code generation file:

      In a database block set org.jooq.util.vertabelo.VertabeloAPIDatabase as the name and 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

      For our purpose it should look as follows:

       database() {
                      name('org.jooq.util.vertabelo.VertabeloAPIDatabase')
                      properties() {
                          property() {
                              key('dialect')
                              value('POSTGRES')
                          }
                          property() {
                              key('api-token')
                              value('your-api-token')
                          }
                          property() {
                              key('model-id')
                              value('your-model-identifier')
                          }
                      }
                  }
      

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

       target() {
                  packageName('com.example.postgres.db')
                  directory('src/main/java')
              }
      

      More information about using standalone jOOQ code generator with Gradle here.

  3. Run the following command:

    gradle build		
    

  4. The final buil.gradle file and the example model are hosted on Github.


What is generated?

The generated code is located under the path provided in target section in build.gradle.

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 describe 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: