by BehindJava

Fetching a DTO With a To-Many Association

Home » java » Fetching a DTO With a To-Many Association

This tutorial 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.

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.

Mapping the Result Programmatically

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

The 2 main benefits of a DTO projection are that you avoid the management overhead of 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 ensure that you keep these benefits. That requires you to use a scalar value projection and not an entity projection.

You can retrieve a scalar value projection as an Object[] or a Tuple interface. The Tuple interface supports alias-based access to its elements and is my preferred representation of a scalar projection. I use it in the following code snippet to get the result as a Stream of Tuples.

Stream<Tuple> resultStream = em.createQuery("select t.id as tournament_id, " +
                                            "t.name as tournament_name, " +
                                            "g.id as game_id, " +
                                            "g.round as game_round " +
                                            "from ChessGame g " +
                                            "join g.chessTournament t", Tuple.class)
                                            .getResultStream();
         
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)));
            chessTournamentDto.getGames()
                              .add(new ChessGameDto(tuple.get("game_id", Long.class), 
                                                    tuple.get("game_round", Integer.class)));
            return chessTournamentDto;
        })
        .distinct()
        .collect(Collectors.toList());

The main challenge of the mapping code is to only instantiate 1 ChessTournamentDto object for each tournament_id and add all associated ChessGameDto objects to its Set of games. I store all ChessTournamentDto objects in the chessTournamentDtoMap and check that Map before instantiating a new object. I then create a new ChessGameDto object and add it to the Set of games.

After the mapping is done, I remove the duplicates from the Stream and collect them as a List.

Create a Custom ResultTransformer

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

  • In Hibernate 4 and 5, you need to implement the ResultTransformer interface and its transformTuple and transformList methods.
  • In Hibernate 6, the ResultTranformer interface got split into the TupleTransformer and the ResultListTransformer interface. For this mapping, you need to implement the TupleTransformer interface and its transformTuple method.

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

Here you can see the implementation of the ResultTransformer interface for Hibernate 4 and 5. I use the same algorithm in the transformTuple method as in the previous example. The transformList method ignores the transformed List of the result and uses the chessTournamentDtoMap to remove the 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<>();    
 
    @Override
    public Object transformTuple(Object[] objects, String[] aliases) {
        List<String> aliasList = Arrays.asList(aliases);
        Map<String, Object> tupleMap = aliasList.stream()
                                                .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), 
                                                                                                                   (String)tupleMap.get(TOURNAMENT_NAME)));
 
        chessTournamentDto.getGames().add(new ChessGameDto((Long)tupleMap.get(GAME_ID), 
                                                           (Integer)tupleMap.get(GAME_ROUND)));
 
        return chessTournamentDto;
    }
 
    @Override
    public List<ChessTournamentDto> transformList(List list) {
        return new ArrayList<>(chessTournamentDtoMap.values());
    }
}

After you define your ResultTransformer, 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 entire result.

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

Conclusion

You can use JPA’s constructor expression and Hibernate’s standard ResultTransformer to map each record of your result set to a DTO object. But mapping the result to a more complex data structure, e.g., one that contains a List of other DTO objects, requires a custom mapping.

You can select a scalar value projection and map it using Java’s Stream API or implement a Hibernate-specific ResultTransformer. In both cases, your mapping code operates on the result set records. Each record includes the values of a parent DTO and a child DTO object. Within your mapping, you need to instantiate both objects and use the parent DTO object to group your result.