项目中一个业务场景包含两个相互依赖的批量插入，第二次批量插入依赖第一次批量插入数据的自增id。我们的工程依赖的spring jdbcTemplate，于是我就翻看了一下jdbcTemplate的源码，发现批量插入接口，只是单存的返回影响的列表，并没有实际意义。
```
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)
```
去[spring jira](https://jira.spring.io)搜了一下，果然有记录，由于解决方案依赖数据库驱动，不同版本的数据支持不一样，所以该解决方案并没有写入官方的api中，下面我贴一下自己使用实例。
#####自定义继承JdbcTemplate的子类
````
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.KeyHolder;
import java.sql.*;
import java.util.List;
public class CustomerJdbcTemplate extends JdbcTemplate {
public int[] batchUpdate(final String sql,final BatchPreparedStatementSetter pss,
final KeyHolder generatedKeyHolder) throws DataAccessException {
return (int[]) execute(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException {
return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
}
},
new PreparedStatementCallback() {
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
if (logger.isDebugEnabled())
logger.debug("Executing batch SQL update and returning " +
"generated keys [" + sql + "]");
try {
int batchSize = pss.getBatchSize();
int totalRowsAffected = 0;
int[] rowsAffected = new int[batchSize];
List generatedKeys = generatedKeyHolder.getKeyList();
generatedKeys.clear();
ResultSet keys = null;
for (int i = 0; i < batchSize; i++) {
pss.setValues(ps, i);
rowsAffected[i] = ps.executeUpdate();
totalRowsAffected += rowsAffected[i];
try {
keys = ps.getGeneratedKeys();
if (keys != null) {
RowMapper rowMapper = new ColumnMapRowMapper();
RowMapperResultSetExtractor rse =
new RowMapperResultSetExtractor(rowMapper, 1);
generatedKeys.addAll((List) rse.extractData(keys));
}
} finally {
JdbcUtils.closeResultSet(keys);
}
}
if (logger.isDebugEnabled())
logger.debug("SQL batch update affected "
+ totalRowsAffected + " rows and returned "
+ generatedKeys.size() + " keys");
return rowsAffected;
} finally {
if (pss instanceof ParameterDisposer)
((ParameterDisposer) pss).cleanupParameters();
}
}
});
}
}
````
#####使用实例
````
final List<User> users = new ArrayList();
for (int i = 0; i < 10; i++) {
User user = new User();
user.setName("name"+1L);
user.setAge(i)
users.add(user);
}
String sql = "insert into user(name,age) values(?,?)";
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
customerJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
User user = users.get(i);
ps.setString(1, user.getName());
ps.setInt(2, user.getAge());
}
@Override
public int getBatchSize() {
return users.size();
}
}, generatedKeyHolder);
List<Map<String, Object>> objectMap = generatedKeyHolder.getKeyList();
for(Map<String, Object> map : objectMap){
System.out.println(map.get("GENERATED_KEY"));
}
````