Set Array as SqlParameterSource in Spring

Question

I want to excute the following select statement

SELECT array_to_json(array_agg(row_to_json(opt)))
FROM (
    SELECT * FROM option WHERE optionid IN (:tags)
)opt

via

MapSqlParameterSource params = new MapSqlParameterSource();
Integer[] a={2200,23};
params.addValue("tags", Arrays.asList(a));
getSimpleJdbcTemplate().queryForObject(statement, String.class, params);

But I always run into the error:

class org.springframework.dao.InvalidDataAccessApiUsageException No value supplied for the SQL parameter 'tags': No value registered for key 'tags'

Same if I use:

Integer[] a = { 2200, 23 };
params.addValue("tags", Arrays.asList(a));
namedParameterJdbcTemplate.queryForObject(statement, params, String.class);

I've updated my answer with a successful example.

RubioRic
– RubioRic

2016-05-04 07:56:44 +00:00
Commented May 4, 2016 at 7:56 — RubioRic
– RubioRic, Commented May 4, 2016 at 7:56

RubioRic · Accepted Answer · 2016-05-04 07:54:34Z

11

The API javadoc states that you must

Pass in the values as a java.util.List of primitive objects

http://docs.spring.io/spring/docs/3.1.0.RELEASE/reference/html/jdbc.html#jdbc-in-clause

And you are using an array. You can convert the array to List using Arrays.asList

Also, I think that you need a NamedParameterJdbcTemplate instead of a SimpleJdbcTemplate for that query.

EDITED:

I've used this example project to test a solution http://www.tutorialspoint.com/spring/spring_jdbc_example.htm

This is the table used in that example

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

These are the records inserted in the DB

------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15

In class StudentJDBCTemplate, I've instantiated a NamedParameterJdbcTemplate and used it in a new method listStudentsNames

   public void setDataSource(DataSource dataSource) {
       this.dataSource = dataSource;
       this.jdbcTemplateObject = new JdbcTemplate(dataSource);
       this.namedJdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
   }

   public List<String> listStudentsNames() {
       String SQL = "select name from Student where id IN (:tags)";

       Integer[] intArray = {1, 2, 3};
       List<Integer> intList = Arrays.asList(intArray);

       MapSqlParameterSource params = new MapSqlParameterSource();
       params.addValue("tags", intList);

       return namedJdbcTemplateObject.queryForList(SQL, params, String.class);

    }

I've also changed MainApp to invoke that new method and write the obtained results

public class MainApp {
     public static void main(String[] args) {
         ApplicationContext context =
         new ClassPathXmlApplicationContext("Beans.xml");

         StudentJDBCTemplate studentJDBCTemplate =
  (StudentJDBCTemplate) context.getBean("studentJDBCTemplate");

         System.out.println("------Listing Multiple Records--------" );
         List<String> studentsName = studentJDBCTemplate.listStudentsNames();
         for (String name : studentsName) {
              System.out.println("Name : " + name);
         }

     }
 }

If you run MainApp, the obtained results are

edited May 4, 2016 at 7:54

answered May 4, 2016 at 5:29

RubioRic

2,4684 gold badges32 silver badges38 bronze badges

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

3 Comments

bobbel Over a year ago

Great stuff. Didn't even know, that it's possible to directly provide lists for IN clauses.

StellaMaris Over a year ago

I run into the same error with NamedParameterJdbcTemplate, please see my edits.

RubioRic Over a year ago

@StellaMaris I'm building a small project to test it. I'll be back.

Collectives™ on Stack Overflow

Set Array as SqlParameterSource in Spring

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related