Projections/DTOs in Spring Data R2DBC

When dealing with Spring Data JPA or Spring Data R2DBC, we all know that everything revolves around entities. You usually create a repository, and in most cases, it either extends the JpaRepository<T, ID>  for Spring Data JPA or the R2dbcRepository<T, ID> for Spring Data R2DBC, and then you’re ready to talk to the database. With the repository in place, things are pretty straightforward: you either use the standard already-provided interface methods, you write derived-query methods, or you can use the @Query annotation to write custom stuff. Up until here, everything’s nice. As I said, everything revolves around entities (with minor exceptions, you can get a primitive return type with a custom query like an Int or String), so as a response, you’ll always get an Entity/List<Entity> for JPA and Mono<Entity>/Flux<Entity> for R2DBC. However, there are certain scenarios when you either don’t want to fetch the entire entity because some columns are redundant/not-needed or, even more, you need to construct a POJO from something completely different from your entity, yet still somehow related. Think about a group by, some counts, or some mapping at the database level, and by default you can’t do that – your repos are bound to entities.

That is when projections and DTOs come into play. For Spring Data JPA there is already a fair amount of documentation/info around (official documentation, Vlad Mihalcea, Thorben Janssen, and so on). This is not the case when it comes to Spring Data R2DBC (we still have amazing documentation here).

So I decided to write a little something about projections and DTOs in Spring Data R2DBC.

Before getting to the code, let’s examine the app. I have a review service that is centered around a single entity. To review, basically, it exposes a CRUD API for it. The app is written in Kotlin with Spring Boot, Spring Data R2DBC, and Spring for GraphQL. 

Here’s the entity:

@Table("reviews")
data class Review(
    @Id
    var id: Int? = null,
    var text: String,
    var author: String,
    @Column("created_at")
    @CreatedDate
    var createdAt: LocalDateTime? = null,
    @LastModifiedDate
    @Column("last_modified_at")
    var lastModifiedAt: LocalDateTime? = null,
    @Column("course_id")
    var courseId: Int? = null
)

And here is its repository:

@Repository
interface ReviewRepository : R2dbcRepository<Review, Int> {
    @Query("select * from reviews r where date(r.created_at) = :date")
    fun findAllByCreatedAt(date: LocalDate): Flux<Review>
    fun findAllByAuthor(author: String): Flux<Review>
    fun findAllByCreatedAtBetween(startDateTime: LocalDateTime, endDateTime: LocalDateTime): Flux<Review>
}

Also, I have written some tests for us, so we can see the result of the new things that we are going to write right away:

@RepositoryIntegrationTest
class ReviewRepositoryIntegrationTest : AbstractTestcontainersIntegrationTest() {

    @Autowired
    lateinit var reviewRepository: ReviewRepository

    @Test
    @RunSql(["/data/reviews.sql"])
    fun findAllByAuthor() {
        StepVerifier.create(reviewRepository.findAllByAuthor("Anonymous"))
            .expectNextCount(3)
            .verifyComplete()
    }

    @Test
    @RunSql(["/data/reviews.sql"])
    fun findAllByCreatedAt() {
        StepVerifier.create(reviewRepository.findAllByCreatedAt(LocalDate.parse("2022-11-14")))
            .expectNextCount(1)
            .verifyComplete()
    }

    @Test
    @RunSql(["/data/reviews.sql"])
    fun findAllByCreatedAtBetween() {
        StepVerifier.create(
            reviewRepository.findAllByCreatedAtBetween(
                LocalDateTime.parse("2022-11-14T00:08:54.266024"),
                LocalDateTime.parse("2022-11-17T00:08:56.902252")
            )
        )
            .expectNextCount(4)
            .verifyComplete()
    }
}

For the integration test, I am using testcontainers library. If you are curious to find out more about these kinds of tests, make sure to check my article "Testcontainers With Kotlin and Spring Data R2DBC."

Projections in Action

Now let’s say that for a specific use case, I want to fetch only text of the reviews by some author. This is a perfect example for a projection. As previously mentioned, spring data query methods return instances of the aggregate root (entity) managed by the repository. Whenever we want to filter out some of the columns from the fetched entities, we can model a projection based on certain required attributes of that entity. Let’s create a class-based projection for this use case:

data class TextProjection(val text: String)

And here is how we can use it:

@Query("select text from reviews where author = :author")
fun findAllTextProjectionsByAuthor(author: String): Flux<TextProjection>

Alright, let’s test it out:

@Test
@RunSql(["/data/reviews.sql"])
fun findAllTextProjectionsByAuthor() {
    StepVerifier.create(reviewRepository.findAllTextProjectionsByAuthor("Sponge Bob"))
        .expectNextMatches { it.text == "Good, loved it!"}
        .verifyComplete()
}

Nice! Let’s take a look at another example: what if I want to fetch only the courseId and concatenated text with author (“text – author”)? For this use case, I am going to use an interface-based projection.

interface AuthoredTextProjection {
    fun getCourseId(): Int?
    @Value("#{target.text + ' - ' + target.author}")
    fun getAuthoredText(): String?
}

Note the usage of @Value annotation: accessor methods in interface-based projections can be used to compute new values based on the target entity. In our case, that is the concatenation of review’s text and author via dash. The root entity that is backing the interface projection is available in @Value’s SpEL expression via target. 

Using @Value on custom interface methods to generate new values creates an open interface-based projection, while having only method names exactly the same as the getters of root entity attributes creates a closed interface-based projection.

Let’s use our new open interface-based projection:

@Query("select course_id, \"text\", author from reviews")
fun findAllAuthoredTextProjections(): Flux<AuthoredTextProjection>

And here’s the test:

@Test
@RunSql(["/data/reviews.sql"])
fun findAllAuthoredTextProjections() {
    StepVerifier.create(reviewRepository.findAllAuthoredTextProjections())
        .expectNextMatches { it.getAuthoredText() == "Amazing, loved it! - Anonymous" && it.getCourseId() == 3 }
        .expectNextMatches { it.getAuthoredText() == "Great, loved it! - Anonymous" && it.getCourseId() == 3 }
        .expectNextMatches { it.getAuthoredText() == "Good, loved it! - Sponge Bob" && it.getCourseId() == 3 }
        .expectNextMatches { it.getAuthoredText() == "Nice, loved it! - Anonymous" && it.getCourseId() == 3 }
        .verifyComplete()
}

Great - it's green. With projections, everything’s clear, but let’s consider another use case. What if I want to fetch a grouping of the number of reviews per author for all my records? Now the result of such a query will have a set of attributes that is somewhat different from the target entity’s fields. 

Now, that’s obvious: projections can’t solve such a problem, since they're mostly based on the target's attributes.

DTOs Mapping in Action

We can try to map our result set using a DTO that’ll look something like this:

data class ReviewsCountPerAuthorView(val author: String?, val numberOfReviews: Int?)

And here’s the new repository method: nothing complicated – a count of review ids and a group by author.

@Query("select author, count(id) as \"numberOfReviews\" from reviews group by author")
fun countReviewsPerAuthor(): Flux<ReviewsCountPerAuthorView>

Here’s the test:

@Test
@RunSql(["/data/reviews.sql"])
fun countReviewsPerAuthor() {
    StepVerifier.create(reviewRepository.countReviewsPerAuthor())
        .expectNextMatches { it == ReviewsCountPerAuthorView("Anonymous", 3) }
        .expectNextMatches { it == ReviewsCountPerAuthorView("Sponge Bob", 1) }
        .verifyComplete()
}

If we try to run it, the test will fail with:

expectation "expectNextMatches" failed (predicate failed on value: ReviewsCountPerAuthorView(author=Anonymous, numberOfReviews=null))

Why is that? The strange thing is that only the author field was correctly mapped, and the numberOfReviews is null. Actually, it is not that strange: as previously mentioned, by default, every Spring Data query method will return instance(s) of the repository’s defined root aggregate, and it’ll try to map the result set to the root entity. As mentioned before, projections are backed by a target, which is the root entity. So in this case, our custom DTO was treated as a potential class-based projection with a matching field named author, and that is why only the author field got its value. 

Okay, we understand the problem, but how do we teach Spring Data R2DBC that this is not a projection, but a custom DTO with custom values that need to be wired in? We define a custom converter using @ReadingConverter, which will map the row’s returned values into our custom DTO:

@ReadingConverter
class ReviewsCountPerAuthorViewReadConverter : Converter<Row, ReviewsCountPerAuthorView> {
    override fun convert(source: Row): ReviewsCountPerAuthorView {
        return ReviewsCountPerAuthorView(source.get("author", String::class.java), source.get("numberOfReviews", String::class.java)?.toInt())
    }
}

Now the little thing that remains to be done is to register it by declaring the following @Bean: 

@Bean
fun r2dbcCustomConversions(connectionFactory: ConnectionFactory, ): R2dbcCustomConversions? {
    val dialect = DialectResolver.getDialect(connectionFactory)
    return R2dbcCustomConversions.of(dialect, listOf(ReviewRepository.ReviewsCountPerAuthorViewReadConverter()))
}

Now if we run our test, it is green, and we successfully mapped our result set to a custom DTO. Now as you might’ve imagined, if there is a @ReadingConverter, then there’s a @WritingConverter, too, right? You’re correct. SELECT data can be extracted from R2DBC’s Row into a custom POJO/entity via @ReadingConverter. INSERT/UPDATE data can be mapped to an OutboundRow via @WritingConverter. Usually, you want to use these converters for writing/reading embedded objects of an entity to/from the actual table’s rows.

Conclusion

We’ve seen how projections can make our life a little easier when it comes to filtering out data from an entity. We analyzed how converters (especially the @ReadingConverter) can aid in mapping custom query data to a custom DTO. The code is here.

Happy coding!