3

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.

Community
  • 1
  • 1
Martin
  • 1,488
  • 1
  • 13
  • 16
  • You may want to show the code of the super-class too. – peter.petrov Oct 22 '15 at 13:34
  • You mean MappingSqlQuery? It's from Spring JDBC: https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/object/MappingSqlQuery.html – Martin Oct 22 '15 at 13:46

2 Answers2

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

peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • I've tried `declareParameter(new SqlParameter(Types.INTEGER));` but that didn't work. That's the closest thing coming to an "Type int", I think? The second example for the list does not show which SqlParameter I should map it to. I am not executing the query directly, but via `findObject`. (see my edit in the original post) – Martin Oct 22 '15 at 13:47
1

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.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • Thanks for the suggestion. I have tried this as well, but still fail to understand what kind of SqlParameter I should map this ? parameter to. Have tried Types.ARRAY, Types.INTEGER and Types.JAVA_OBJECT. – Martin Oct 23 '15 at 09:51
  • Maybe OracleTypes.ARRAY. I'm not sure whether plain JDBC supports non-scalar datatypes. If yes, then it must be something new. – ibre5041 Oct 23 '15 at 09:55
  • I've tried OracleTypes.ARRAY, OracleTypes.JAVA_OBJECT and OracleTypes.OTHER with both a List and int[] without result, unfortunately... – Martin Oct 23 '15 at 10:05
  • What is the error you're getting? There are many HOWTOs on the Internet, how to pass as an array to a Oracle SQL query/procedure. – ibre5041 Oct 23 '15 at 10:25
  • Mainly `java.sql.SQLException: Invalid column type` and in some cases appended with `: sqlType=2003`. Cannot find much online about what kind of SqlParameter to use. I can find a lot of tutorials where the declareParameter() function is not needed, though. Will probably look into that soon. – Martin Oct 23 '15 at 10:59