Back to articles list

September 11, 2015 - 4 minutes read

docs

Generate jOOQ Classes With Vertabelo and Gradle

Patrycja Dybka

Java Software Developer

Tags:

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

Generate jOOQ Classes via Vertabelo XML

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.
Download the model as an XML file.

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.

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

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.

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:

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.

Run the following command:
```
gradle build		
```

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.

Tags:

Generate jOOQ Classes via Vertabelo XML

Generate jOOQ Classes via Vertabelo API

What is generated?

You may also like

ORMs Under the Hood

7 Common Database Design Errors

How I Use the Vertabelo API With Gradle