Question

spring JdbcTemplate批量插入 怎么获得数据库自动增长的id

Answer 1

如下，插入一条记录的时候可以这样返回id(一下代码只是说明事例): //保存一个test对象,并返回该对象 public Test save(Test test){ KeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement( Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement("insert into tb_test (name,sex) values (?,?)", Statement.RETURN_GENERATED_KEYS); ps.setString(1, test.getName()); ps.setString(2, test.getSex()); return ps; } }, keyHolder); //这里可以获得数据库id test.setId(Integer.valueOf(keyHolder.getKeyList().get(0) .toString())); return test; } // 批量插入, public List<Test> saveOrUpdateAll(final List<Test> list) { getJdbcTemplate().batchUpdate( "insert into tb_test (name,sex) values (?,?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, list.get(i).getName()); ps.setString(2, list.get(i).getSex()); } @Override public int getBatchSize() { return list.size(); } }); //哪个方法可以获得list中每个test对象的id?我不想重新进行查询 return list; } 问题补充：飞雪无情 写道你已经写好了插入一个对象的方法，就是public Test save(Test test)，批量插入的时候可以直接使用该方法的。# public List<Test> saveOrUpdateAll(final List<Test> list) { List<Test> resultList=new ArrayList<Test>(); for(Test test:list){ resultList.add(save(test)); } return resultList; } 这样就好了。我也考虑过这样的方式，但是把: getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement( Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement("insert into tb_test (name,sex) values (?,?)", Statement.RETURN_GENERATED_KEYS); ps.setString(1, test.getName()); ps.setString(2, test.getSex()); return ps; } }, keyHolder); 这么一段代码放在循环里面，会不会性能有些影响吧？Spring既然提供了batchUpdate,该会不会在批处理上有些性能优势呢? 问题补充：飞雪无情 写道spring这个批量插入有点限制，比如你这个特殊需要，我感觉它实现不了，所以你用我上面说的那个方法迂回实现。我感觉性能方面应该不会有太大的影响。你看spring的批量插入的时候这个BatchPreparedStatementSetter借口的方法setValues(PreparedStatement ps, int i) ,提供了一个索引i，它肯定也遍历了，要么怎么知道i的值。所以有特殊需求的时候就用上面那种方案，没有的时候推荐用spring提供了批量操作，我们项目中就这么做的。看了Spring这两个方法的源码，觉得这个东西在封装的时候考虑的不是很周全，确实局限性太大了，比如我批量保存的时候有可能一部分是插入，一部分是更新，这个时候也只能按照你说的这种方式了，比较而言还是orm框架持久层用起来的方便。 spring的这个两个方法看了源码感觉在设计上确实存在缺陷，灵活性不够,其实返回数据库执行的条数意义根本就不大 //其实我想的是这样的一个方法 /** * * @author BAOSJ * @date 2010-7-2 * @param sql * @param values * @param batchSize * @return * @throws Exception */ public List<Object> saveOrUpdateAll(String sql, List<Object[]> values, Integer batchSize) throws Exception { ResultSet rs = null; PreparedStatement ps = null; Connection conn = null; // 返回执行对象的id List<Object> ids = new ArrayList<Object>(); try { conn = getConnection(); conn.setAutoCommit(false); ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS); for (int i = 0; i < values.size(); i++) { Object[] objects = values.get(i); for (int j = 0; j < objects.length; j++) { ps.setObject(i + 1, objects[j]); } if (i % batchSize == 0 || values.size() <= batchSize) { ps.executeBatch(); rs = ps.getGeneratedKeys(); int c = 0; while (rs.next()) { ids.add(rs.getObject(c)); c++; } } } conn.commit(); } catch (Exception e) { conn.rollback(); throw e; } finally { destroy(rs, ps, conn); } return ids; } //update源码 public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder) throws DataAccessException { Assert.notNull(generatedKeyHolder, "KeyHolder must not be null"); logger.debug("Executing SQL update and returning generated keys"); Integer result = (Integer) execute(psc, new PreparedStatementCallback() { public Object doInPreparedStatement(PreparedStatement ps) throws SQLException { int rows = ps.executeUpdate(); List generatedKeys = generatedKeyHolder.getKeyList(); generatedKeys.clear(); ResultSet keys = ps.getGeneratedKeys(); if (keys != null) { try { RowMapper rowMapper = getColumnMapRowMapper(); RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1); generatedKeys.addAll((List) rse.extractData(keys)); } finally { JdbcUtils.closeResultSet(keys); } } if (logger.isDebugEnabled()) { logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys"); } return new Integer(rows); } }); return result.intValue(); } //batchUpdate源码 public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException { if (logger.isDebugEnabled()) { logger.debug("Executing SQL batch update [" + sql + "]"); } return (int[]) execute(sql, new PreparedStatementCallback() { public Object doInPreparedStatement(PreparedStatement ps) throws SQLException { try { int batchSize = pss.getBatchSize(); InterruptibleBatchPreparedStatementSetter ipss = (pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter) pss : null); 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(); } return ps.executeBatch(); } else { List rowsAffected = new ArrayList(); for (int i = 0; i < batchSize; i++) { pss.setValues(ps, i); if (ipss != null && ipss.isBatchExhausted(i)) { break; } rowsAffected.add(new Integer(ps.executeUpdate())); } int[] rowsAffectedArray = new int[rowsAffected.size()]; for (int i = 0; i < rowsAffectedArray.length; i++) { rowsAffectedArray[i] = ((Integer) rowsAffected.get(i)).intValue(); } return rowsAffectedArray; } } finally { if (pss instanceof ParameterDisposer) { ((ParameterDisposer) pss).cleanupParameters(); } } } }); }