CODEWITHSUNDEEP
javapostgresqlhibernate
Ilia Tapia
Ilia Tapia

Reputation: 649

How to use query from postgres properly in a Java JPA repository?

I have this query :

SELECT * FROM public.app_catalog as a , (SELECT  app_id , COUNT(*) as count 
FROM public.statistics_log 
where logged_at between  '2019-08-20 12:22:40.186'   and '2019-08-22 12:22:40.186'           
GROUP BY app_id
LIMIT 10) AS App_id 
where App_id.app_id = a.app_catalog_id

And I want to write this in a Java repository

I tried like this:

/**
 * @param startDate .
  * @param enddate .
 * @return AppCatalog
 */
@Query("SELECT app FROM "
        + "(SELECT  b.app_id , COUNT(*) as count"
        + "FROM public.statistics_log as b inner join public.app_catalog as app on  b.app_id = app.app_catalog_id"
        + "where b.logged_at between :startDate and :endDate "
        + "GROUP BY b.app_id"
        + "LIMIT 10)app")
List<AppCatalog> findingTrendingAppsInCatalog(@Param("startDate") LocalDate startDate,
                                              @Param("endDate") LocalDateTime enddate);

The error I am getting:

Validation failed for query for method public abstract java.util.List com.xitee.cbc.repository.AppCatalogRepository.findingTrendingAppsInCatalog(java.time.LocalDate,java.time.LocalDateTime)!

But it seems the syntax is wrong because it does not compile.

Upvotes: 0

Views: 86

Answers (1)

forpas
forpas

Reputation: 164064

You don't need the outer select and you need to leave spaces between the strings that you concatenate:

@Query("SELECT  b.app_id, COUNT(*) as count "
     + "FROM public.statistics_log as b inner join public.app_catalog as app "           
     + "ON b.app_id = app.app_catalog_id "
     + "WHERE b.logged_at BETWEEN :startDate and :endDate "
     + "GROUP BY b.app_id "
     + "LIMIT 10")

Upvotes: 2

Related Questions