How to used a NamedJDBCTemplate placeholder in the where clause of a postgres ARRAY comparison?

user_356011 0
Viewed 117

I'm attempting to do a postgres query with an ARRAY/GIN where clause using spring-jdbc. NamedParameterJdbcTemplate throws an error when I attempt to use a named parameter inside an ARRAY[].

minimal example:

CREATE TABLE testing(col1 text[]);
INSERT INTO testing VALUES ('{item1, item2, item3}');
INSERT INTO testing VALUES ('{item4, item5, item6}');

=> select * from testing where col1 @> ARRAY['item4'];
        col1         
---------------------
 {item4,item5,item6}

Java:

        var sql = "select * from testing where col1 @> ARRAY[:item]";
        var paramMap = Map.of("item", "item4");
        var results = namedJDBCTemplate.queryForList(sql, paramMap);

Exception:

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'item]': No value registered for key 'item]'
    at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList
spring-boot spring-jdbc jdbc postgresql java
1 Answers

adding spaces around the parameter and casting it in the sql query, e.g.

select * from testing where col1 @> ARRAY[ CAST ( :item AS text) ]

fixed it

user_3560110
Related