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:

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.

Solution 1

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.

Solution 2

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

}

Solution 3

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

  });
 }

Comments

[Teja Kantamneni]

Can we do the same using just JDBC??

[skaffman]

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.

[Edward Dale]

@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 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]

@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.

[Pace]

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.

[Edward Dale]

@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.

[Will]

that URL that SO did not allow my to post above was: fusesource.com/docs/router/2.2/transactions/…

[Shlomi Noach]

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.

[luso]

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?

[Stewart]

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. docs.spring.io/spring-framework/docs/current/javadoc-api/org‌​/…

[Harish Kumar Saini]

Thanks a lot, this is exactly what I was looking for!!