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

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:

  • Understand how we can build a query that fetches a nested collection by using a MULTISET value constructor.
  • Can map the query results into returned objects without using extra dependencies.

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

You can use the approach described in this blog post only if you are using jOOQ 3.15 or newer. If you are using an older jOOQ version, you should read this blog post.

The Requirements of Our Example

The database of our example application has two tables:

1. 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.

2. 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 that provides two finder methods which 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 requested student. If the requested student isn't found, this method returns an empty Optional object.

The StudentDTO class contains the information of one student. 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
}

The BookDTO class contains the information of one book. Its source code looks as follows:

public class BookDTO {

    private Long id;
    private String name;

    //Getters and setters are omitted
}

Next, we will implement the StudentRepository class with jOOQ.

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

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. This ensures that the Spring container finds our repository when it scans the classpath for Spring beans.
  2. Ensure that our finder methods are invoked inside a read-only transaction and make sure that a new transaction is created if an existing transaction isn't found.
  3. 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.

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

import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional(readOnly = true)
class StudentRepository {

    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jooq = jooq;
    }
}

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

Implementing the findAll() Method

When we want to write a method that returns all students found from the database, we have to follow these steps:

First, we have to add a package-private findAll() method to the StudentRepository class. This method has no method parameters and it returns a list of StudentDTO objects. After we have added this method to our repository class, its source code looks as follows:

import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
@Transactional(readOnly = true)
public class StudentRepository {

    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jooq = jooq;
    }

    List<StudentDTO> findAll() {
        
    }
}
There are two things I want to point out:

  • The method visibility restrictions of transactional methods were changed in Spring Framework 6.0. Because our sample application uses Spring Framework 6.0.10, we can write package-private methods which are transactional.
  • The @Transactional annotation added to a repository class is used as a last resort. We should add the @Transactional annotations to our public service methods because these methods typically perform operations which should either succeed or fail as one unit.

Second, we have to write a query that selects the information of the returned students (id and name) and maps the returned records into StudentDTO objects by using reflection. After we written this query, the source code of the StudentRepository class looks as follows:

import org.jooq.DSLContext;
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.Tables.STUDENTS;

@Repository
@Transactional(readOnly = true)
public class StudentRepository {

    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jooq = jooq;
    }

    List<StudentDTO> findAll() {
        return jooq.select(
                STUDENTS.ID, 
                STUDENTS.NAME
        )
                .from(STUDENTS)
                .fetchInto(StudentDTO.class);
    }
}

Third, we have to modify our query to fetch the books of every returned student. We can make the required changes by following these steps:

  1. Select the books of every returned student by using a MULTISET value constructor.
  2. Configure an alias which ensures that the found books are set to the books field of the StudentDTO class.
  3. Convert the returned records into BookDTO objects by using an ad-hoc converter and reflection.

After we have made the required changes to our query, the source code of our repository class looks as follows:

import org.jooq.DSLContext;
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.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.Tables.STUDENTS;
import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.selectDistinct;

@Repository
@Transactional(readOnly = true)
public class StudentRepository {

    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jooq = jooq;
    }

    List<StudentDTO> findAll() {
        return jooq.select(
                STUDENTS.ID,
                STUDENTS.NAME,
                multiset(
                        selectDistinct(
                                BOOKS.ID,
                                BOOKS.NAME
                        )
                                .from(BOOKS)
                                .where(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                ).as("books").convertFrom(r -> r.into(BookDTO.class))
        )
                .from(STUDENTS)
                .fetchInto(StudentDTO.class);
    }
}

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 requested student.

Implementing the findById() Method

When we want to implement a repository method that returns the information of the requested student, we have to follow these steps:

First, we have to add a package-private findById() method to the StudentRepository class. This method takes the id of the requested student as a method parameter and it returns an Optional object that contains the found StudentDTO object. After we have added this method to our repository class, its source code looks as follows:

import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Optional;

@Repository
@Transactional(readOnly = true)
public class StudentRepository {

    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jooq = jooq;
    }

    Optional<StudentDTO> findById(Long id) {
        
    }
}

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

  1. Make sure that the query returns only the student whose id is equal to the id given as a method parameter.
  2. Modify the query to return an Optional object that contains the found record.
  3. Transform the found record into a StudentDTO object by using a record mapper and reflection.

After we have written the required query, the source of our repository class looks as follows:

import org.jooq.DSLContext;
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.Tables.BOOKS;
import static net.petrikainulainen.jooqtips.Tables.STUDENTS;
import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.selectDistinct;

@Repository
@Transactional(readOnly = true)
public class StudentRepository {

    private final DSLContext jooq;

    @Autowired
    StudentRepository(DSLContext jooq) {
        this.jooq = jooq;
    }

    Optional<StudentDTO> findById(Long id) {
        return jooq.select(
                STUDENTS.ID, 
                STUDENTS.NAME,
                multiset(
                        selectDistinct(
                                BOOKS.ID,
                                BOOKS.NAME
                        )
                                .from(BOOKS)
                                .where(BOOKS.STUDENT_ID.eq(STUDENTS.ID))
                ).as("books").convertFrom(r -> r.into(BookDTO.class))
        )
                .from(STUDENTS)
                .where(STUDENTS.ID.eq(id))
                .fetchOptional()
                .map(r -> r.into(StudentDTO.class));
    }
}

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

Summary

This blog post has taught us three things:

  • We can select nested collections by using a MULTISET value constructor.
  • Ad-hoc converters are extremely useful when we want to convert the records returned by a MULTISET value constructor into objects which can be added to the selected nested collection.
  • We can combine a record mapper with ad-hoc converters. This allows us to convert records into the objects returned by our repository method. The best part of this technique is that we don't have to use any extra dependencies.

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

0 comments… add one

Leave a Reply