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.
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 anOptional
object which contains the information of the requested student. If the requested student isn't found, this method returns an emptyOptional
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:
- 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. - 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.
- Add a
DSLContext
field to the created class and ensure that the Spring container injects the realDSLContext
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() { } }
- 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:
- Select the books of every returned student by using a
MULTISET
value constructor. - Configure an alias which ensures that the found books are set to the
books
field of theStudentDTO
class. - 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:
- Make sure that the query returns only the student whose
id
is equal to theid
given as a method parameter. - Modify the query to return an
Optional
object that contains the found record. - 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.