Retrieve a DTO with a multi-association

DTO projections offer better performance than entities if you only want to read but not modify the information retrieved. They avoid the overhead of managing an entity class and allow you to select only the database columns that your business code needs.

But as often, DTO projections also have a downside, and that is the management of associations. When you select an entity object, you can easily browse through all of its managed associations. Doing this with a DTO projection requires custom results mapping.

The JPA specification defines a constructor expression for JPQL queries, which is executed for each record in the result set and does not support nested constructor calls. This is more than enough to map your query result to a flat data structure. But you cannot map a query result consisting of multiple records to a DTO object that contains a list of other complex objects.

This article will show you the best way to map such a query result programmatically and how to use hibernate Result transformer to let Hibernate handle the result.

Map the result by programming

The most obvious solution to avoiding the described flaws of JPA constructor expressions is to write your own mapping using Java’s Stream API. This is a good approach if you only need this mapping for one query and avoid a critical trap that I see in many of my consulting projects.

The 2 main benefits of a DTO projection are that you avoid the overhead of managing an entity projection and only select the database columns you need in your business code. If you map the query result yourself, you need to make sure that you retain these benefits. This forces you to use a scalar value projection and not an entity projection.

You can get a scalar value projection as a Object[] or one Tuple interface. The Tuple The interface supports aliasing of its elements and is my favorite representation of a scalar projection. I am using it in the following snippet to get the result as Flux of Tuples.

Stream<Tuple> resultStream = em.createQuery("select as tournament_id, " +
											" as tournament_name, " +
											" as game_id, " +
											"g.round as game_round " +
											"from ChessGame g " +
											"join g.chessTournament t", Tuple.class)
Map<Long, ChessTournamentDto> chessTournamentDtoMap = new LinkedHashMap<>();
List<ChessTournamentDto> chessTournamentDtos = resultStream
		.map(tuple -> {
			ChessTournamentDto chessTournamentDto = chessTournamentDtoMap.computeIfAbsent(tuple.get("tournament_id", Long.class), 
																						  id -> new ChessTournamentDto(tuple.get("tournament_id", Long.class), 
																													   tuple.get("tournament_name", String.class)));
							  .add(new ChessGameDto(tuple.get("game_id", Long.class), 
													tuple.get("game_round", Integer.class)));
			return chessTournamentDto;

The main challenge of the mapping code is to instantiate only 1 Chess TournamentDto object for each tournament_id and add all associates Chess gameDto opposes his Adjust of Games. I store everything Chess TournamentDto objects in the chessTournamentDtoMap and check that Menu before instantiating a new object. I then create a new Chess gameDto object and add it to Adjust of Games.

Once the mapping is complete, I remove the duplicates from the Flux and collect them as List.

Create a custom result transformer

You can implement a similar mapping using Hibernate Result transformer. The specific implementation of the transformer depends on your version of Hibernate:

  • In Hibernate 4 and 5, you need to implement the Result transformer the interface and its transformTuple and transformList methods.
  • In Hibernate 6, the Result transformer the interface has been divided into TupleTransformer and the Result list transformer interface. For this mapping, you must implement the TupleTransformer the interface and its transformTuple method.

The signature of the method and your implementation of the transformTuple are the same for all versions of Hibernate.

Here you can see the implementation of the Result transformer interface for Hibernate 4 and 5. I use the same algorithm in the transformTuple method as in the previous example. The transformList method ignores transformation List of the result and uses the chessTournamentDtoMap to remove duplicates from the query result.

// Implementation for Hibernate 4 and 5
public class ChessTournamentDtoTransformer implements ResultTransformer {

    private static final String TOURNAMENT_ID = "tournament_id";
    private static final String TOURNAMENT_NAME = "tournament_name";
    private static final String GAME_ID = "game_id";
    private static final String GAME_ROUND = "game_round";

    private final Map<Long, ChessTournamentDto> chessTournamentDtoMap = new LinkedHashMap<>();    

    public Object transformTuple(Object[] objects, String[] aliases) {
        List<String> aliasList = Arrays.asList(aliases);
        Map<String, Object> tupleMap =
                                                .collect(Collectors.toMap(a -> a, 
                                                                          a -> objects[aliasList.indexOf(a)]));

        ChessTournamentDto chessTournamentDto = chessTournamentDtoMap.computeIfAbsent((Long)tupleMap.get(TOURNAMENT_ID), 
                                                                                      id -> new ChessTournamentDto((Long)tupleMap.get(TOURNAMENT_ID), 

        chessTournamentDto.getGames().add(new ChessGameDto((Long)tupleMap.get(GAME_ID), 

        return chessTournamentDto;

    public List<ChessTournamentDto> transformList(List list) {
        return new ArrayList<>(chessTournamentDtoMap.values());

After defining your Result transformer, you can assign it to your query. Hibernate will call the transformTuple method for each record in the result set and the transformList method for the whole result.

List<ChessTournamentDto> dtos = em.createQuery("select as tournament_id, " +
												" as tournament_name, " +
												" as game_id, " +
												"g.round as game_round " +
												"from ChessGame g " +
												"join g.chessTournament t")
								  .setResultTransformer(new ChessTournamentDtoTransformer())


You can use the JPA constructor expression and the Hibernate standard Result transformer to map each record in your result set to a DTO object. But map the result to a more complex data structure, for example, one that contains a List other DTO objects, requires custom mapping.

You can select a scalar value projection and map it using Java’s Stream API or implement a Hibernate-specific solution. Result transformer. In either case, your mapping code operates on the records in the result set. Each record includes the values ​​of a parent DTO object and a child DTO object. In your mapping, you must instantiate the two objects and use the parent DTO object to group your result.

Source link