
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
-
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
blockorg.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
andorg.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 setorg.jooq.util.vertabelo.VertabeloXMLDatabase
as thename
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
blockjooq: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
andorg.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 setorg.jooq.util.vertabelo.VertabeloAPIDatabase
as thename
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') } } }
- database dialect – for our purpose is
-
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
.
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.