Using jOOQ With Spring: Code Generation

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 that 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 how you can configure the application context of a Spring application which uses jOOQ. You can 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:

  1. 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.
  2. 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.
  3. 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:

  1. Add the plugin declaration to the plugins section of the pom.xml file.
  2. Create an execution which runs the read-project-properties goal in the initialize Maven lifecycle phase.
  3. 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:

  1. Add the plugin declaration to the plugins section of the pom.xml file.
  2. Ensure that the schema generation is skipped if the value of skip.db.creation property is true.
  3. Create an execution which runs the execute goal in the generate-sources Maven lifecycle phase.
  4. Configure the created execution by following these steps:
    1. Configure the JDBC driver, database url, username, and password.
    2. Ensure that the changes are committed automatically.
    3. Configure the location of the SQL script which creates the schema of our database.
  5. 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>

	<!-- Skip DB creation if the value of the skip.db.creation property is true -->
	<configuration>
		<skip>${skip.db.creation}</skip>
	</configuration>

	<executions>
		<!-- Execute SQL statements to the configured database -->
		<execution>
			<id>create-database-h2</id>
			<phase>generate-sources</phase>
			<goals>
				<goal>execute</goal>
			</goals>
			<configuration>
				<!-- Configure the database connection -->
				<driver>${db.driver}</driver>
				<url>${db.url}</url>
				<username>${db.username}</username>
				<password>${db.password}</password>

				<!-- Ensure that our changes are committed automatically -->
				<autocommit>true</autocommit>
				<!-- Configure the location of the invoked SQL script -->
				<srcFiles>
					<srcFile>src/main/resources/schema.sql</srcFile>
				</srcFiles>
			</configuration>
		</execution>
	</executions>

	<dependencies>
		<!-- 
			Because we use the H2 database, we have to add it as the dependency 
			of this plugin.
		-->
		<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:

  1. Add the plugin declaration to the plugins section of the pom.xml file.
  2. Create an execution which runs the generate goal of the jOOQ-codegen Maven plugin during the generate-sources Maven lifecycle phase.
  3. Configure the plugin by following these steps:
    1. 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.
    2. Configure the database which is used as the source by following these steps:
      1. Ensure that the used database dialect is read from the jooq.generator.db.dialect property.
      2. Configure the code generation to include all tables found from the PUBLIC schema.
    3. Configure the code generation to generate classes for database tables and records.
    4. 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.
  4. 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.4.4</version>
 
    <executions>
		<!-- Generate the required class from the database -->
        <execution>
            <id>generate-h2</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
 
    <dependencies>
		<!--
			Because we use the H2 database, we have to add it as the dependency
			of this plugin.
		-->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.3.174</version>
        </dependency>
    </dependencies>
 
    <configuration>
		<!-- Configure the database connection -->
        <jdbc>
            <driver>${db.driver}</driver>
            <url>${db.url}</url>
            <user>${db.username}</user>
            <password>${db.password}</password>
        </jdbc>
 
        <generator>
            <database>
				<!-- Configure the used database dialect -->
                <name>${jooq.generator.db.dialect}</name>
				<!-- Include all tables found from the PUBLIC schema -->
                <includes>.*</includes>
                <excludes></excludes>
                <inputSchema>PUBLIC</inputSchema>
            </database>
			<!-- Generate classes for tables and records -->
            <generate>
                <records>true</records>
            </generate>
			<!-- Configure the target package and directory -->
            <target>
                <packageName>net.petrikainulainen.spring.jooq.todo.db</packageName>
                <directory>target/generated-sources/jooq</directory>
            </target>
        </generator>
    </configuration>
</plugin>

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 generate code from our database by using the jOOQ-codegen Maven plugin.
  • We learned what kind of classes are created when the code generation is run.

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.

P.S. You can get the example application of this blog post from Github.

If you want to use jOOQ with Spring Framework, you should read my Using jOOQ With Spring tutorial.
12 comments… add one
  • Anonymous Mar 25, 2014 @ 16:24

    Hi Petri, thanks for the great article! When I take the github code, the configuration part of the project builds, but the jooq-only part fails. What is the difference between the configuration and jooq-only versions, as far as what they should be used for? I'd like to have a complete spring mvc project that is based on your example, with a single view, controller, model, etc. Do you plan to expand your example to be like a 'hello world' version of jooq-springmvc? Thanks again for the information.

    • Petri Mar 25, 2014 @ 16:42

      Do you get the error because the Spring container cannot find a correct implementation for the DateTimeService bean? If this is the case, you should set the active Spring profile to 'application'. This is required because the integration tests use a different implementation of that interface than the application.

      Are you running the example by using Jetty Maven plugin (this should set the profile automatically) or do you run it by using an external servlet container like Tomcat?

      The jooq-only example application will be divided into two "parts":

      • The frontend is a single page application which is implemented by using AngularJS.
      • The backend provides a REST API which is used by the single page application.

      At the moment the application has only the backend which doesn't have end-to-end tests. Also, it is missing a few small features which will be added to it in the future.

      The bad news is that it won't be a "regular" Spring MVC application. The good news is that the controller isn't aware of jOOQ. This means that you can replace the REST API with a "regular" Spring MVC application if you want to.

  • Anonymous Jun 14, 2014 @ 20:18

    Spring sucks. It's great news.

  • incze May 3, 2015 @ 5:01

    creation_time timestamp not null default current_timestamp,
    ...
    modification_time timestamp not null default current_timestamp

    this won't work on mysql: http://stackoverflow.com/questions/4897002/mysql-current-timestamp-on-create-and-on-update

    • Petri May 5, 2015 @ 18:39

      Thank you for pointing this out! I will add a comment to the README when I have time to do it.

  • Ronnie Wang Jan 20, 2017 @ 5:11

    Hi Petri:

    Thanks for your share, but there are some format errors. In this post, all the xml code can not show out correctly. I don't know whether it's my Chrome's problem or not.

    • Petri Jan 20, 2017 @ 9:14

      Hi,

      Thank you for pointing this point. It should be fixed now. It seems that "something" is messing up with the XML markup because I have to fix it on a regular basis. I guess it is time to find out what is going on.

  • Programming Mar 1, 2018 @ 17:57

    Thank yyou for this great article, I have shared it on Facebook.

    • Petri Mar 12, 2018 @ 10:43

      You are welcome. Also, thank you for sharing.

  • Akash Aug 16, 2022 @ 20:53

    Hi Petri, Great Article It's help me a lot I just want to know how can I generate these classes on my own config (e.g. As JOOQ put the annotations on the getter/setter but I want that annotations on the variables ) can I do this.

  • Ashwani Gupta Sep 28, 2022 @ 21:40

    Using Jooq, I cannot use other storage for storing properties of my application.

Leave a Reply