CODEWITHSUNDEEP
javaspringoracle-databasejdbcspring-jdbc
Martin
Martin

Reputation: 1508

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

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.

Upvotes: 3

Views: 3980

Answers (2)

ibre5041
ibre5041

Reputation: 5288

There is no straightforward solution for this. There is NO way how to put collection into IN clause. The IN clause expects either fixed lenght set of scalars of a subquery.

So you can create your own database type

CREATE TYPE NUMBERS AS TABLE OF NUMBER;

And then select from:

 SELECT test_id, name FROM test_table WHERE test_id IN (select * from TABLE( ? ) )

In this case you can pass array of integers a bind variable. TABLE is special "cast" operator from collection into a table.

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39437

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);

Upvotes: 1

Related Questions