spring JPA query with spel expression for random

Question

I am trying to generate a random number so I can do sampling in a data set in a JPA query. I have read this is totally possible everywhere but I keep getting errors:

code:

@Query("select t from Telemetry t where t.id = :id and t.updateTimestamp between :startTime and :endTime and #{ #T(java.lang.Math).random() } < :samplingRate ")
Iterable<Telemetry> findFor(@Param("id") String org, @Param("startTime") Instant startTime, @Param("endTime") Instant endTime, @Param("samplingRate") Float samplingRate);

error:

org.hibernate.QueryException: unexpected char: '#' [select t from com.mz.rad.dao.Telemetry t where t.id = :id and t.updateTimestamp between :startTime and :endTime and #{ #T(java.lang.Math).random() } < :samplingRate ]

Digging deeper into this I discover this exception:

throw new IllegalArgumentException("Parameter with that position [" + parameterPosition + "] did not exist");

parameter position is 1 or 2 Depending on which syntax I use.

I have tried with with/without hashes. No luck. I am using postgres which has a random() function but it doesn't like me invoking that either (which is my ultimate goal).

This question is about using SPeL, but I just need a random number

Answer 1

You can try like this :#

select t from Telemetry t where t.id = :id and t.updateTimestamp between :startTime and :endTime and :#{T(java.lang.Math).random()} < :samplingRate

If not then use ?#

select t from Telemetry t where t.id = :id and t.updateTimestamp between :startTime and :endTime and ?#{T(java.lang.Math).random()} < :samplingRate

Answer 2

Answer:

@Query("select t from Telemetry t where t.id = :id and t.updateTimestamp between :startTime and :endTime and :#{T(java.lang.Math).random()} < :samplingRate ")

Helps to bump your POM

   <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
        <version>1.10.5.RELEASE</version>
    </dependency>