如何使用单引号设置查询参数 [英] How to set query parameters with single quotes

问题描述

我正在使用oracle数据库.我需要通过jpa存储库运行更新查询.这是我尝试执行的查询.

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

但是这个例外

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

有什么主意吗?

推荐答案

名称为[to]的参数不存在，因为您将:to放在了单引号之间.使用:to代替':to'.

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

话虽如此，这还是行不通的.我遇到了非常类似的问题，几个小时后终于找到了一个解决方案，我在此处在此处答复.出于某些原因，当interval开始起作用时，参数注入将无法按您期望的那样工作.

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

这篇关于如何使用单引号设置查询参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！