0

My native query -

interface PodcastRepository: JpaRepository<Podcast, Long> {
    @Query(value = "SELECT new com.krtkush.sample.modules.podcast.models.PodcastDTO" +
            "(p.id, p.author, p.title, p.description c.name, c2.name) " +
            "AS sub_category_name FROM podcasts p " +
            "LEFT JOIN categories c ON p.podcast_category_id = c.category_id " +
            "LEFT JOIN categories c2 ON p.podcast_subcategory_id = c2.category_id " +
            "WHERE p.podcast_owner = :ownerId", nativeQuery = true)
    fun getPodcastsByOwner(@Param("ownerId")owner: Long): List<PodcastDTO>
}

However, when I execute the function I get the following error -

org.postgresql.util.PSQLException: ERROR: syntax error at or near "." Position: 15

position 15 is . after SELECT new com

I'm following this tutorial - https://smarterco.de/spring-data-jpa-query-result-to-dto/ The difference is that I'm using SQL rather than JPQL.

krtkush
  • 1,378
  • 4
  • 23
  • 46
  • 2
    You can't use constructor `new com.krtkush.sample.modules.podcast.models.PodcastDTO` in nativeQuery, you need to use JPQL – Eklavya Sep 07 '20 at 11:45
  • What would be the equivalent JPQL? When I try to remove `nativeQuery=true` I get `QuerySyntaxException: unexpected token: c near line 1, column 107` – krtkush Sep 07 '20 at 11:49
  • 1
    You need to use proper entity and column name in JPQL or use interface to map using nativequery but you need to use alias for column in query to match in interface. Here you found details about 2nd way https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection – Eklavya Sep 07 '20 at 11:51

0 Answers0