BatchSqlUpdate - how to get automatically generated keys

Question

I am using spring BatchSqlUpdate to insert a rowset. How to get automatically generated keys for all inserted rows?

When doing one insert, I get keys like this -

SqlUpdate sqlUpdate = new SqlUpdate(dataSource, sqlTemplate.toString()); sqlUpdate.setReturnGeneratedKeys(true); KeyHolder keyHolder = new GeneratedKeyHolder(); sqlUpdate.update(new Object[] {}, keyHolder); return keyHolder.getKey().longValue(); 

Thanks!

Answer 1

There is no such solution for this using BatchSqlUpdate as far as I know, but you can always

• request the last key before inserting
• using this information, request all new keys after insertion

Answer 2

class template proc:

 public abstract class BatchPreparedStatementSetterWithKeyHolder<T> implements BatchPreparedStatementSetter { private final List<T> beans; /** * @param datas * @param returnGeneratedKeys true设置{@linkplain Statement#RETURN_GENERATED_KEYS} */ public BatchPreparedStatementSetterWithKeyHolder(List<T> beans) { this.beans = beans; } @Override public void setValues(PreparedStatement ps, int i) throws SQLException { setValues(ps, beans.get(i)); } @Override public final int getBatchSize() { return beans.size(); } public void setPrimaryKey(KeyHolder keyHolder) { List<Map<String, Object>> keys = keyHolder.getKeyList(); for (int i = 0, len = keys.size(); i < len; i++) { setPrimaryKey(keys.get(i), beans.get(i)); } } protected abstract void setValues(PreparedStatement ps, T bean) throws SQLException; protected abstract void setPrimaryKey(Map<String, Object> primaryKey, T bean); } 

batchupdate utility method:

 private static void generatedKeys(PreparedStatement ps, KeyHolder keyHolder) throws SQLException { List<Map<String, Object>> keys = keyHolder.getKeyList(); ResultSet rs = ps.getGeneratedKeys(); if (rs == null) return; try { keys.addAll(new RowMapperResultSetExtractor<Map<String, Object>>(new ColumnMapRowMapper(), 1).extractData(rs)); } finally { UtilIO.close(rs); } } /** * 批量更新* @param jdbcTemplate * @param sql * @param pss * @param keyHolder 存储主键，如果要存储主键，就必须传入此对象* @return sql执行结果* @see JdbcTemplate#batchUpdate(String, org.springframework.jdbc.core.BatchPreparedStatementSetter) */ public static <T> int[] batchUpdateWithKeyHolder(JdbcTemplate jdbcTemplate, final String sql, final BatchPreparedStatementSetterWithKeyHolder<T> pss) { return jdbcTemplate.execute(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); } }, new PreparedStatementCallback<int[]>() { @Override public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { try { int batchSize = pss.getBatchSize(); InterruptibleBatchPreparedStatementSetter ipss = (pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter) pss : null); int[] result; KeyHolder keyHolder = new GeneratedKeyHolder(); try { if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) { for (int i = 0; i < batchSize; i++) { pss.setValues(ps, i); if (ipss != null && ipss.isBatchExhausted(i)) break; ps.addBatch(); } result = ps.executeBatch(); generatedKeys(ps, keyHolder); } else { List<Integer> rowsAffected = new ArrayList<Integer>(); for (int i = 0; i < batchSize; i++) { pss.setValues(ps, i); if (ipss != null && ipss.isBatchExhausted(i)) break; rowsAffected.add(ps.executeUpdate()); generatedKeys(ps, keyHolder); } result = UtilObj.tointArray(rowsAffected); } } finally { pss.setPrimaryKey(keyHolder); } return result; } finally { if (pss instanceof ParameterDisposer) ((ParameterDisposer) pss).cleanupParameters(); } } }); } 

ex:

 UtilJdbc.batchUpdateWithKeyHolder(jdbcTemplate, "insert into tbe_vm_node (creator_id, host, ssh_ip, ssh_user, ssh_passwd)" + " values (?, ?, ?, ?, ?)", new BatchPreparedStatementSetterWithKeyHolder<VmNode>(vmNodes) { @Override protected void setValues(PreparedStatement ps, VmNode vmNode) throws SQLException { UtilJdbc.setValues(ps, vmNode.getCreatorId(), vmNode.getHost(), vmNode.getSshIp(), vmNode.getSshUser(), vmNode.getSshPasswd()); } @Override protected void setPrimaryKey(Map<String, Object> primaryKey, VmNode vmNode) { vmNode.setId((Long) primaryKey.get("abc")); } }); 

Answer 3

Simple answer: use getKeyList () method

 KeyHolder keyHolder = new GeneratedKeyHolder(); // Specify which column IDs to return, or get all columns by default jdbcTemplate.update(INSERT_SQL, new MapSqlParameterSource(), keyHolder, new String[] {"column_name"}); List<Map<String, Object>> keyList = keyHolder.getKeyList();