Spring Boot + jOOQ Tutorial - 3 : Fetching One-to-One Relationships

October 19, 2023 Siva

Spring Boot + jOOQ Tutorial - 3 : Fetching One-to-One Relationships

Share this post:

In the previous tutorial, we have learned how to implement basic CRUD Operations using jOOQ. In this tutorial, we will learn how to fetch One-to-One relationships using jOOQ.

Source Code:

You can find the complete source code of this project on GitHub: https://github.com/sivaprasadreddy/spring-boot-jooq-demo

Generally, when we are displaying a list of records, we will display minimal information about the record, and when the user clicks on the record, we will display the complete information about the record.

In our sample application, let’s say we will display the list of users with minimal information like id, name, and email. When the user clicks on the user, we will display the complete information about the user including the user preferences.

So, let’s update our findUserById() method to fetch the user preferences as well.

First, let’s create UserPreferences as a record.

public record UserPreferences(Long id, String theme, String language) {
}

Update the User class to include the UserPreferences.

package com.sivalabs.bookmarks.models;

public record User (
     Long id,
     String name,
     String email,
     String password,
     UserPreferences preferences
) {
    public User(Long id, String name, String email, String password) {
        this(id, name, email, password, null);
    }

    public static User create(Long id, String name, String email, String password) {
        return new User(id, name, email, password, null);
    }
}

In SQL, we can fetch the user preferences using the LEFT OUTER JOIN query as shown below:

SELECT u.id, u.name, u.email, u.password, up.id as "preferences_id", up.theme, up.language
FROM users u LEFT OUTER JOIN user_preferences up ON u.preferences_id = up.id
WHERE u.id = 1;

Let’s implement the above query using jOOQ.

public Optional<User> findUserById(Long id) {
    return dsl
            .select(
                USERS.ID, USERS.NAME, USERS.EMAIL, USERS.PASSWORD,
                USER_PREFERENCES.ID, USER_PREFERENCES.THEME, USER_PREFERENCES.LANGUAGE)
            .from(USERS.leftOuterJoin(USER_PREFERENCES).on(USERS.PREFERENCES_ID.eq(USER_PREFERENCES.ID)))
            .where(USERS.ID.eq(id))
            .fetchOptional(record -> new User(
                    record.get(USERS.ID),
                    record.get(USERS.NAME),
                    record.get(USERS.EMAIL),
                    record.get(USERS.PASSWORD),
                    new UserPreferences(
                            record.get(USER_PREFERENCES.ID),
                            record.get(USER_PREFERENCES.THEME),
                            record.get(USER_PREFERENCES.LANGUAGE)
                    )
            ));
}

Now, let’s update our test case to verify the above method.

package com.sivalabs.bookmarks.repositories;

import com.sivalabs.bookmarks.models.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jooq.JooqTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.context.annotation.Import;
import org.springframework.test.context.jdbc.Sql;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

import static org.assertj.core.api.Assertions.assertThat;

@JooqTest
@Import({UserRepository.class})
@Testcontainers
@Sql("classpath:/test-data.sql")
class UserRepositoryTest {

    @Autowired
    UserRepository userRepository;

    @Container
    @ServiceConnection
    static final PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Test
    void findUserById() {
        Optional<User> userOptional = userRepository.findUserById(1L);
        assertThat(userOptional).isPresent();
        assertThat(userOptional.get().id()).isEqualTo(1L);
        assertThat(userOptional.get().name()).isEqualTo("Admin");
        assertThat(userOptional.get().email()).isEqualTo("admin@gmail.com");
        assertThat(userOptional.get().password()).isEqualTo("admin");
        assertThat(user.preferences().id()).isEqualTo(2L);
        assertThat(user.preferences().theme()).isEqualTo("Dark");
        assertThat(user.preferences().language()).isEqualTo("EN");
    }
}

If you run the test, it should pass.

Using Implicit Join Path

The jOOQ generated code provides a better way to fetch the One-to-One relationships using the implicit join path to the associated table like USERS.userPreferences().ID, USERS.userPreferences().THEME, etc.

Let’s update our findUserById() method to use the implicit join path.

public Optional<User> findUserById(Long id) {
    return dsl
            .select(
                USERS.ID, USERS.NAME, USERS.EMAIL, USERS.PASSWORD,
                USERS.userPreferences().ID, USERS.userPreferences().THEME, USERS.userPreferences().LANGUAGE)
            .from(USERS)
            .where(USERS.ID.eq(id))
            .fetchOptional(record -> new User(
                record.get(USERS.ID),
                record.get(USERS.NAME),
                record.get(USERS.EMAIL),
                record.get(USERS.PASSWORD),
                new UserPreferences(
                    record.get(USER_PREFERENCES.ID),
                    record.get(USER_PREFERENCES.THEME),
                    record.get(USER_PREFERENCES.LANGUAGE)
                )
            ));
}

Using Row Value Expressions

We can also fetch the One-to-One relationships using jOOQ’s row value expression as follows:

public Optional<User> findUserById(Long id) {
    return dsl
            .select(
                USERS.ID, USERS.NAME, USERS.EMAIL, USERS.PASSWORD,
                row(
                    USERS.userPreferences().ID,
                    USERS.userPreferences().THEME,
                    USERS.userPreferences().LANGUAGE
                ).mapping(UserPreferences::new).as("preferences"))
            .from(USERS)
            .where(USERS.ID.eq(id))
            .fetchOptional()
            .map(mapping((userId, name, email, password, preferences) ->
                            new User(userId, name, email, password, preferences)));
}

You might want to fetch the same information for other methods as well such as findUserByEmail(). In that case, you can extract the select clause and result mapping into separate methods as shown in the Part-2 Implementing findUserById() section.

Conclusion

In this tutorial, we have learned how to fetch One-to-One relationships using jOOQ. In the next tutorial, we will learn how to fetch One-to-Many relationships using jOOQ.

Share this post:

SivaLabs