Fetching A DTO Projection with a To Many Association

DTO projections provide better performance than entities if you only want to read but not change the retrieved information. They avoid the management overhead of an entity class and enable you to only select the database columns your business code needs.

But as so often, DTO projections also have a downside, and that’s the handling of associations. When you select an entity object, you can easily traverse all of its managed associations. Doing that with a DTO projection requires a custom result mapping.

The JPA specification defines a constructor expression for JPQL queries, which gets executed for each record in the result set and doesn’t support nested constructor calls. That’s more than good enough to map your query result to a flat data structure. But you can’t map a query result that consists of multiple records to a DTO object that contains a list of other complex objects.

This video will show you the best way to map such a query result programmatically and how to use Hibernate’s ResultTransformer to let Hibernate handle the result.

Like my channel? Subscribe!

Links mentioned in the video:

Want to connect with me?

[ebichu]

I believe we need jOOQ due to this case. jOOQ provides JSON function query, so we can directly fetch the nested SQL result as JSON, and it can be mapped to directly DTO with sub-collection via Jackson.

[jamilxt]

Thank you.

By the way, you look great in beard! 😁