spring batch insert using hibernateTemplate, JdbcTemplate

Question

I have a few questions related to batch insert in Spring.

When I do something like that:

 public void save(Car car) {

String sql1 = "insert into Car values (1, 'toyota')"; String sql2 = "insert into Car values (2, 'chrysler')"; String sql3 = "insert into Car values (3, 'infinity')";

String[] tab = new String[2]; tab[0] = sql1; tab[1] = sql2; tab[2] = sql3;

getJdbcTemplate().update(sql1); getJdbcTemplate().update(sql2); getJdbcTemplate().update(sql3);

// getJdbcTemplate().batchUpdate(tab);

}

in mysql log file I see:

    1 Query       insert into Car values (1, 'toyota')
    2 Query       insert into Car values (2, 'chrysler')
    3 Query       insert into Car values (3, 'infinity')

so we have 3 insert statements (and 3 network calls).

When I use getJdbcTemplate().batchUpdate(tab) in the log file I can see:

1094 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing SQL batch update of 3 statements 1110 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource 1110 [main] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/test?useServerPrepStmts=true] 1610 [main] DEBUG org.springframework.jdbc.support.JdbcUtils - JDBC driver supports batch updates

and in mysql log:

    1 Query       insert into Car values (1, 'toyota')
    1 Query       insert into Car values (2, 'chrysler')
    1 Query       insert into Car values (3, 'infinity')

I understand that in background addBatch method is invoked on statement object and all these operations are performed simultaneously. Additional benefit is reduction of network calls. Is my reasoning correct?

I'm looking for something similar in HibernateTemplate. I can do it in this way:

  getHibernateTemplate().saveOrUpdateAll(Arrays.asList(new Car(4, "infinity"), new Car(5, "ford")));

In that case, in the log file I can see:

    3 Prepare     select car_.id, car_.name as name0_ from Car car_ where car_.id=?
    3 Prepare     select car_.id, car_.name as name0_ from Car car_ where car_.id=?
    3 Prepare     insert into Car (name, id) values (?, ?)

So it seems that everything is done in one shot as it was done for getJdbcTemplate().updateBatch(...)

Please correct me if I'm wrong.

Answer 1

To produce result similar to Hibernate (with prepared statment), you should use JdbcTemplate.batchUpdate(String, BatchPreparedStatementSetter). Something like this:

final List<Car> cars = Arrays.asList(...);

getJdbcTemplate().batchUpdate("insert into Car (name, id) values (?, ?);",
    new BatchPreparedStatementSetter() {
        private int i = 0;
        public int getBatchSize() { return cars.size(); }
        public void setValues(PreparedStatement ps) {
            ps.setString(1, cars.get(i).getName());
            ps.setInt(2, cars.get(i).getId());
            i++;
        }
    });