CODEWITHSUNDEEP
hibernatejpa
Shehan Simen
Shehan Simen

Reputation: 1306

Single quote in hibernate query named parameter gives error

I need to have a single quotation around the timeBucket. I have the following native sql, which is working perfectly fine.

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

I am concatenating timeBukcket parameter because if I use setParameter method to bind that variable, then I get error

"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]"

I tried using sql string concatenation like following. But it fails with same error:

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

Also I used SELECT time_bucket('''|| :timeBucket ||''', time) to escape single quote.

But same error, no use. This is really frustrating that Hibernate doesn't have a quick solution for such simple thing. Any advice will be helpful.

Upvotes: 1

Views: 1468

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16400

I suppose you are using timescale? What is the issue with using the following?

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 the parameter to time_bucket needs to be a string or interval. Maybe you need to cast the parameter to an interval cast(:timeBucket as interval)

Upvotes: 1

Related Questions