CODEWITHSUNDEEP
javasqlspringjdbcjdbctemplate
Edward Dale
Edward Dale

Reputation: 30133

Inserting multiple rows using JdbcTemplate

How can I execute the following SQL in a scalable way using JdbcTemplate running on mySQL. In this case, scalable means:

  1. Only one SQL statement is executed on the server
  2. it works for any number of rows.

Here's the statement:

INSERT INTO myTable (foo, bar) VALUES ("asdf", "asdf"), ("qwer", "qwer")

Assume that I have a list of POJO's with foo and bar fields. I realize that I could just iterate over the list and execute:

jdbcTemplate.update("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMap)

but that doesn't doesn't accomplish the first criterion.

I believe I could also execute:

jdbcTemplate.batchUpdate("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMapArray)

but from what I can tell, that will just compile the SQL once and execute it multiple times, failing the first criterion again.

The final possibility, which seems to pass both criteria, would be to simply build the SQL myself with a StringBuffer, but I'd like to avoid that.

Upvotes: 29

Views: 70971

Answers (4)

Anatolii Stepaniuk
Anatolii Stepaniuk

Reputation: 2703

It looks to me that batchUpdate() method of JdbcTemplate could be helpful in this case (copied from here http://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/):

//insert batch example
public void insertBatch(final List<Customer> customers){

  String sql = "INSERT INTO CUSTOMER " +
    "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

  getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
    Customer customer = customers.get(i);
    ps.setLong(1, customer.getCustId());
    ps.setString(2, customer.getName());
    ps.setInt(3, customer.getAge() );
}

@Override
public int getBatchSize() {
    return customers.size();
}

  });
 }

Upvotes: 6

Sheetal Mohan Sharma
Sheetal Mohan Sharma

Reputation: 2924

you can also try with jdbcInsert.executeBatch(sqlParamSourceArray)

   // define parameters
jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
jdbcInsert.withTableName("TABlE_NAME");
SqlParameterSource[] sqlParamSourceArray = new SqlParameterSource[apiConsumer
        .getApiRoleIds().size()];
for (int i = 0; i < myCollection.size(); i++) 
    {
  sqlParamSourceArray[i] = new MapSqlParameterSource().addValue("COL1");
      ......................
}
// execute insert
int[] keys = jdbcInsert.executeBatch(sqlParamSourceArray);

Upvotes: -1

Will
Will

Reputation: 102

Multirow inserts (using "row value constructors") are in fact part of the SQL-92 standard. See http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts.

Some databases do not support this syntax, but many do. In my experience Derby/Cloudscape, DB2, Postgresql and the newer Hypersonic 2.*+ releases do support this.

Your concern about getting this to work as a PreparedStatement is understandable, but I've seen similar cases where Spring JDBC does automatically handle a Collection of items for certain queries (like where in (?)), but I cannot vouch for this case.

I did find some possibly helpful information at (can't add second link to this post) which might be of some help.

I can tell you that its probably not possible for your second requirement (works for any number of arguments) to be met in the most strict sense: every database I've used does impose query length limitations that would come into play.

Upvotes: 5

Desorder
Desorder

Reputation: 491

You can use BatchPreparedStatementSetter like below.

public void insertListOfPojos(final List<MyPojo> myPojoList) {

    String sql = "INSERT INTO "
        + "MY_TABLE "
        + "(FIELD_1,FIELD_2,FIELD_3) "
        + "VALUES " + "(?,?,?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i)
            throws SQLException {

            MyPojo myPojo = myPojoList.get(i);
            ps.setString(1, myPojo.getField1());
            ps.setString(2, myPojo.getField2());
            ps.setString(3, myPojo.getField3());

        }

        @Override
        public int getBatchSize() {
            return myPojoList.size();
        }
    });

}

Upvotes: 49

Related Questions