using WITH clause in @Query JPA Postgresql

Question

I am trying to use this query from here - http://technoir.blog/blog/post/timeseries_tips_for_postgresql:

with filled_dates as (
  select day, 0 as blank_count from
    generate_series('2014-01-01 00:00'::timestamptz, current_date::timestamptz, '1 day') 
      as day
),
signup_counts as (
  select date_trunc('day', created_at) as day, count(*) as signups
    from users
  group by date_trunc('day', created_at)
)
select filled_dates.day, 
       coalesce(signup_counts.signups, filled_dates.blank_count) as signups
  from filled_dates
    left outer join signup_counts on signup_counts.day = filled_dates.day
  order by filled_dates.day

When I put this in @Query("WITH filled_dates as ( .... "), it throws this error -

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: WITH near line 1, column 1 [WITH filled_dates as

How can I use WITH clause in manual JPA queries?

you would consult documentation about what is JPQL and realise that SQL != JPQL, and Spring has some "nativeQuery" flag. But then you've already been through docs, no? — user8558216
– user8558216, Commented Mar 15, 2018 at 8:12

Vasan · Accepted Answer · 2018-03-14 23:10:46Z

8

Use nativeQuery=true in the annotation to switch to plain SQL. Otherwise it expects JPQL queries.

@Query(value = "WITH filled_dates AS ...", nativeQuery=true)

Also, make sure you've formatted it correctly for Java in terms of spaces, quotes etc.

If you want to use parameters, use positional instead of named parameters.

For example (a bit silly):

@Query(value="select * from emp where name=?1 and dept=?2", nativeQuery=true)
public Collection<Employee> findEmployeesByNameAndDept(String name, String dept);

Adding such a complex query to Java code using JPA seems like a poor idea though. Maybe you should use a stored procedure or function or somesuch.

edited Mar 14, 2018 at 23:10

answered Mar 14, 2018 at 22:27

Vasan

4,9564 gold badges22 silver badges41 bronze badges

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

6 Comments

nirvair Over a year ago

I am yet to understand how to use a stored procedure for this kind of queries. Can you help me give a starting point?

Vasan Over a year ago

@nirvair Sorry, but I'm not an expert in that area either. What I'd suggest is to open a new question with that as the focus. SO might not be the best place for that though. Post your current code (with the huge query in Java) at codereview.stackexchange.com and ask how it can be made better. Meanwhile, if adding nativeQuery at least resolved the error, you could accept this answer so it may help others!

nirvair Over a year ago

one more query. How can I add parameters in this query? Would it be the same way as it was in JPQL?

Vasan Over a year ago

@nirvair Use positional parameters like ?1, ?2 etc and maintain the same order in arguments.

nirvair Over a year ago

Can you give an example?

|

Collectives™ on Stack Overflow

using WITH clause in @Query JPA Postgresql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related