Convert to DTO from join selection

Question

I have these two tables

And I am using this query to get the results

@Query(value = "SELECT bet.bet, match.name, match.week FROM bet INNER JOIN match ON match.id=bet.match_id WHERE match.week = ?1", nativeQuery = true)
List<Object[]> customQuery(Long week);

So far this is the only way I could retrieve the results and actually use them later. To use them I am using this code currently

List<Object[]> bets = gr.customQuery(2l);
for (Object[] object : bets) {
    int bet = (BigInteger) object[0];
    String name = (String) object[1];
    int week = (BigInteger) object[2];

    System.out.println(bet + " " + name + " " + week);
}

But using it that way seems odd to me. Is there a better way to directly map the result to a DTO or something...

No. Jpa/Spring-Data-Jpa doesn't support projections with joins. You might make some progress with EntityGraphs but I don't think so. — K.Nicholas, Dec 30 '20 at 17:44
@K.Nicholas how do you suggest I should solve this? How would you aproach this problem? I think this is very common thing that can happen. — Vasil Emilov, Dec 30 '20 at 17:47
I like to have a constructor in `BetDTO` that takes a `Bet` and builds itself. Check with your team on best practices. — K.Nicholas, Dec 30 '20 at 17:51

score 2 · Accepted Answer · answered Dec 30 '20 at 19:59

There are some options. The most straighforward way would be to define a projection Interface, like:

public interface BetDTO {
    Long getBet();
    String getName();
    Integer getWeek();
}

With that you could change your query return type to:

List<BetDTO> customQuery(Integer week);

The rest would then be using getters.

Convert to DTO from join selection

1 Answers1