How to convert data from query with JPA Repository

Question

I have a query that seems to work fine.

public interface GameRepository extends JpaRepository<Game,Integer> {
@Query(value="SELECT Cast(bifnb as varchar) bifnb , count(*) FROM (SELECT  count(fk_game) as nb FROM public.game INNER JOIN score s on game.id_game = s.fk_game WHERE fk_board_game = 2014 GROUP BY fk_game) as bifnb group by bifnb", nativeQuery = true)
List<StatisticDto> nbplayer();

}

But when I try to use it with JPA and type StatisticDTO , I get the following error:

context with path [/api] threw exception [Request processing failed; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [****.statistics.payload.StatisticDto]] with root cause

So I tried with the same query and : List<Object> nbplayer();

It works but I don't know how to work with a List I prefer to use a StatisticDto.

See my answer to this question: https://stackoverflow.com/questions/72704697/problem-with-translation-in-hibernate-when-select-maxcolumn-name — Davide D'Alto, Jun 22 '22 at 10:30
have you looke this solution https://stackoverflow.com/a/52593316/10166394 — YWILLS, Jun 22 '22 at 11:14
@DavideD'Alto I tried your solution, but i don't know why but i can use c[0] et c[1] because the expected type is Array and found Object... I really don't understand how to work with the Object type — Orel M, Jun 22 '22 at 14:39

Davide D'Alto · Answer 1 · 2022-06-22T15:06:43.000

1

Assuming StatisticDTO looks like this:

public class StatisticDTO {
   private String key;
   private Integer value;
   // ... getter/setter
}

This query should work:

@Query(value="SELECT Cast(bifnb as varchar) as \"key\" , count(*) as \"value\" FROM (SELECT  count(fk_game) as nb FROM public.game INNER JOIN score s on game.id_game = s.fk_game WHERE fk_board_game = 2014 GROUP BY fk_game) as bifnb group by bifnb", nativeQuery = true)
List<StatisticDto> nbplayer();

See this article about transforming results to DTO for more information.

I've quoted the aliases because I think key and value are special keywords.

edited Jun 22 '22 at 15:06

answered Jun 22 '22 at 11:04

Davide D'Alto

7,421
2
16
30

I tried but change value and key to avoid the protected name - this time i get: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause org.postgresql.util.PSQLException: ERROR: syntax error at or near "as" – Orel M Jun 22 '22 at 14:33

How to convert data from query with JPA Repository

1 Answers1