hibernate查询命名参数中的单引号出现错误

Question

我需要在timeBucket周围有一个引号。我有以下本机sql，它运行得非常好。

entityManager.createNativeQuery("SELECT time_bucket('" + timeBucket + "', time) as timestamp, avg(cast(value as double precision)) as value" +
" FROM agent_data " +
" WHERE agent_id = :agentId and topic_id = :topicId" +
" GROUP BY timestamp " +
" ORDER BY timestamp DESC " +
" LIMIT :noOfRecords", "GetAgentDataMapping")
.setParameter("noOfRecords", noOfRecords)
.setParameter("agentId", agentId)
.setParameter("topicId", topicId)
.getResultList();

我正在连接timeBucket参数，因为如果我使用setParameter方法绑定该变量，那么我会得到错误

"Could not locate ordinal parameter [1], expecting one of [2, 3, 4, 5, 6]; nested exception is java.lang.IllegalArgumentException: Could not locate ordinal parameter [1], expecting one of [2, 3, 4, 5, 6]"

我尝试使用sql字符串连接，如下所示。但它失败了，出现了同样的错误：

em.createNativeQuery("SELECT time_bucket('|| :timeBucket ||', time) as timestamp, avg(cast(value as double precision)) as value" +
" FROM agent_data " +
" WHERE agent_id = :agentId and topic_id = :topicId" +
" GROUP BY timestamp " +
" ORDER BY timestamp DESC " +
" LIMIT :noOfRecords", "GetAgentDataMapping")
.setParameter("timeBucket", timeBucket)
.setParameter("noOfRecords", noOfRecords)
.setParameter("agentId", agentId)
.setParameter("topicId", topicId)
.getResultList();

我还使用了SELECT time_bucket('''|| :timeBucket ||''', time)来转义单引号。

但同样的错误，没有用。Hibernate对于这么简单的事情并没有一个快速的解决方案，这真的很令人沮丧。任何建议都会有帮助。

Answer 1

我想您使用的是时间刻度？使用以下内容有什么问题？

em.createNativeQuery("SELECT time_bucket(:timeBucket, time) as timestamp, avg(cast(value as double precision)) as value" +
" FROM agent_data " +
" WHERE agent_id = :agentId and topic_id = :topicId" +
" GROUP BY timestamp " +
" ORDER BY timestamp DESC " +
" LIMIT :noOfRecords", "GetAgentDataMapping")
.setParameter("timeBucket", timeBucket)
.setParameter("noOfRecords", noOfRecords)
.setParameter("agentId", agentId)
.setParameter("topicId", topicId)
.getResultList();

AFAIUtime_bucket的参数需要是字符串或间隔。也许您需要将参数强制转换为区间cast(:timeBucket as interval)