Projections/DTOs in Spring Data R2DBC
In this article, learn about projections in Spring Data R2DBC and see how to map custom queries to DTOs in Spring Data R2DBC.
Join the DZone community and get the full member experience.
Join For FreeWhen 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!
Opinions expressed by DZone contributors are their own.
Comments