When you need to derive meta-models from existing databases and want to create type-safe queries with an elegant, fluent-API, jOOQ definitely is a tool to consider here.
In the following tutorial I’d like to demonstrate how to integrate the jOOQ meta-model generator into a Maven build using the jOOQ Maven Plug-in, the Build Helper Maven Plug-in and Maven profiles to finally create a running application to query an existing RDBMS using such a generated meta-model.
Prerequisites
To compile and run the following examples, we need…
-
MySQL (or any other RDBMS)
Database Setup
We need a database filled with some basic input so this is our database structure.
One table for our books, another table for authors and a cross-table to join them.
CREATE TABLE book (
id BIGINT PRIMARY KEY,
title VARCHAR(255)
);
CREATE TABLE author(
id BIGINT PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE book_author_rel (
bookid BIGINT NOT NULL,
authorid BIGINT NOT NULL,
FOREIGN KEY fk_book(bookid)
REFERENCES book(id),
FOREIGN KEY fk_author(authorid)
REFERENCES author(id)
);Additionally we need some data to query for ..
INSERT INTO book VALUES
(1, 'Some book'),
(2, 'Another Book'),
(3, 'Some completely other book')
;
INSERT INTO author VALUES
(1, 'tim'),
(2, 'selena'),
(3, 'thelma')
;
INSERT INTO book_author_rel VALUES
(1,1),
(2,2),
(3,2)
;Maven Dependencies
Just two plug-ins and one dependency needed: the jOOQ Maven Plug-in to generate our meta-model from an existing database, the Build Helper Plug-in for Maven to add the generated-sources-directory as a source directory to the Maven build environment and the dependency for the JDBC connector – in this case: MySQL.
jOOQ Maven Plugin
Our configuration for the jOOQ plug-in looks like this one.
We’re binding its execution to a specific life-cycle phase, we’re adding the mysql-connector as a plug-in dependency and we configure how to connect to the database and some details for the code generator.
More detailed information about possible configuration details can be found on the jOOQ documentation.
In the last element, we’re advising the generator to store the generated classes in src/generated-sources/jooq. This is important because that is the location that we want the build helper plug-in to look for in the next section.
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.3.2</version>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
</dependencies>
<configuration>
<jdbc>
<driver>${jdbc.driver}</driver>
<url>${jdbc.url}</url>
<user>${jdbc.user}</user>
<password>${jdbc.password}</password>
</jdbc>
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>${jdbc.database.name}</inputSchema>
</database>
<generate>
<deprecated>false</deprecated>
</generate>
<target>
<packageName>com.hascode.model</packageName>
<directory>src/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>Build Helper Maven Plugin
This plug-in is like a swiss army-knife and if you’re interested in its other features, please feel free to have a look at the plugin’s project website.
In our case, we simply want to add the directory with generated sources as a source directory to our Maven build.
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<version>1.8</version>
<executions>
<execution>
<id>add-source</id>
<phase>generate-sources</phase>
<goals>
<goal>add-source</goal>
</goals>
<configuration>
<sources>
<source>src/generated-sources/jooq</source>
</sources>
</configuration>
</execution>
</executions>
</plugin>Using Profiles
Using Maven profiles allows us to separate information that we would not want to save it directly in the pom.xml but pass it to the build by specifying profiles e.g. in your settings.xml or passing the parameters to Maven as a parameter like -Dkey=value.
The following sample profile provides basic information for the database connection and schema used and is activated by default:
<profiles>
<profile>
<id>sample-profile</id>
<activation>
<activeByDefault>true</activeByDefault>
</activation>
<properties>
<jdbc.user>user</jdbc.user>
<jdbc.password>password</jdbc.password>
<jdbc.url>jdbc:mysql://localhost:3306/mydb</jdbc.url>
<jdbc.database.name>mydb</jdbc.database.name>
</properties>
</profile>
</profiles>Generating the Metamodel and adding its Source Directory
We’re now ready to generate our meta-model (assuming the database is running with the schema described above) like this.
$ mvn compile
[INFO] ------------------------------------------------------------------------
[INFO] Building jooq-maven-tutorial 1.0.0
[INFO] ------------------------------------------------------------------------
[INFO] --- jooq-codegen-maven:3.3.2:generate (default) @ jooq-maven-tutorial ---
[INFO] Using this configuration:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.3.0.xsd">
<jdbc>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/mydb</url>
<user>user</user>
<password>password</password>
</jdbc>
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<includeExcludeColumns>false</includeExcludeColumns>
<recordVersionFields></recordVersionFields>
<recordTimestampFields></recordTimestampFields>
<dateAsTimestamp>false</dateAsTimestamp>
<unsignedTypes>true</unsignedTypes>
<inputSchema>mydb</inputSchema>
</database>
<generate>
<relations>true</relations>
<deprecated>false</deprecated>
<instanceFields>true</instanceFields>
<generatedAnnotation>true</generatedAnnotation>
<records>true</records>
<pojos>false</pojos>
<immutablePojos>false</immutablePojos>
<interfaces>false</interfaces>
<daos>false</daos>
<jpaAnnotations>false</jpaAnnotations>
<validationAnnotations>false</validationAnnotations>
<globalObjectReferences>true</globalObjectReferences>
<fluentSetters>false</fluentSetters>
</generate>
<target>
<packageName>com.hascode.model</packageName>
<directory>/data/project/jooq-maven-tutorial/src/generated-sources/jooq</directory>
</target>
</generator>
</configuration>[INFO] License parameters
[INFO] ----------------------------------------------------------
[INFO] Thank you for using jOOQ and jOOQ's code generator
[INFO]
[INFO] Database parameters
[INFO] ----------------------------------------------------------
[INFO] dialect : MYSQL
[INFO] URL : jdbc:mysql://localhost:3306/mydb
[INFO] target dir : /data/project/jooq-maven-tutorial/src/generated-sources/jooq
[INFO] target package : com.hascode.model
[INFO] includes : [.*]
[INFO] excludes : []
[INFO] includeExcludeColumns : false
[INFO] ----------------------------------------------------------
[INFO]
[INFO] DefaultGenerator parameters
[INFO] ----------------------------------------------------------
[INFO] strategy : class org.jooq.util.DefaultGeneratorStrategy
[INFO] deprecated : false
[INFO] generated annotation : true
[INFO] JPA annotations : false
[INFO] validation annotations : false
[INFO] instance fields : true
[INFO] records : true
[INFO] pojos : false
[INFO] immutable pojos : false
[INFO] interfaces : false
[INFO] daos : false
[INFO] relations : true
[INFO] global references : true
[INFO] ----------------------------------------------------------
[INFO]
[INFO] Generation remarks
[INFO] ----------------------------------------------------------
[INFO] none
[INFO]
[INFO] ----------------------------------------------------------
[INFO] Emptying : /data/project/jooq-maven-tutorial/src/generated-sources/jooq/com/hascode/model
[INFO] Generating schemata : Total: 1
[INFO] Generating schema : Mydb.java
[INFO] ----------------------------------------------------------
[INFO] Sequences fetched : 0 (0 included, 0 excluded)
[INFO] Tables fetched : 3 (3 included, 0 excluded)
[INFO] UDTs fetched : 0 (0 included, 0 excluded)
[INFO] Generating tables
[INFO] Adding foreign key : book_author_rel_ibfk_1 (mydb.book_author_rel.bookid) referencing KEY_book_PRIMARY
[INFO] Adding foreign key : book_author_rel_ibfk_2 (mydb.book_author_rel.authorid) referencing KEY_author_PRIMARY
[INFO] Generating table : Author.java [input=author, output=author, pk=KEY_author_PRIMARY]
[INFO] ARRAYs fetched : 0 (0 included, 0 excluded)
[INFO] Enums fetched : 0 (0 included, 0 excluded)
[INFO] Generating table : Book.java [input=book, output=book, pk=KEY_book_PRIMARY]
[INFO] Generating table : BookAuthorRel.java [input=book_author_rel, output=book_author_rel, pk=N/A]
[INFO] Tables generated : Total: 253.201ms
[INFO] Generating table references
[INFO] Table refs generated : Total: 254.091ms, +0.889ms
[INFO] Generating Keys
[INFO] Keys generated : Total: 257.175ms, +3.084ms
[INFO] Generating records
[INFO] Generating record : AuthorRecord.java
[INFO] Generating record : BookRecord.java
[INFO] Generating record : BookAuthorRelRecord.java
[INFO] Table records generated : Total: 267.545ms, +10.369ms
[WARNING] Table unavailable : The `mysql`.`proc` table is unavailable. Stored procedures cannot be loaded. Check if you have sufficient grants
[INFO] Routines fetched : 0 (0 included, 0 excluded)
[INFO] Packages fetched : 0 (0 included, 0 excluded)
[INFO] GENERATION FINISHED! : Total: 288.101ms, +20.555ms
[INFO]
[INFO] --- build-helper-maven-plugin:1.8:add-source (add-source) @ jooq-maven-tutorial ---
[INFO] Source directory: /data/project/jooq-maven-tutorial/src/generated-sources/jooq added.
[..]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------Now we should be able to see a bunch of generated files in src/generated-sources/jooq and you should see this directory configured as a source directory with the help of the Builder Helper Maven Plug-in.
This is the result in my Eclipse IDE:
m2e Maven for Eclipse Integration
The m2e Eclipse Plug-in traditionally might display a warning that the jOOQ and the build-helper-plugin are not supported.
At least for the second one we’re able to download an addition from Sonatype via the Eclipse Marketplace (using the Quick Fix Feature).
Querying the Database with jOOQ
Having generated the meta-model, we’re now ready to write an application to query our database in no time.
The following example fetches all books for the author named ‘selena’ from the database and prints the result to the screen.
package com.hascode.tutorial;
import static com.hascode.model.Tables.AUTHOR;
import static com.hascode.model.Tables.BOOK;
import static com.hascode.model.Tables.BOOK_AUTHOR_REL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.jooq.DSLContext;
import org.jooq.Record2;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
public class BookStore {
public void run() throws SQLException {
Connection conn = getConnection();
DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
// query books for author named 'selena'
Result<Record2<Long, String>> result = create
.select(BOOK.ID, BOOK.TITLE).from(BOOK).join(BOOK_AUTHOR_REL)
.on(BOOK_AUTHOR_REL.BOOKID.equal(BOOK.ID)).join(AUTHOR)
.on(BOOK_AUTHOR_REL.AUTHORID.equal(AUTHOR.ID))
.where(AUTHOR.NAME.equal("selena"))
.orderBy(BOOK.TITLE.asc(), BOOK.ID.asc()).fetch();
result.forEach((r) -> {
System.out.println(String.format("%s (id: %s)",
r.getValue(BOOK.TITLE), r.getValue(BOOK.ID)));
});
conn.close();
System.exit(0); // it's a tutorial :)
}
public static void main(final String[] args) throws SQLException {
new BookStore().run();
}
private Connection getConnection() {
try {
Class.forName(System.getProperty("jdbc.driver")).newInstance();
return DriverManager.getConnection(System.getProperty("jdbc.url"),
System.getProperty("jdbc.user"),
System.getProperty("jdbc.password"));
} catch (InstantiationException | IllegalAccessException
| ClassNotFoundException | SQLException e) {
e.printStackTrace(); // for tutorial's sake ;)
}
return null;
}
}Now we’re ready to query the database by running the following command:
$ mvn exec:java -Dexec.mainClass=com.hascode.tutorial.BookStore
[INFO] Scanning for projects...
[..]
[INFO] ------------------------------------------------------------------------
[INFO] Building jooq-maven-tutorial 1.0.0
[INFO] ------------------------------------------------------------------------
[INFO] --- exec-maven-plugin:1.3.1:java (default-cli) @ jooq-maven-tutorial ---
Another Book (id: 2)
Some completely other book (id: 3)Tutorial Sources
Please feel free to download the tutorial sources from my GitHub repository, fork it there or clone it using Git:
git clone https://bitbucket.org/hascode/jooq-maven-tutorial.git