Best way to map query result to DTO

Question

I want to make a complex query and map the result into a DTO. The DTO is below:

@Value(staticConstructor = "of")
public class TotalsDto {
    LocalDate date;
    long totals;
    long totalPerCategory;
    int categoryId;
    String categoryName;
}

My repository interface is extending from JpaRepository. This is throwing an IllegalArgumentException: Not a managed type, because TotalsDto is not an Entity itself.

The repository is:

@Repository
public interface TotalsRepository extends JpaRepository<TotalsDto, Integer> { 

    @Query(value = "SELECT ...", nativeQuery = true)
    List<TotalsDto> getTotals(params...);
}

The query is getting data from other entities to build the DTO. Any way to map every column to DTO? I tried to map it with the query below but it still getting Not a managed class.

SELECT my.package.TotalsDto.of(column1, subqueryResult1, subqueryResult2...)

Answer 1

1) Make TotalsDto an interface

2) Create getters:

public interface TotalsDto{

    long getTotals();
    int getCategoryId();
    ...

}

Spring Data Jpa will then automatically create / fill you result object.

More on the subject here

Answer 2

The first type argument to Repository (or JpaRepository) must be a managed entity. Therefore, JpaRepository<TotalsDto, Integer> is invalid, as TotalsDto is not a managed entity. Change TotalsDto to a projection interface like @MaciejKowalski has suggested, and then pass a JPA entity to Repository as the first type argument, say, Repository<Order, Integer>.