Question 89855 in PostgreSQL Question posted in
The official documentation can be found here.

Error when passing List/Set of Strings to be Used in an "IN" clause in a query: Can't infer type – Postgresql

I am attempting to pass an array of string values so that said array of strings can be used in an "IN" clause like so with Java/PostgreSQL:

Sample query (contained in a string variable):

private static final String strSQLQueryAcceptingAnArray = "SELECT count(*) FROM sometable WHERE clm IN (:arrStringValues);";

public List<SampleReturnObject> getInClauseResults(List<String> lstStringValues) {
    Set<String> setStringValues = new HashSet<String>(lstStringValues);
    return this.query(strSQLQueryAcceptingAnArray, previouslyDefinedRowMapper, new Object[] { setStringValues });
}

However, when I run this, I get the following error message:

Can’t infer the SQL type to use for an instance of ors.springframework.jdbc.namedparam.MapSQLParameterSource. Use setObject() with an explicit Types value to specify the type to use.

How can I pass a list/array of strings to use in an "IN" clause in PostgreSQL using Java and Spring Framework?

Tags:

2

Answers


  1. 0

    Append the array values in a string by seperating the values using comma and then use that string in sql query IN clause.

    Login or Signup to reply.
  2. 0

    You can use a NamedParameterJdbcTemplate with SqlParameterSource like this:

    
    private static final String strSQLQueryAcceptingAnArray = "SELECT count(*) FROM sometable WHERE clm IN (:arrStringValues)";

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<SampleReturnObject> getInClauseResults(List<String> lstStringValues) {
        Set<String> setStringValues = new HashSet<String>(lstStringValues);
        SqlParameterSource sqlParameterSource = new MapSqlParameterSource("arrStringValues", setStringValues);
        return namedParameterJdbcTemplate.query(strSQLQueryAcceptingAnArray, sqlParameterSource, previouslyDefinedRowMapper);
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search