在JPA Postgresql中使用WITH子句的@Query

Question

我正在尝试使用这里的查询 - 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

当我将这段代码放入@Query("WITH filled_dates as ( .... ")中时，它会抛出以下错误 -

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

我怎样在手动的JPA查询中使用WITH子句？

- nirvair

---

你会查阅文档了解什么是JPQL，并意识到SQL！= JPQL，Spring有一些“nativeQuery”标志。但是你已经浏览过文档了，不是吗？ - user8558216

Answer 1

在注解中使用nativeQuery=true可切换到纯SQL模式，否则它会期望JPQL查询。

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

此外，请确保在Java中正确格式化了空格、引号等内容。

如果您想要使用参数，请使用位置参数而不是命名参数。

例如（有点傻）：

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

然而，将如此复杂的查询添加到使用JPA的Java代码中似乎不是一个好主意。也许你应该使用存储过程、函数或类似的工具。

- Vasan

---

我还不太了解如何使用存储过程来处理这种类型的查询。你能帮我提供一个起点吗？ - nirvair

@nirvair 抱歉，我也不是那个领域的专家。我的建议是开一个新问题，以此为重点。虽然SO可能不是最好的地方。在codereview.stackexchange.com上发布您当前的代码（包括Java中的巨大查询），并询问如何改进它。同时，如果添加nativeQuery至少解决了错误，您可以接受这个答案，以便帮助其他人！ - Vasan

还有一个问题。我该如何在这个查询中添加参数？与JPQL中的方式相同吗？ - nirvair

@nirvair 使用位置参数，如 ?1、?2 等，并保持参数的相同顺序。 - Vasan

你能举个例子吗？ - nirvair

@nirvair 我编辑了我的回答，请现在检查。 - Vasan