How to set query parameters with single quotes

Question

I'm using a oracle database.I need to run a update query through jpa repository.This is the query I have tried to execute.

            @Transactional(propagation = Propagation.REQUIRES_NEW)
            @Modifying
            @Query(
                value = "UPDATE transactionlog SET transactionstatus= :ps,startedat = CURRENT_TIMESTAMP, readytoprocessat= (CURRENT_TIMESTAMP+ interval ':to' second)  WHERE logid IN (:li) ",
                nativeQuery = true)
            public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList, @Param("to") int timeOut);

But this exception

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that name [to] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that name [to] did not exist

But if i change this method as follows, it works fine.

@Transactional(propagation = Propagation.REQUIRES_NEW)
            @Modifying
            @Query(
                value = "UPDATE transactionlog SET transactionstatus= :ps,startedat = CURRENT_TIMESTAMP, readytoprocessat= (CURRENT_TIMESTAMP+ interval '5' second)  WHERE logid IN (:li) ",
                nativeQuery = true)
            public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList);

Any idea?

score 4 · Answer 1 · edited May 23 '17 at 12:17

Parameter with name [to] doesn't exist because you put :to between single quotes. Use :to instead of ':to'.

That being said, this will not work anyway. I faced really similar issue and after some hours finally found a solution which I present in answer here. For some reason, when interval comes into play injection of parameters doesn't work as you would expect.

Considering conclusion from the link above - I believe this should work:

@Transactional(propagation = Propagation.REQUIRES_NEW)
@Modifying
@Query(value = "UPDATE transactionlog SET transactionstatus= :ps,
       startedat = CURRENT_TIMESTAMP, 
       readytoprocessat= (CURRENT_TIMESTAMP + (( :to ) || 'second')\\:\\:interval)
       WHERE logid IN (:li) ",nativeQuery = true)
public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList, @Param("to") int timeOut);

goddam spring. Mine worked without the double backslashes `\\` on interval. — Wall-E, May 27 '22 at 14:26

score 0 · Answer 2 · answered May 20 '15 at 06:55

0

replace the :ps and all other parameter with ?1, ?2, ... and make the methos parameter match SQL parameter (the order will be important) .

answered May 20 '15 at 06:55

Alaa Abuzaghleh

1,023
6
11

score 0 · Answer 3 · answered May 22 '15 at 05:37

I have found an answer for this problem.

@Transactional(propagation = Propagation.REQUIRES_NEW)
        @Modifying
        @Query(
            value = "UPDATE transactionlog SET transactionstatus= :ps,startedat = CURRENT_TIMESTAMP, readytoprocessat= CURRENT_TIMESTAMP+ NUMTODSINTERVAL( :to, 'SECOND' )  WHERE logid IN (:li) ",
            nativeQuery = true)
        public Integer reserve(@Param("ps") short processingStatus, @Param("li") List<Integer> logIdList, @Param("to") int timeOut);

score -2 · Answer 4 · answered May 20 '15 at 07:01

-2

readytoprocessat= (CURRENT_TIMESTAMP+ interval ':to' second)

This clause is having issue, try to perform it one line prior, separately. Then you would be able to see the problem yourself.

answered May 20 '15 at 07:01

Raúl

1,542
4
24
37

How to set query parameters with single quotes

4 Answers4

Linked