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

Question

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.

It might help to include the error messages you get in your question. — GhostCat
– GhostCat, Commented Aug 22, 2019 at 11:57

forpas · Accepted Answer · 2019-08-22 12:00:25Z

2

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")

answered Aug 22, 2019 at 12:00

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ilia Tapia Over a year ago

Does this select all the column in app_catalog

forpas Over a year ago

It selects: app_id and count just like the query you wrote, because select * from (...) selected 2 columns.

Ilia Tapia Over a year ago

I want to select all the column in app catalog, is that possible

forpas Over a year ago

This answer is about the syntax error you get. If you solved this and you have another requirement post a new question describing what you want.

forpas Over a year ago

Don't ask new questions in your question because you invalidate the existing answer(s).

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related