How to insert an ArrayList of String into database using SqlParameterSource

Question

I want to insert the contents of a Person bean into my database table person using Spring's SqlParameterSource. One of the attributes in Person class is an a java.util.List which is causing org.springframework.jdbc.BadSqlGrammarException.

The Person class is as follows:

package learn.spring.model;

import java.util.List;

public class Person {
    private String name;
    private int age;
    private List<String> affiliations;
    @Override
    public String toString() {
        return "Person [name=" + name + ", age=" + age + ", affiliations="
                + affiliations + "]";
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public List<String> getAffiliations() {
        return affiliations;
    }
    public void setAffiliations(List<String> affiliations) {
        this.affiliations = affiliations;
    }


}

The code that inserts this Person bean into db is:

String query="insert into person (name, age, affiliations) values (:name,:age,:affiliations)";
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(p);
KeyHolder key = new GeneratedKeyHolder();
System.out.println(template.update(query, paramSource, key));

The bean I am trying to insert is: Person [name=rickesh, age=22, affiliations=[1, 2, 3]]

I am getting the following exception:

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into person (name, age, affiliations) values (?,?,?, ?, ?)]; nested exception is java.sql.SQLException: Column count doesn't match value count at row 1
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:843)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:288)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:270)
    at learn.spring.main.ApplicationRunner.createPerson(ApplicationRunner.java:46)
    at learn.spring.main.ApplicationRunner.main(ApplicationRunner.java:38)
Caused by: java.sql.SQLException: Column count doesn't match value count at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233)
    at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:845)
    at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    ... 5 more

With some hit and trial I am able to understand that Person.affiliations is causing this exception. How can I use SqlParameterSource to insert a List into database table? Please advice.

Answer 1

Your approach is not correct.

EDIT: You get an error because Spring cannot interpret that you want to store list in single row.

You cannot store a list of strings in a single row. Convert your private List<String> affiliations; into a comma separate list and it will work.

Use this to generate comma separated list from List:

String delim = "";
    for (Item i : list) {
        sb.append(delim).append(i);
        delim = ",";
    }