Passing parameter value in native query where it will be surrounded by single quotation marks

Question

I've spent a lot of time trying to figure out how to pass a parameter value to a native sql query. The value in the resulted query is surrounded with single quotation marks. That must be the main issue I think.

The following query works if the parameter value is hard-coded. I've excluded unrelated parts from it:

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - interval '70' minute
...
""";

However when I tried to pass the value:

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - interval ':x' minute
...
""";

var searchQuery = em.createNativeQuery(query, BikeEntity.class);
searchQuery.setParameter("x", 70);

I got the error:

java.lang.IllegalArgumentException: Could not locate named parameter [x], expecting one of []

Without quotation marks:

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - interval :x minute
...
""";

The error was:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 205

I've also tried different recommendations in this post Native query with named parameter fails with "Not all named parameters have been set"

but without success

Using Java 17 SpringBoot 2.7.6 PostgreSQL

What about `searchQuery.setParameter("x", "70");` without quotation marks in query? — Dirk Deyne, Apr 14 '23 at 16:11

score 1 · Answer 1 · answered Apr 14 '23 at 18:01

1

Just pass your parameter as string like

searchQuery.setParameter("x", "70 minutes");

And cast it as PostgreSQL interval type as

var query = 
"""
select
...
and ss.status_date < now() AT TIME ZONE 'UTC' - :x::interval
...
""";

answered Apr 14 '23 at 18:01

Pepe N O

1,678
1
7
11

I've got: `org.springframework.dao.InvalidDataAccessApiUsageException: Could not locate named parameter [x], expecting one of [x::interval]; nested exception is java.lang.IllegalArgumentException: Could not locate named parameter [x], expecting one of [x::interval]` – ka3ak Apr 19 '23 at 06:57
What if you separate the parameter from casting `and ss.status_date < now() AT TIME ZONE 'UTC' - :x ::interval`? I've tested it with JDBC and prepared statements and it works on `openjdk 17.0.6 2023-01-17 OpenJDK Runtime Environment (build 17.0.6+10-Ubuntu-0ubuntu122.04) OpenJDK 64-Bit Server VM (build 17.0.6+10-Ubuntu-0ubuntu122.04, mixed mode, sharing)` – Pepe N O Apr 19 '23 at 15:03
Unfortunately the separation didn't help either. The error was:` `org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet ... Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"` – ka3ak Apr 19 '23 at 17:19

score 0 · Answer 2 · answered Apr 19 '23 at 07:01

Meanwhile I solved the problem more radically by parameterizing the right operand of < entirely:

var query = """
...
and ss.status_date < :x
...
""";

var searchQuery = em.createNativeQuery(query, BikeEntity.class);
var calendar = Calendar.getInstance();
calendar.add(Calendar.MINUTE, - 70);
searchQuery.setParameter("x", calendar.getTime());
return searchQuery.getResultList();

Passing parameter value in native query where it will be surrounded by single quotation marks

2 Answers2