Inserting multiple rows using JdbcTemplate

Question

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

Only one SQL statement is executed on the server
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.

This has nothing to do with JdbcTemplate, or even JDBC. You can't do this in SQL, period (or standard SQL, anyway), so you certainly can't do it in JdbcTemplate. — skaffman, Jul 02 '10 at 12:55
@skaffman: I've updated my question to say that I'm using mySQL. Maybe it's an mySQL-only feature, but it's described at http://dev.mysql.com/doc/refman/5.1/en/insert.html about a quarter of the way down: "INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example: " — Edward Dale, Jul 02 '10 at 13:01
@Teja: Yes, it would be possible to do in pure JDBC, but that's not the question. I've updated the question with a third possibility which would be to build the SQL all by hand. — Edward Dale, Jul 02 '10 at 13:04
If you're using InnoDB then a batchUpdate *should* only update the index table after the last insert. The only efficiency gain you'd get from using a single statement is that you'd have to send less data to the MySQL server. I doubt you'll be able to do the multiple inserts with a standard JdbcTemplate but you could always extend JdbcTemplate and roll your own batch insert method which built the insert string by hand. — Pace, Jul 02 '10 at 13:26
@Pace: That seems to be a good explanation and nobody else is posting an answer. If you rewrite it as an answer, I'll accept it. — Edward Dale, Jul 07 '10 at 13:58

score 48 · Answer 1 · answered Jan 15 '12 at 21:32

48

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

}

answered Jan 15 '12 at 21:32

Desorder

481
4
3

I've got "Parameter index out of range (1 > number of parameters, which is 0)" when using :NamedParameters instead of ?. Some update for using with NamedParameterJdbcTemplate? – luso Oct 21 '16 at 09:35
I think this solution doesn't meet the 1st criteria of the question. From the testing I've done, and from the following Javadoc for `BatchPreparedStatementSetter`, I think this approach simply makes multiple calls for the same SQL string: `sets values on a PreparedStatement provided by the JdbcTemplate class, for each of a number of updates in a batch using the same SQL.`. This is problematic for both performance and for the atomicity of the data. If one of the INSERT statements fails for an "out of range" Exception we'd want either all of the data to be written or none of it. – Joseph Cass Jun 21 '23 at 08:11

score 6 · Answer 2 · answered Jul 27 '15 at 11:20

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

  });
 }

Thanks a lot, this is exactly what I was looking for!! – Harish Kumar Saini Jun 18 '20 at 03:13 — Harish Kumar Saini, Jun 18 '20 at 03:13

Will · Accepted Answer · 2013-06-14T20:18:09.720

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.

that URL that SO did not allow my to post above was: http://fusesource.com/docs/router/2.2/transactions/DataAccess-JDBC.html — Will, Oct 15 '10 at 18:42

score -1 · Answer 4 · answered Feb 07 '14 at 09:40

-1

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

answered Feb 07 '14 at 09:40

Sheetal Mohan Sharma

2,908
1
23
24

1

This answer is false! `jdbcInsert.executeBatch()` does NOT return the keys. It returns `the array of number of rows affected as returned by the JDBC driver`. See the javadocs. https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcInsert.html#executeBatch-org.springframework.jdbc.core.namedparam.SqlParameterSource – Stewart Jan 26 '18 at 22:30

score -4 · Answer 5 · answered Oct 14 '10 at 18:09

-4

You can't do this in JDBC, period. In MySQL it's just syntactic sugar, but the effect of the statement will be the same as issuing several INSERT statements. So you can use batchUpdate and it will have the same effect.

answered Oct 14 '10 at 18:09

Chochos

5,155
22
27

3

Incorrect. MySQL's "extended insert" (as presented in the Question) is faster than a batch insert (where you prepare in advance but insert one row at a time). It is NOT a syntactic sugar in MySQL. – Shlomi Noach Jul 04 '13 at 06:39

Inserting multiple rows using JdbcTemplate

5 Answers5

Linked