CODEWITHSUNDEEP
springpostgresql
StellaMaris
StellaMaris

Reputation: 867

Spring SQL-Parameter Mapping (array)

I want to use a parameter called tags in a sql statement:

SELECT * FROM reply WHERE array[:tags] @> array[2293,2294];

I pass the parameter and execute the statement via:

MapSqlParameterSource params = new MapSqlParameterSource();
Integer[] a={2293,2294};
params.addValue("tags", Arrays.asList(a));
namedParameterJdbcTemplate.queryForObject(statement, params, String.class);

But it throws the error:

class org.springframework.dao.InvalidDataAccessApiUsageException No value supplied for the SQL parameter 'tags]': No value registered for key 'tags]'

If I change the statement to:

SELECT * FROM reply WHERE '{:tags}' @> array[2293,2294];

It throws the error:

class org.springframework.dao.DataIntegrityViolationException PreparedStatementCallback; SQL [SELECT * FROM reply WHERE '{:tags}' @> array[2293,2294]; ERROR: invalid input syntax for integer: ":tags" Position: 72; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: ":tags" Position: 72

If I change the statement to:

SELECT * FROM reply WHERE (:tags) @> array[2293,2294];

It throws the error:

class org.springframework.jdbc.BadSqlGrammarException PreparedStatementCallback; bad SQL grammar [SELECT * FROM reply WHERE (?, ?) @> array[2293,2294]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: record @> integer[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 81

Upvotes: 0

Views: 1070

Answers (1)

StellaMaris
StellaMaris

Reputation: 867

Separate brackets from :tags solves the problem:

SELECT * FROM reply WHERE array[ :tags ] @> array[2293,2294];

Upvotes: 1

Related Questions