Single quote in hibernate query named parameter gives error

Question

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.

Out of curiosity, would it work if you'd use positional parameters? — Davide D'Alto
– Davide D'Alto, Commented Apr 15, 2021 at 8:10
@Davide No, it doesn't. How come Hibernate doesn't have a solution for this? — Shehan Simen
– Shehan Simen, Commented Apr 16, 2021 at 1:13
I think there is an error in your code, but first try what I suggested below. Apart from that, you could just use HQL for this. Is there any reason to use SQL? — Christian Beikov
– Christian Beikov, Commented Apr 16, 2021 at 9:19
@ChristianBeikov I just don't like to use HQL as in future this query will become complex and HQL may not be the ideal solution — Shehan Simen
– Shehan Simen, Commented Apr 20, 2021 at 0:23
Would you mind elaborating what it is that you think HQL is not the ideal solution for? — Christian Beikov
– Christian Beikov, Commented Apr 20, 2021 at 7:24

Christian Beikov · Accepted Answer · 2021-04-16 09:17:31Z

1

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)

answered Apr 16, 2021 at 9:17

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shehan Simen Over a year ago

If I use cast(:timeBucket as interval) then it works. Thanks for the suggestion. Otherwise the issue is same as I posted originally

Christian Beikov Over a year ago

Not sure what you tried, but the examples you posted look like you used it in a wrong way. '|| :timeBucket ||' is a string literal within which there is obviously no parameter replacement going on. What you probably wanted to do is '' || :timeBucket || ''

Collectives™ on Stack Overflow

Single quote in hibernate query named parameter gives error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related