79742

Spring JPA - Parameter with that position [1] did not exist

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

Answer1:

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.

Recommend

  • Transactional and Stream in Spring
  • retain HTML tags after xslt
  • Spring Data JPA JPQL queries on parent interface
  • Format time labels in charts_flutter time series chart
  • What rules govern using @MOdify and @Query in JPA Repository?
  • Update label's text when pressing a button in Kivy for Python
  • Retrieve ACL of ParseObject given objectId
  • Using imported docs from MongoDB in DerbyJS
  • Parse cloud code ascending / limit won't work in query in promise
  • Dynamic search by criteria
  • iOS and CoreBluetooth Low Energy Required?
  • Stored function with temporary table in postgresql
  • BSON::ObjectId vs Mongo::ObjectID
  • IE memory leak and eval with jQuery
  • How to remove Object from array using mongoose
  • FindAndModify, return array of Objects
  • how to get username into sql trigger when multiple users signed on from asp membership
  • RxJava debounce by arbitrary value
  • Z3: Convert between FP and BitVector?
  • D3 get axis values on zoom event
  • C: Incompatible pointer type initializing
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Counter field in MS Access, how to generate?
  • Convert array of 8 bytes to signed long in C++
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • SQL merge duplicate rows and join values that are different
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • How to get Windows thread pool to call class member function?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal