Spring SQL-Parameter Mapping (array)

Question

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

Looks like there was a similar problem: stackoverflow.com/questions/31925109/… — stas.yaranov
– stas.yaranov, Commented May 12, 2016 at 11:46

StellaMaris · Accepted Answer · 2016-05-12 13:14:55Z

1

Separate brackets from :tags solves the problem:

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

answered May 12, 2016 at 13:14

StellaMaris

8673 gold badges12 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Spring SQL-Parameter Mapping (array)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related