@Query not recognizing parameters if they are inside single quote in Spring Framework

Question

I have a big problem with Spring Data in @Query.

I have the following query :

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;

this query works in postgres without any problem now in spring to use it :

  @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>

This query doesn't work at all, the problem is that spring can't recognize the parather beginDate (?1) & endDate (?2). I tried a lot of solution from stackoverflow like solution and solution 2 but I can't get rid of this problem. I don't know if it's intented or it's a bug in spring.

Have you tried without adding quotes around '?1' and '?2' ? Did it work that way or show some different error? — Kuldeep Singh, Jun 05 '18 at 07:33
@KuldeepSidhu yes I did but single quote are necessary; this is the error I get : `org.postgresql.util.PSQLException: ERROR: syntax error at or near "created_at" Position: 132` — Noah13, Jun 05 '18 at 07:48
Did you try to use the query without any casting ('::date' etc)? And why do you use it?? — Cepr0, Jun 05 '18 at 08:10
@Cepr0 that's postgres way of casting. I'm using this query because it's the solution for my problem. — Noah13, Jun 05 '18 at 10:33
I'm also using postgres and use casting only for jsonb fields. Then did you try to exclude casting from this query? — Cepr0, Jun 05 '18 at 10:36
I need to show only date not timestamp that's why I'm casting to date. the issue here is spring can't really detect paramters when they are inside single quote they aren't replaced by variable's values and are executed like a normal text '?1' — Noah13, Jun 05 '18 at 10:38
@Noah13 did you find a solution for this , I am facing the same issue — Manas Pratim Chamuah, Nov 29 '19 at 09:59

score 2 · Answer 1 · answered Jun 05 '18 at 19:56

Try to simply use the classic SQL cast function:

@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>

where AbsenceCount is a projection like this (java):

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

still ?1 and ?2 are not inside single quote this query won't work. — Noah13, Jun 06 '18 at 18:19

@Query not recognizing parameters if they are inside single quote in Spring Framework

1 Answers1