时间戳

首页 > 解决方案 > 休眠查询命名参数中的单引号给出错误

hibernate - 休眠查询命名参数中的单引号给出错误

问题描述

我需要在 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 没有针对如此简单的事情的快速解决方案。任何建议都会有所帮助。

标签： hibernatejpa

解决方案

我想你正在使用时间刻度？使用以下内容有什么问题？

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)

推荐阅读