Question:

I'm trying to use a native query with Spring JPA repositories, but keep getting the exception.

This is my Repository with the query, I don't know why I keep getting the exception because I have the @Param value set, I escaped the : character too and I think also I don't have a [1] parameter because my parameter should be :lastUpdate not ?1

@Qualifier("eipoEnergyRepository") public interface EipoEnergyRepository extends JpaRepository<Energy, Integer> { @Query(value = "select max(REALENERGY) as REALENERGY, e.OBJECTID as object, " + "DATE_FORMAT(MAX(LASTUPDATE),'%Y-%m-%d %H\\:%i\\:00') AS LASTUPDATE " + "from ENERGY e WHERE str_to_date(LASTUPDATE, '%Y-%m-%d %H\\:%i\\:%s' ) > str_to_date(':lastUpdate', '%Y-%m-%d %H\\:%i\\:%s' ) " + "group by ROUND(UNIX_TIMESTAMP(LASTUPDATE)/600), e.object", nativeQuery=true) List<Energy> findByLastUpdate(@Param("lastUpdate") String lastUpdate); }

This is related to <a href="https://jira.spring.io/browse/DATAJPA-1235" rel="nofollow">https://jira.spring.io/browse/DATAJPA-1235</a> basically handling of colons in String literals is broken :-(

A workaround would be to get rid of colons in queries which might be interpreted as bind parameters.

In your case options might be:

a) using <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm" rel="nofollow">trunc</a> if you are using Oracle for reducing the precision of dates for comparison. That's what you are trying to do in the where clause, right? Other databases might have similar functions.

b) using a different character and replacing it with a colon on the java side.