CODEWITHSUNDEEP
springpostgresqlspring-bootkotlinspring-data
Noah13
Noah13

Reputation: 347

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

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.

Upvotes: 2

Views: 702

Answers (1)

Cepr0
Cepr0

Reputation: 30419

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();
}

Upvotes: 2

Related Questions