What SQL Parameter Type to use for an IN condition when using MappingSqlQuery

Question

I am having trouble declaring a parameter for an IN condition when I use the MappingSqlQuery feature of Spring JDBC. I am using an Oracle database.

I have tried to use an INTEGER and an ARRAY without result.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

@Component
public class TestQuery extends MappingSqlQuery<List<Object>> {
    @Autowired
    public TestQuery(DataSource ds) {
        super(ds, "SELECT test_id, name FROM test_table WHERE test_id IN (?)");

        /////////////////////////////////
        // What sql type to declare here?
        declareParameter(new SqlParameter(Types.));
        /////////////////////////////////

        compile();
    }

    @Override
    protected List<Object> mapRow(ResultSet rs, int i) throws SQLException {
        // ...
        return null;
    }
}

I want to use the object of this TestQuery class as follows:

...
@Autowired
private TestQuery testQuery;

public List<Object> ...() {
    List<Integer> ids = Arrays.asList(36006122, 36004367);
    List<Object> objects = testQuery.findObject(ids);
    return objects;
}
...

The only information I can find about this is here: http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-in-clause The problem is that they are not declaring the parameter there, while it is needed in order to use the MappingSqlQuery feature: org.springframework.dao.InvalidDataAccessApiUsageException: 1 parameters were supplied, but 0 parameters were declared in class

Database type: Oracle

Spring version: 3.2.14.RELEASE

Oracle dependency version: ojdbc7:12.1.0.1

Edit: Added TestQuery usage

---

In the meantime I have fixed it by abandoning MappingSqlQuery and pretty much using this: https://stackoverflow.com/a/1327222/1019778 in combination with NamedParameterJdbcTemplate.

Answer 1

Type int should work here. Also, if you want to pass in a List of values, you can do this:
where some_column_name in (:lst).
Then you should do this to pass in the actual values:
query.setParameterList("lst", someJavaListOfValues);