jOOQ Tips: Implementing a Read-Only One-to-Many Relationship With SimpleFlatMapper

This blog post describes how we can implement a one-to-many relationship when we are querying data from the database with jOOQ. After we have finished this blog post, we:

• Can get the required dependencies with Maven and Gradle.
• Can get the required information from the database by using only one database query.
• Understand how we can implement a read-only one-to-many relationship with jOOQ.

Let's start by taking a quick look at the requirements of our example.

The Requirements of Our Example

The database of our example application has two tables:

First, the students table contains the information of the students saved to the database. This table has two columns:

• The id column contains the unique id of the student.
• The name column contains the full name of the student.

Second, the books table contains the information of the books which are owned by the students found from the students table. This table has three columns:

• The id column contains the unique id of the book.
• The name column contains the name of the book.
• The student_id column contains the id of the student who owns the book.

The following figure illustrates the structure of our database:

During this blog post we will write a StudentRepository class which provides finder methods that are used to query student information from the database. These methods are:

• The List<StudentDTO> findAll() method returns the information of all students found from the database.
• The Optional<StudentDTO> findById(Long id) method returns an Optional object which contains the information of the specified student. If no student is found, this method returns an empty Optional object.

The StudentDTO class contains the information of one student, and its source code looks as follows:

import java.util.List;

public class StudentDTO {

    private Long id;
    private String name;
    private List<BookDTO> books;

    //Getters and setters are omitted
}

As we can see, both methods must also return the books which are owned by the returned students. The BookDTO class contains the information of one book, and its source code looks as follows:

public class BookDTO {

    private Long id;
    private String name;

    //Getters and setters are omitted
}

Before we can implement the required finder methods, we have to get the required dependencies. Next, we will find out how we can get the required dependencies with Maven and Gradle.

Getting the Required Dependencies

We will use library called SimpleFlatMapper for transforming the query results into StudentDTO objects. Because we want to map multiple rows to a one object, we have to process ResultSet objects. That's why we have to use the JDBC integration of SimpleFlatMapper. In other words, we have to declare the sfm-jdbc dependency in our build script.

If we are using Maven, we have to add the following snippet to the dependencies section of our POM file:

<dependency>
	<groupId>org.simpleflatmapper</groupId>
	<artifactId>sfm-jdbc</artifactId>
	<version>3.17.4</version>
</dependency>

If we are using Gradle, we have to add the sfm-jdbc dependency to the compile dependency configuration. In other words, we have to add the following snippet to our build.gradle file:

dependencies {
	compile(
		'org.simpleflatmapper:sfm-jdbc:3.17.4'
	)
}

Let's move on and find out how we can implement a read-only one-to-many relationship with Spring Framework, jOOQ, and SimpleFlatMapper.

Implementing a Read-Only One-To-Many Relationship with jOOQ and SimpleFlatMapper

Before we can implement our finder methods, we have to create our repository class by following these steps:

1. Create a new package-private class called StudentRepository and annotate the created class with the @Repository annotation.
2. Add a DSLContext field to the created class and ensure that the Spring container injects the real DSLContext object into this field by using constructor injection.
3. Add a JdbcMapper<StudentDTO> field to the created class. We will use this field when we transform our query results into StudentDTO objects.
4. Ensure that the constructor of the StudentRepository class creates a new JdbcMapper<StudentDTO> object by using the JdbcMapperFactory class. When we create a new JdbcMapper<StudentDTO> object, we have to configure the keys which are used to identify unique StudentDTO and BookDTO objects. Because the primary key columns of the students and books tables are found from the ResultSet by using the aliases: id and books_id, we have to mark these two aliases as keys.
5. Store the created JdbcMapper<StudentDTO> object in the jdbcMapper field.

After we have created our repository class, its source code looks as follows:

import org.jooq.DSLContext;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
class StudentRepository {

    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }
}

We are now ready to write our finder methods. Let's start by implementing the findAll() method.

Implementing the findAll() Method

When we want to return all students found from the database, we have to implement our finder method by following these steps:

First, we have to add a findAll() method to our repository class and ensure that our database query is run inside a read-only transaction. This method takes no method parameters, and it returns a List<StudentDTO> object.

After we have added the findAll() method to our repository class, the source code of the StudentRepository class looks as follows:

import org.jooq.DSLContext;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
class StudentRepository {

    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }

    @Transactional(readOnly = true)
    public List<StudentDTO> findAll() {

    }
}

Second, we have to implement the database query that returns all students found from the database. We can implement this database query with jOOQ by following these steps:

1. Select the id and name columns from the students table. Because the names of these columns are equal to the field names of the StudentDTO class, we don't have to use aliases.
2. Select the id and name columns from the books table. When we select columns whose values are set to the child object of the one-to-many relationship, we have to set alieses to the selected columns or otherwise the SimpleFlatMapper cannot populate the child objects. We can construct these aliases by using this rule: [the name of the collection field]_[the field name of the child class]. In other words, because the name of the List<BookDTO> field is books and the BookDTO class has the id and name fields, we have to use these aliases: books_id and books_name.
3. Add a FROM clause to the created database query and ensure that the query results are selected from the students table.
4. Ensure that our database query returns the books of the returned students. Because we want that our query returns students who don't have any books, we have to use a left join.
5. Sort the query results in ascending order by using the id column of the students table. This is a crucial step because it ensures that SimpleFlatMapper doesn't create duplicate StudentDTO objects when it transforms our query results into a list of StudentDTO objects.
6. Store the ResultQuery object that contains our database query in a local variable.

After we have implemented our database query with jOOQ, the source code of the StudentRepository class looks as follows:

import org.jooq.DSLContext;
import org.jooq.ResultQuery;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

import static net.petrikainulainen.jooqtips.db.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.db.Tables.STUDENTS;

@Repository
class StudentRepository {
    
    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }
    
    @Transactional(readOnly = true)
    public List<StudentDTO> findAll() {
        ResultQuery query = jooq.select(STUDENTS.ID,
                STUDENTS.NAME,
                BOOKS.ID.as("books_id"),
                BOOKS.NAME.as("books_name")
        )
                .from(STUDENTS)
                .leftJoin(BOOKS).on(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                .orderBy(STUDENTS.ID.asc());
    }
}

Third, we have to transform the ResultQuery object into a list of StudentDTO objects. We can do this by following these steps:

1. Add a private method called transformQueryIntoList() to our repository class. This method takes a ResultQuery object as a method parameter and returns a List<Student> object.
2. Implement the transformQueryIntoList() method. Our implementation transforms our query object into a list of StudentDTO objects and returns the created list. Also, if our implementation throws an SQLException, this method wraps it into a unchecked exception called DataQueryException and throws the created DataQueryException.
3. Ensure that the findAll() method invokes the transformQueryIntoList() method and returns a list of StudentDTO objects.

After we have transformed our query results into a list of StudentDTO objects, the source code of our repository class looks as follows:

import org.jooq.DSLContext;
import org.jooq.ResultQuery;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;

import static net.petrikainulainen.jooqtips.db.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.db.Tables.STUDENTS;

@Repository
class StudentRepository {

    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }

    @Transactional(readOnly = true)
    public List<StudentDTO> findAll() {
        ResultQuery query = jooq.select(STUDENTS.ID,
                STUDENTS.NAME,
                BOOKS.ID.as("books_id"),
                BOOKS.NAME.as("books_name")
        )
                .from(STUDENTS)
                .leftJoin(BOOKS).on(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                .orderBy(STUDENTS.ID.asc())
                .fetchResultSet();

        return transformQueryIntoList(query);
    }

    private List<StudentDTO> transformQueryIntoList(ResultQuery query) {
        try (ResultSet rs = query.fetchResultSet()) {
            return jdbcMapper.stream(rs).collect(Collectors.toList());
        } catch (SQLException ex) {
            throw new DataQueryException(
                    "Cannot transform query result into a list because of an error",
                    ex
            );
        }
    }
}

The source code of the DataQueryException class looks as follows:

class DataQueryException extends RuntimeException {

    DataQueryException(String messageTemplate, Object... params) {
        super(String.format(messageTemplate, params));
    }

    DataQueryException(String message, Throwable cause) {
        super(message, cause);
    }
}

We have now implemented a repository method that returns the information of all students found from the database. Next, we will find out how we can implement a repository method that returns the information of the specified student.

Implementing the findById() Method

When we want to return the information of the specified student, we have to implement our finder method by following these steps:

First, we have to add a findById() method to our repository class and ensure that our database query is run inside a read-only transaction. This method takes the id of the requested student (a Long object) as a method parameter and returns an Optional<StudentDTO> object.

After we have added the findById() method to our repository class, the relevant part of the StudentRepository class looks as follows:

import org.jooq.DSLContext;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Optional;

@Repository
class StudentRepository {

    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }
    
    @Transactional(readOnly = true)
    public Optional<StudentDTO> findById(Long id) {
    
    }
}

Second, we have to implement the database query that returns the information of the specified student. We can implement this database query with jOOQ by making the following changes to the database query which returns the information of all students:

1. Ensure that our database query returns the information of the student whose id is equal to the id that is given as a method parameter.
2. Remove the ORDER BY clause. Even though our query results contain multiple rows, we don't need the ORDER BY clause because all returned rows contain the same student id and full name.

After we have implemented our database query with jOOQ, the relevant part of the StudentDTO class looks as follows:

import org.jooq.DSLContext;
import org.jooq.ResultQuery;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Optional;

import static net.petrikainulainen.jooqtips.db.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.db.Tables.STUDENTS;

@Repository
class StudentRepository {
    
    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }
    
    @Transactional(readOnly = true)
    public Optional<StudentDTO> findById(Long id) {
        ResultQuery query = jooq.select(STUDENTS.ID,
                STUDENTS.NAME,
                BOOKS.ID.as("books_id"),
                BOOKS.NAME.as("books_name")
        )
                .from(STUDENTS)
                .leftJoin(BOOKS).on(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                .where(STUDENTS.ID.eq(id));
    }
}

Third, we have to add a method called transformQueryIntoObject() to the StudentRepository class. This method transforms a ResultQuery object into an Optional<StudentDTO> object and returns the created object. After we have added this method to our repository class, we have to implement this method by following these steps:

1. Get a ResultSet object that contains our query results.
2. Transform the ResultSet object given as a method parameter into an Iterator<StudentDTO> object.
3. If the created iterator is empty, return an empty Optional object.
4. If the created iterator contains one object, return an Optional object that contains the found StudentDTO object.
5. If the created iterator contains multiple objects, throw a new DataQueryException.
6. If our implementation throws an SQLException, we must wrap the thrown exception into a unchecked exception called DataQueryException and throw the created DataQueryException.

After we have implemented this method, the relevant part of the StudentRepository class looks as follows:

import org.jooq.DSLContext;
import org.jooq.ResultQuery;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Optional;

import static net.petrikainulainen.jooqtips.db.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.db.Tables.STUDENTS;

@Repository
class StudentRepository {

    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }

    @Transactional(readOnly = true)
    public Optional<StudentDTO> findById(Long id) {
        ResultQuery query = jooq.select(STUDENTS.ID,
                STUDENTS.NAME,
                BOOKS.ID.as("books_id"),
                BOOKS.NAME.as("books_name")
        )
                .from(STUDENTS)
                .leftJoin(BOOKS).on(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                .where(STUDENTS.ID.eq(id))
                .fetchResultSet();
    }

    private Optional<StudentDTO> transformQueryIntoObject(ResultQuery query) {
        try (ResultSet rs = query.fetchResultSet()) {
            Iterator<StudentDTO> students = jdbcMapper.iterator(rs);
            if (!students.hasNext()) {
                return Optional.empty();
            }

            StudentDTO found = students.next();
            if (students.hasNext()) {
                throw new DataQueryException("Multiple students were found");
            }

            return Optional.of(found);
        }
        catch (SQLException ex) {
            throw new DataQueryException(
                    "Cannot transform query result into object because of an error",
                    ex
            );
        }
    }
}

Fourth, we have to ensure that the findById() method transforms the ResultQuery object into an Optional<StudentDTO> object and returns the created object.

After we have done this, the relevant part of the StudentRepository class looks as follows:

import org.jooq.DSLContext;
import org.jooq.ResultQuery;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Optional;

import static net.petrikainulainen.jooqtips.db.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.db.Tables.STUDENTS;

@Repository
class StudentRepository {

    private final JdbcMapper<StudentDTO> jdbcMapper;
    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jdbcMapper = JdbcMapperFactory
                .newInstance()
                .addKeys("id", "books_id")
                .newMapper(StudentDTO.class);

        this.jooq = jooq;
    }

    @Transactional(readOnly = true)
    public Optional<StudentDTO> findById(Long id) {
        ResultQuery query = jooq.select(STUDENTS.ID,
                STUDENTS.NAME,
                BOOKS.ID.as("books_id"),
                BOOKS.NAME.as("books_name")
        )
                .from(STUDENTS)
                .leftJoin(BOOKS).on(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                .where(STUDENTS.ID.eq(id))
                .fetchResultSet();

        return transformQueryIntoObject(query);
    }

    private Optional<StudentDTO> transformQueryIntoObject(ResultQuery query) {
        try (ResultSet rs = query.fetchResultSet()) {
            Iterator<StudentDTO> students = jdbcMapper.iterator(rs);
            if (!students.hasNext()) {
                return Optional.empty();
            }

            StudentDTO found = students.next();
            if (students.hasNext()) {
                throw new DataQueryException("Multiple students were found");
            }

            return Optional.of(found);
        }
        catch (SQLException ex) {
            throw new DataQueryException(
                    "Cannot transform query result into object because of an error",
                    ex
            );
        }
    }
}

We have now implemented two finder methods which demonstrate how we can implement a read-only one-to-many relationship with jOOQ and SimpleFlatMapper. Let's summarize what we learned from this blog post.

Summary

This blog post has taught us five things:

• Because we want to map multiple rows to a one object, we have to use the JDBC integration of the SimpleFlatMapper
• We have to declare the sfm-jdbc dependency in our build script.
• We must ensure that the primary key of the "root" object won't change until all rows which contain the information of the same "root" object have been processed by the JdbcMapper object.
• We can transform a ResultSet object into the returned object by using the JdbcMapper class.
• When we create the JdbcMapper object by using the JdbcMapperFactory class, we have to the mark the primary key columns of the root and child tables as keys.

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