Introduction to Jooq with Spring

1. Overview

This article will introduce Jooq Object Oriented Querying – Jooq – and a simple way to set it up in collaboration with the Spring Framework.

Most Java applications have some sort of SQL persistence and access that layer with the help of higher-level tools such as JPA. And while that’s useful, in some cases you really need a finer, more nuanced tool to get to your data or to actually take advantage of everything the underlying DB has to offer.

Jooq avoids some typical ORM patterns and generates code that allows us to build typesafe queries, and get complete control of the generated SQL via a clean and powerful fluent API.

This article focuses on Spring MVC. Our article Spring Boot Support for jOOQ describes how to use jOOQ in Spring Boot.

2. Maven Dependencies

The following dependencies are necessary to run the code in this tutorial.

2.1. jOOQ

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.19.7</version>
</dependency>

2.2. Spring

There are several Spring dependencies required for our example; however, to make things simple, we just need to explicitly include two of them in the POM file:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>6.0.13</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>6.0.13</version>
</dependency>

2.3. Database

To make things easy for our example, we will make use of the H2 embedded database:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

3. Code Generation

3.1. Database Structure

Let’s introduce the database structure we will be working with throughout this article. Suppose that we need to create a database for a publisher to store information the books and authors they manage, where an author may write many books and a book may be co-written by many authors.

To make it simple, we will generate only three tables: the book for books, author for authors, and another table called author_book to represent the many-to-many relationship between authors and books. The author table has three columns: id, first_name, and last_name. The book table contains only a title column and the id primary key.

The following SQL queries, stored in the intro_schema.sql resource file, will be executed against the database we have already set up before to create the necessary tables and populate them with sample data:

DROP TABLE IF EXISTS author_book, author, book;

CREATE TABLE author (
  id             INT          NOT NULL PRIMARY KEY,
  first_name     VARCHAR(50),
  last_name      VARCHAR(50)  NOT NULL
);

CREATE TABLE book (
  id             INT          NOT NULL PRIMARY KEY,
  title          VARCHAR(100) NOT NULL
);

CREATE TABLE author_book (
  author_id      INT          NOT NULL,
  book_id        INT          NOT NULL,
  
  PRIMARY KEY (author_id, book_id),
  CONSTRAINT fk_ab_author     FOREIGN KEY (author_id)  REFERENCES author (id)  
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_ab_book       FOREIGN KEY (book_id)    REFERENCES book   (id)
);

INSERT INTO author VALUES 
  (1, 'Kathy', 'Sierra'), 
  (2, 'Bert', 'Bates'), 
  (3, 'Bryan', 'Basham');

INSERT INTO book VALUES 
  (1, 'Head First Java'), 
  (2, 'Head First Servlets and JSP'),
  (3, 'OCA/OCP Java SE 7 Programmer');

INSERT INTO author_book VALUES (1, 1), (1, 3), (2, 1);

3.2. Properties Maven Plugin

We will use three different Maven plugins to generate the Jooq code. The first of these is the Properties Maven plugin.

This plugin is used to read configuration data from a resource file. It is not required since the data may be directly added to the POM, but it is a good idea to manage the properties externally.

In this section, we will define properties for database connections, including the JDBC driver class, database URL, username, and password, in a file named intro_config.properties. Externalizing these properties makes it easy to switch the database or just change the configuration data.

The read-project-properties goal of this plugin should be bound to an early phase so that the configuration data can be prepared for use by other plugins. In this case, it is bound to the initialize phase:

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>properties-maven-plugin</artifactId>
    <version>1.0.0</version>
    <executions>
        <execution>
            <phase>initialize</phase>
            <goals>
                <goal>read-project-properties</goal>
            </goals>
            <configuration>
                <files>
                    <file>src/main/resources/intro_config.properties</file>
                </files>
            </configuration>
        </execution>
    </executions>
</plugin>

3.3. SQL Maven Plugin

The SQL Maven plugin is used to execute SQL statements to create and populate database tables. It will make use of the properties that have been extracted from the intro_config.properties file by the Properties Maven plugin and take the SQL statements from the intro_schema.sql resource.

The SQL Maven plugin is configured as below:

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>
    <version>1.5</version>
    <executions>
        <execution>
            <phase>initialize</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <driver>${db.driver}</driver>
                <url>${db.url}</url>
                <username>${db.username}</username>
                <password>${db.password}</password>
                <srcFiles>
                    <srcFile>src/main/resources/intro_schema.sql</srcFile>
                </srcFiles>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.2.224</version>
        </dependency>
    </dependencies>
</plugin>

Note that this plugin must be placed later than the Properties Maven plugin in the POM file since their execution goals are both bound to the same phase, and Maven will execute them in the order they are listed.

3.4. jOOQ Codegen Plugin

The Jooq Codegen Plugin generates Java code from a database table structure. Its generate goal should be bound to the generate-sources phase to ensure the correct order of execution. The plugin metadata looks like the following:

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>
                <jdbc>
                    <driver>${db.driver}</driver>
                    <url>${db.url}</url>
                    <user>${db.username}</user>
                    <password>${db.password}</password>
                </jdbc>
                <generator>
                    <target>
                        <packageName>com.baeldung.jooq.introduction.db</packageName>
                        <directory>src/main/java</directory>
                    </target>
                </generator>
            </configuration>
        </execution>
    </executions>
</plugin>

3.5. Generating Code

To finish up the process of source code generation, we need to run the Maven generate-sources phase. In Eclipse, we can do this by right-clicking on the project and choosing Run As –> Maven generate-sources. After the command is completed, source files corresponding to the author, book, author_book tables (and several others for supporting classes) are generated.

Let’s dig into table classes to see what Jooq produced. Each class has a static field of the same name as the class, except that all letters in the name are capitalized. The following are code snippets taken from the generated classes’ definitions:

The Author class:

public class Author extends TableImpl<AuthorRecord> {
    public static final Author AUTHOR = new Author();

    // other class members
}

The Book class:

public class Book extends TableImpl<BookRecord> {
    public static final Book BOOK = new Book();

    // other class members
}

The AuthorBook class:

public class AuthorBook extends TableImpl<AuthorBookRecord> {
    public static final AuthorBook AUTHOR_BOOK = new AuthorBook();

    // other class members
}

The instances referenced by those static fields will serve as data access objects to represent the corresponding tables when working with other layers in a project.

4. Spring Configuration

4.1. Translating jOOQ Exceptions to Spring

In order to make exceptions thrown from Jooq execution consistent with Spring support for database access, we need to translate them into subtypes of the DataAccessException class.

Let’s define an implementation of the ExecuteListener interface to convert exceptions:

public class ExceptionTranslator extends DefaultExecuteListener {
    public void exception(ExecuteContext context) {
        SQLDialect dialect = context.configuration().dialect();
        SQLExceptionTranslator translator 
          = new SQLErrorCodeSQLExceptionTranslator(dialect.name());
        context.exception(translator
          .translate("Access database using Jooq", context.sql(), context.sqlException()));
    }
}

This class will be used by the Spring application context.

4.2. Configuring Spring

This section will go through steps to define a PersistenceContext that contains metadata and beans to be used in the Spring application context.

Let’s get started by applying necessary annotations to the class:

• @Configuration: Make the class to be recognized as a container for beans
• @ComponentScan: Configure scanning directives, including the value option to declare an array of package names to search for components. In this tutorial, the package to be searched is the one generated by the Jooq Codegen Maven plugin
• @EnableTransactionManagement: Enable transactions to be managed by Spring
• @PropertySource: Indicate the locations of the properties files to be loaded. The value in this article points to the file containing configuration data and dialect of the database, which happens to be the same file mentioned in subsection 4.1.

@Configuration
@ComponentScan({"com.baeldung.Jooq.introduction.db.public_.tables"})
@EnableTransactionManagement
@PropertySource("classpath:intro_config.properties")
public class PersistenceContext {
    // Other declarations
}

Next, use an Environment object to get the configuration data, which is then used to configure the DataSource bean:

@Autowired
private Environment environment;

@Bean
public DataSource dataSource() {
    JdbcDataSource dataSource = new JdbcDataSource();

    dataSource.setUrl(environment.getRequiredProperty("db.url"));
    dataSource.setUser(environment.getRequiredProperty("db.username"));
    dataSource.setPassword(environment.getRequiredProperty("db.password"));

    return dataSource; 
}

Now we define several beans to work with database access operations:

@Bean
public TransactionAwareDataSourceProxy transactionAwareDataSource() {
    return new TransactionAwareDataSourceProxy(dataSource());
}

@Bean
public DataSourceTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource());
}

@Bean
public DataSourceConnectionProvider connectionProvider() {
    return new DataSourceConnectionProvider(transactionAwareDataSource());
}

@Bean
public ExceptionTranslator exceptionTransformer() {
    return new ExceptionTranslator();
}
    
@Bean
public DefaultDSLContext dsl() {
    return new DefaultDSLContext(configuration());
}

Finally, we provide a Jooq Configuration implementation and declare it as a Spring bean to be used by the DSLContext class:

@Bean
public DefaultConfiguration configuration() {
    DefaultConfiguration JooqConfiguration = new DefaultConfiguration();
    jooqConfiguration.set(connectionProvider());
    jooqConfiguration.set(new DefaultExecuteListenerProvider(exceptionTransformer()));

    String sqlDialectName = environment.getRequiredProperty("jooq.sql.dialect");
    SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);
    jooqConfiguration.set(dialect);

    return jooqConfiguration;
}

5. Using jOOQ With Spring

This section demonstrates the use of Jooq in common database access queries. There are two tests, one for commit and one for rollback, for each type of “write” operation, including inserting, updating, and deleting data. The use of “read” operation is illustrated when selecting data to verify the “write” queries.

We will begin by declaring an auto-wired DSLContext object and instances of Jooq generated classes to be used by all testing methods:

@Autowired
private DSLContext dsl;

Author author = Author.AUTHOR;
Book book = Book.BOOK;
AuthorBook authorBook = AuthorBook.AUTHOR_BOOK;

5.1. Inserting Data

The first step is to insert data into tables:

dsl.insertInto(author)
  .set(author.ID, 4)
  .set(author.FIRST_NAME, "Herbert")
  .set(author.LAST_NAME, "Schildt")
  .execute();
dsl.insertInto(book)
  .set(book.ID, 4)
  .set(book.TITLE, "A Beginner's Guide")
  .execute();
dsl.insertInto(authorBook)
  .set(authorBook.AUTHOR_ID, 4)
  .set(authorBook.BOOK_ID, 4)
  .execute();

A SELECT query to extract data:

Result<Record3<Integer, String, Integer>> result = dsl
  .select(author.ID, author.LAST_NAME, DSL.count())
  .from(author)
  .join(authorBook)
  .on(author.ID.equal(authorBook.AUTHOR_ID))
  .join(book)
  .on(authorBook.BOOK_ID.equal(book.ID))
  .groupBy(author.LAST_NAME)
  .fetch();

The above query produces the following output:

+----+---------+-----+
|  ID|LAST_NAME|count|
+----+---------+-----+
|   1|Sierra   |    2|
|   2|Bates    |    1|
|   4|Schildt  |    1|
+----+---------+-----+

The result is confirmed by the Assert API:

assertEquals(3, result.size());
assertEquals("Sierra", result.getValue(0, author.LAST_NAME));
assertEquals(Integer.valueOf(2), result.getValue(0, DSL.count()));
assertEquals("Schildt", result.getValue(2, author.LAST_NAME));
assertEquals(Integer.valueOf(1), result.getValue(2, DSL.count()));

When a failure occurs due to an invalid query, an exception is thrown and the transaction rolls back. In the following example, the INSERT query violates a foreign key constraint, resulting in an exception:

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenInserting_thenFail() {
    dsl.insertInto(authorBook)
      .set(authorBook.AUTHOR_ID, 4)
      .set(authorBook.BOOK_ID, 5)
      .execute();
}

5.2. Updating Data

Now let’s update the existing data:

dsl.update(author)
  .set(author.LAST_NAME, "Baeldung")
  .where(author.ID.equal(3))
  .execute();
dsl.update(book)
  .set(book.TITLE, "Building your REST API with Spring")
  .where(book.ID.equal(3))
  .execute();
dsl.insertInto(authorBook)
  .set(authorBook.AUTHOR_ID, 3)
  .set(authorBook.BOOK_ID, 3)
  .execute();

Get the necessary data:

Result<Record3<Integer, String, String>> result = dsl
  .select(author.ID, author.LAST_NAME, book.TITLE)
  .from(author)
  .join(authorBook)
  .on(author.ID.equal(authorBook.AUTHOR_ID))
  .join(book)
  .on(authorBook.BOOK_ID.equal(book.ID))
  .where(author.ID.equal(3))
  .fetch();

The output should be:

+----+---------+----------------------------------+
|  ID|LAST_NAME|TITLE                             |
+----+---------+----------------------------------+
|   3|Baeldung |Building your REST API with Spring|
+----+---------+----------------------------------+

The following test will verify that Jooq worked as expected:

assertEquals(1, result.size());
assertEquals(Integer.valueOf(3), result.getValue(0, author.ID));
assertEquals("Baeldung", result.getValue(0, author.LAST_NAME));
assertEquals("Building your REST API with Spring", result.getValue(0, book.TITLE));

In case of a failure, an exception is thrown and the transaction rolls back, which we confirm with a test:

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenUpdating_thenFail() {
    dsl.update(authorBook)
      .set(authorBook.AUTHOR_ID, 4)
      .set(authorBook.BOOK_ID, 5)
      .execute();
}

5.3. Deleting Data

The following method deletes some data:

dsl.delete(author)
  .where(author.ID.lt(3))
  .execute();

Here is the query to read the affected table:

Result<Record3<Integer, String, String>> result = dsl
  .select(author.ID, author.FIRST_NAME, author.LAST_NAME)
  .from(author)
  .fetch();

The query output:

+----+----------+---------+
|  ID|FIRST_NAME|LAST_NAME|
+----+----------+---------+
|   3|Bryan     |Basham   |
+----+----------+---------+

The following test verifies the deletion:

assertEquals(1, result.size());
assertEquals("Bryan", result.getValue(0, author.FIRST_NAME));
assertEquals("Basham", result.getValue(0, author.LAST_NAME));

On the other hand, if a query is invalid, it will throw an exception and the transaction rolls back. The following test will prove that:

@Test(expected = DataAccessException.class)
public void givenInvalidData_whenDeleting_thenFail() {
    dsl.delete(book)
      .where(book.ID.equal(1))
      .execute();
}

6. Conclusion

This tutorial introduced the basics of Jooq, a Java library for working with databases. It covered the steps to generate source code from a database structure and how to interact with that database using the newly created classes.