title: How to insert an ArrayList of String into database using SqlParameterSource
body: <p>I want to <strong>insert the contents of a Person bean</strong> into my database <strong>table person</strong> using Spring's <strong>SqlParameterSource</strong>. One of the attributes in Person class is an a <code>java.util.List</code> which is causing <code>org.springframework.jdbc.BadSqlGrammarException</code>. </p>
  
  <p>The <code>Person</code> class is as follows:</p>
  
  <pre><code>package learn.spring.model;
  
  import java.util.List;
  
  public class Person {
      private String name;
      private int age;
      private List&lt;String&gt; 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&lt;String&gt; getAffiliations() {
          return affiliations;
      }
      public void setAffiliations(List&lt;String&gt; affiliations) {
          this.affiliations = affiliations;
      }
  
  
  }
  </code></pre>
  
  <p>The code that inserts this Person bean into db is:</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>The bean I am trying to insert is: <code>Person [name=rickesh, age=22, affiliations=[1, 2, 3]]</code></p>
  
  <p>I am getting the following exception:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>With some hit and trial I am able to understand that <code>Person.affiliations</code> is causing this exception. How can I use <strong>SqlParameterSource</strong> to insert a List into database table? Please advice.</p>