As we might remember from the first part of this tutorial, jOOQ states that
jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API.
The first part of this tutorial describes how we can configure the application context of a Spring powered application which uses jOOQ but it doesn’t describe how we can create typesafe SQL queries with jOOQ.
This blog post takes us one step closer to the solution. If we want to build typesafe database queries with jOOQ, we have to reverse-engineer our database and create classes which represents different database tables, records, and so on. These classes are the building blocks of typesafe SQL queries.
Luckily jOOQ provides an easy way to automate this process. This blog post describes how we can generate the required classes with Maven.
Let’s get started.
Additional reading:
- Creating Profile Specific Configuration Files with Maven explains how you can create different configurations for different environment by using Maven build profiles. The example application of this blog post is configured by using the approach described in this blog post.
- Using jOOQ with Spring: Configuration is the first part of this tutorial and it describes you can configure the application context of a Spring application which uses jOOQ. You understand this blog post without reading the first part of this tutorial, but if you want to really use jOOQ in a Spring powered application, I recommend that you read that blog post as well.
Generating Code with Maven
Our build process is divided into three important phases which are described in the following:
- Read the database configuration from the profile specific configuration file. We want to use the same configuration file for our application and our build script because this helps us to avoid duplication. We need the database connection details when we update our database schema and generate code from our database.
- Update the database schema if needed. Because we want to generate code from our database, we have to ensure that its schema is updated before the code generation is started.
- Generate code from the database. This phase reads the metadata from the configured database and creates the classes which are used to write typesafe database queries with jOOQ.
Let’s move on and find out how we can configure these phases in our pom.xml file.
Reading the Properties From the Profile Specific Properties File
We can read the properties from the profile specific properties file by using the Properties Maven plugin. This plugin reads the contents of a properties file and ensures that we can use its properties in our pom.xml file.
We can configure this plugin by following these steps:
- Add the plugin declaration to the plugins section of the pom.xml file.
- Create an execution which runs the read-project-properties goal in the initialize Maven lifecycle phase.
- Ensure that the properties are read from the profile specific configuration file (profiles/${build.profile.id}/config.properties).
The configuration of the Properties Maven plugin looks as follows:
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>properties-maven-plugin</artifactId>
<version>1.0-alpha-2</version>
<executions>
<execution>
<phase>initialize</phase>
<goals>
<goal>read-project-properties</goal>
</goals>
<configuration>
<files>
<file>profiles/${build.profile.id}/config.properties</file>
</files>
</configuration>
</execution>
</executions>
</plugin>Let’s move on and find out how we can update the database schema of our application.
Updating the Database Schema
Before we can generate any code from a database, we have to ensure that the schema of our database is up-to-date. The easiest way to do this is to use the SQL Maven plugin which can execute SQL statements found from a SQL file.
In a real life application you probably want to use either Flyway or Liquibase for this purpose.
Let’s find out how we can ensure that the database of our example is always up-to-date.
First, we have to create the SQL file which creates the database schema. This SQL script will create the todos table if it isn’t found from the database.
The schema.sql file looks as follows:
CREATE TABLE IF NOT EXISTS todos ( id BIGINT AUTO_INCREMENT PRIMARY KEY, creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, description VARCHAR(500), modification_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, title VARCHAR(100) );
Second, we have to add a skip.db.creation property to the properties section of the pom.xml file. This property is used to enable and disable the schema update. Because we want to enable the database schema update in all profiles, we have to set the value of this property to false.
The relevant part of our POM file looks as follows:
<properties>
<skip.db.creation>false</skip.db.creation>
</properties>Third, we have to configure the SQL Maven plugin. We can do this by following these steps:
- Add the plugin declaration to the plugins section of the pom.xml file.
- Ensure that the schema generation is skipped if the value of skip.db.creation property is true.
- Create an execution which runs the execute goal in the generate-sources Maven lifecycle phase.
- Configure the created execution by following these steps:
- Configure the JDBC driver, database url, username, and password.
- Ensure that the changes are committed automatically.
- Configure the location of the SQL script which creates the schema of our database.
- Add the H2 database as the dependency of this plugin.
The configuration of the SQL Maven plugin looks as follows:
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>sql-maven-plugin</artifactId>
<version>1.5</version>
<configuration>
<skip>${skip.db.creation}</skip>
</configuration>
<executions>
<execution>
<id>create-database-h2</id>
<phase>generate-sources</phase>
<goals>
<goal>execute</goal>
</goals>
<configuration>
<driver>${db.driver}</driver>
<url>${db.url}</url>
<username>${db.username}</username>
<password>${db.password}</password>
<autocommit>true</autocommit>
<srcFiles>
<srcFile>src/main/resources/schema.sql</srcFile>
</srcFiles>
</configuration>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.3.174</version>
</dependency>
</dependencies>
</plugin>Let’s move on and find out how we can configured the jOOQ-codegen Maven plugin.
Generating Code from the Database
Our last task is to configure the jOOQ-codegen Maven plugin. Let’s find out how this is done.
First, We have to add a jooq.generator.db.dialect property to the properties section section of the pom.xml file. This property specifies the correct database dialect and it is used to configure the jOOQ-codegen Maven plugin. Because our example application uses the H2 database, we have to set the value of this property to org.jooq.util.h2.H2Database.
The reason why the database dialect is specified as a property is that this gives us the possibility to use different databases in different environments.
The relevant part of our POM file looks as follows:
<properties>
<jooq.generator.db.dialect>org.jooq.util.h2.H2Database</jooq.generator.db.dialect>
</properties>Second, we have to configure the jOOQ-codegen Maven plugin. We can do this by following these steps:
- Add the plugin declaration to the plugins section of the pom.xml file.
- Create an execution which runs the generate goal of the jOOQ-codegen Maven plugin during the generate-sources Maven lifecycle phase.
- Configure the plugin by following these steps:
- Configure the JDBC connection and set the name of the driver class, database url, username, and password. Remember that the actual property values are read from the profile specific configuration file.
- Configure the database which is used as the source by following these steps:
- Ensure that the used database dialect is read from the jooq.generator.db.dialect property.
- Configure the code generation to include all tables found from the PUBLIC schema.
- Configure the code generation to generate classes for database tables and records.
- Configure the target package and directory. These configuration options are described in the following:
- The target package specifies the package which is the root package of the created classes.
- The target directory specifies the directory in which the classes are generated.
- Add the H2 database as the dependency of this plugin.
The configuration of the jOOQ-codegen Maven plugin looks as follows:
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.2.2</version>
<executions>
<execution>
<id>generate-h2</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>${h2.version}</version>
</dependency>
</dependencies>
<configuration>
<jdbc>
<driver>${db.driver}</driver>
<url>${db.url}</url>
<user>${db.username}</user>
<password>${db.password}</password>
</jdbc>
<generator>
<database>
<name>${jooq.generator.db.dialect}</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>PUBLIC</inputSchema>
</database>
<generate>
<records>true</records>
</generate>
<target>
<packageName>net.petrikainulainen.spring.jooq.todo.db</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>
You can get more information about the code generation from the jOOQ reference manual:
Let’s find out what happens when the code generation is run.
What Is Generated?
When the generate goal of the jOOQ-codegen Maven plugin is invoked, it analyzes the schema of the database and generates classes to the configured target directory and package. In our situation, this means that:
- The code is generated to the directory target/generated-sources/jooq.
- The root package of the generated classes is net.petrikainulainen.spring.jooq.todo.db.
The configuration which we created during this blog post ensures that the following classes are created:
- The classes generated to the net.petrikainulainen.spring.jooq.todo.db package contain the metadata of the database. jOOQ calls these classes “global” artifacts.
- The net.petrikainulainen.spring.jooq.todo.db.tables.Todos class is a table class which describes the structure of the a single database table. We can use this class to write database queries against the data stored to the todos database table.
- The net.petrikainulainen.spring.jooq.todo.db.tables.recods.TodoRecord class is a record class which contains the information of a single table row. The database queries which fetch data from the todos database table return TodoRecord objects (if we choose to do so).
Summary
We have now successfully configured the jOOQ-codegen Maven plugin to generate code from our database. This tutorial has taught us two things:
- We learned how we can configure the jOOQ-codegen Maven plugin to generate code from a database.
- We learned what kind of classes are created when the code generation is started.
The next part of this tutorial describes how we can add CRUD functions to a simple web application by using the classes generated by jOOQ.
- The example application of this blog post is available at Github.
Reference: Using jOOQ with Spring: Code Generation from our JCG partner Petri Kainulainen at the Petri Kainulainen blog.
