One of issues when you work with relational databases in Java is that they are unable to translate object relationships (such as composition) due to the tabular nature of data sources. That means that as developers we usually tend to have an intermediate layer, which is responsible to abstract the data source’s data organization. This is called ORM or object-relational mapping. In Spring ecosystem the de facto standard is Hibernate, however it is not [yet] available for new non-blocking Spring R2DBC API.

Frankly speaking, you don’t need Hibernate to do object mapping, as R2DBC is easy to use and you can do it yourself. And moreover it is a great pleasure, especially because we – Java devs – tend to rely on tools that are not state of the art of software design. This post focuses on a problem of fetching composed entites from Postgresql (using INNER JOIN) and mapping it with Data Mapper pattern (as defined in P of EAA) with custom Spring ReactiveCrudRepository extension.

Do you want to increase your Java collections skills?

This topic is really huge, and a single post is not enough to cover all. That is why I wrote this Practical Guide. Everything you need in the one book. Do you want to become Java ninja?

Problem

In the object-oriented programming we work with objects, that possess rich relationships between each other, based on aggregation, association or composition. Imagine, that you work on task management app, where each task entity holds a reference to project entity. Or, another case – job app, where job entity points the corresponding employer object.

However, in relational databases, despite to their name, data is organized in tabular way. That means we need to have a intermediate layer between database and business logic. Such mechanism is called object-relational mapping (or ORM) and it allows to access data entities in a way independent from how they are stored in data sources. In Java de facto choice is Hibernate, and as Spring developers we use it a lot with relational DBs, such as MySQL, Postgre etc.

But, when you use non-blocking Webflux APIs and R2DBC to work with relational databases, you don’t have ORM, because Hibernate is not supported by R2DBC. Yet, maybe. And I think it is good – as Java developers we love to talk about software design, but we depend on tools, that are not best examples of software architectural principles. This post is about dealing with composition in entities without Hibernate in Spring R2DBC and Postgresql.

Solution

Consider a following example: we build task management application and we have Task and Project entites. Each are stored in separate tables, but are referenced using project_id key. When we want to retrieve a list of tasks, we want to have information about corresponding projects, so we could show it in client apps. Yet we can have two separate repos and then combine data it is highly inefficient idea. The better approach is to write a custom TaskRepository that return composed objects.

Step 1. Define a custom repository

By default, ReactiveCrudRepository is an entry point, that offers a basic CRUD functionality, but it is limited to that. In order to provide custom queries we need to extend it with a custom repository. For that we first create a new interface that defines a contract for custom repository, and then extend the entry repository with it:

public interface CustomTaskRepository {

    Flux<Task> findAllTasks (UUID userId);

    Flux<Task> findTasksForDay (UUID userId, LocalDate day);

}

The next step is to extend core TaskRepository with CustomTaskRepository like it is shown below:

public interface TaskRepository extends 
    ReactiveCrudRepository<Task, UUID>,
    CustomTaskRepository {}

Step 2. Implement the contract

Now we can implement the aforesaid interface. Note, that implementations should have Impl endings due to Spring DI rules. In that component we need to inject DatabaseClient that is non-blocking client to handle database operations. In Spring Boot things are pre-configured, so in that case you just need to define a dependency and use constructor-based DI to make Spring inject it:

public class CustomTaskRepositoryImpl implements CustomTaskRepository {

    private DatabaseClient client;

    public CustomTaskRepositoryImpl(DatabaseClient client) {
        this.client = client;
    }

    public Flux<Task> findAllTasks (UUID userId) {
        return null;
    }

    public Flux<Task> findTasksForDay (UUID userId, LocalDate day) {
        return null;
    }
}

Step 3. Prepare SQL queries

Fetching composed entites means that we need to use JOIN operations. Postgresql has 6 types of join operations, but this is out of scope of this post. Maybe in the future I will add Postgresql as a topic of my blog, but not now 🙂 Here we use INNER JOIN operation, that returns rows that match the given condition in both tables.

In our example we have Task and Project entites connected with project_id. Take a look on the code snippet below:

public Flux<Task> findAllTasks (UUID userId) {
    String query = "SELECT task_id, task_content, is_completed, user_id, task_date, task_project, project_id, project_name "
                    + " FROM tasks INNER JOIN projects ON task_project = project_id WHERE user_id = :userId";
    return null;
}

Step 4. Bind params and execute query

Likewise we do it in plain JDBC, we first prepare a query and then execute it. In R2DBC we use DatabaseClient.execute() method for this. We also may bind some variables, like userId. This is done using bind() method, which accept two arguments: key (a name of variable in query) and a value.

public Flux<Task> findAllTasks (UUID userId) {
    String query = "SELECT task_id, task_content, is_completed, user_id, task_date, task_project, project_id, project_name "
                    + " FROM tasks INNER JOIN projects ON task_project = project_id WHERE user_id = :userId";
    Flux<Task> result = client.execute(query)
                        .bind("userId", userId)
                        ///...
    return null;
}

Step 5. Use mapper to work with results

This is the cumberstone, as that is a reason why we use ORM frameworks. We need to map raw results to Java objects. For that (as well to promote reusability) we create a mapper. This design pattern, defined by Martin Fowler is used to move data between objects and a database while keeping them independent of each other and the mapper itself. The idea is displayed below:

Graph 1. Data mapper design pattern

In R2DC reactive client mapping operation is performed by map() method. It accepts a normal BiFunction that maps raw row results to corresponding Java model. We can implement it using aforesaid functional interface like that:

public class TaskMapper implements BiFunction<Row, Object, Job> {

    @Override
    public Task apply(Row row, Object o) {
        UUID taskId = row.get("task_id", UUID.class);
        String content = row.get("task_content", String.class);
        Boolean completed = row.get("is_completed", Boolean.class);
        LocalDate createdAt = row.get("task_date", LocalDate.class);

        UUID projectId = row.get("project_id", UUID.class);
        String projectName = row.get("project_name", String.class);

        Project project = new Project(projectId, projectName);
        Task task = new Task(taskId, content, complted, createdAt, project);
        return task;
    }
}

Next, we can add this component to our custom repository:

public Flux<Task> findAllTasks (UUID userId) {
    String query = "SELECT task_id, task_content, is_completed, user_id, task_date, task_project, project_id, project_name "
                    + " FROM tasks INNER JOIN projects ON task_project = project_id WHERE tasks.user_id = :userId";

    TaskMapper mapper = new TaskMapper();

    Flux<Task> result = client.execute(query)
                        .bind("userId", userId)
                        .map(mapper:apply)
                        //...
    return null;
}

Step 6. Consume data

The final step is to call one of terminal operations to consume data pipeline. DatabaseClient has three operations to work with queries:

  • all() = returns all rows of the result
  • first() = returns the first row of the entire result
  • one() = returns exactly one result and fails if the result contains more rows

In our example, we need all entites that satisfy to the query, so we use all() method, like it is shown below:

public Flux<Task> findAllTasks (UUID userId) {
    String query = "SELECT task_id, task_content, is_completed, user_id, task_date, task_project, project_id, project_name "
                    + " FROM tasks INNER JOIN projects ON task_project = project_id WHERE tasks.user_id = :userId";

    TaskMapper mapper = new TaskMapper();

    Flux<Task> result = client.execute(query)
                        .bind("userId", userId)
                        .map(mapper:apply)
                        .all();
    return result;
}

As you can note, this is not a rocket science to use R2DBC with complex composed objects without a need of ORM framework, like Hibernate. R2DBC provides a fluent API and is easy to use and to abstract database operations, so you can impelement required persistance layer logic yourself.

If you have questions regarding this post, don’t hesitate to drop a comment below or contact me. Have a nice day!

References

  • Mark Paluch Reactive programming with SQL databases (2019) Jaxenter access here
  • Martin Fowler P of EAA: Data Mapper access here
  • Piotr Mińkowski Introduction to reactive APIS with Postgres, R2DBC, Spring Data JDBC and Spring Webflux (2018) acces here