spring - @Query无法识别参数是否在Spring Framework中的单引号内

Question

我在@Query中使用Spring Data有一个大问题。

我有以下查询:

SELECT created_at::DATE "date",
count(*)
FROM
absence
WHERE
created_at::DATE between '2018-05-27' AND  '2018-05-31'
GROUP BY created_at::DATE;

这个查询现在可以在postgres中正常使用了，现在可以在 Spring 使用:

  @Query("SELECT created_at\\:\\:DATE \"date\",count(*) FROM absence " +
            "WHERE created_at\\:\\:DATE between '?1' AND '?2' " +
            "GROUP created_at\\:\\:DATE", nativeQuery = true)
    fun getAbsenceCount(beginDate: String,endDate: String): List<AbsenceCount>

该查询根本不起作用，问题是spring无法识别parather beginDate(？1)和endDate(？2)。
我尝试了很多来自stackoverflow的解决方案，例如 solution和 solution 2，但我无法摆脱这个问题。
我不知道这是故意的还是 Spring 的错误。

Answer 1

尝试简单地使用经典的SQL cast函数:

@Query(value = "" + 
    "select " +
    "  cast(a.created_at as date) as createdAt, " +
    "  count(*) as quantity " + 
    "from " + 
    "  absence a " + 
    "where " + 
    "  cast(a.created_at as date) between ?1 and ?2 " + 
    "group " + 
    "  cast(a.created_at as date)" + 
    "", nativeQuery = true)
fun getAbsenceCount(beginDate: String, endDate: String): List<AbsenceCount>

其中 AbsenceCount是这样的投影(java):

public interface AbsenceCount {
    LocalDate getCreatedAt();
    Long getQuantity();
}