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

我连接timeBukcket参数是因为如果我使用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();

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