问题(What):
执行插入操作后,如何返回自动增长的ID?
解答(How):
用JdbcTemplate提供的update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)方法
假设有表A
CREATE TABLE TEST.A(
ID INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
NAME VARCHAR(6) NOT NULL);
其中
Field ID为主键且自动增长
根据update方法中的PreparedStatementCreator的实现方式,有两种写法
写法一:自己实现PreparedStatementCreator的createPreparedStatement方法
@Test
public void getGenedIds1() throws SQLException {
ApplicationContext context = new ClassPathXmlApplicationContext(
"webmvc-config.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean(JdbcTemplate.class);
final String sql = "INSERT INTO TEST.A(NAME) VALUES(666),(6666)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
return con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
//or return con.prepareStatement(sql, new String[]{"ID"});//ID是自动增长的自动
}
}, keyHolder);
System.out.println(i);//2
System.out.println(keyHolder.getKeyList());//[{ID=1}, {ID=2}]
}
写法二:使用Spring的PreparedStatementCreatorFactory类来实现PreparedStatementCreator的createPreparedStatement方法建议使用这种。
@Test
public void getGenedIds2() throws SQLException {
ApplicationContext context = new ClassPathXmlApplicationContext(
"webmvc-config.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean(JdbcTemplate.class);
final String sql = "INSERT INTO TEST.A(NAME) VALUES(666),(6666)";
KeyHolder keyHolder = new GeneratedKeyHolder();
PreparedStatementCreatorFactory p = new PreparedStatementCreatorFactory(sql);
p.setGeneratedKeysColumnNames(new String[]{"ID"});
//or p.setReturnGeneratedKeys(true);
int i = jdbcTemplate.update(p.newPreparedStatementCreator(new Object[]{}), keyHolder);
System.out.println(i);//2
System.out.println(keyHolder.getKey());//报错,插入单条记录才能调用,以Number类型返回数据
System.out.println(keyHolder.getKeys());//报错,插入单条记录才能调用,以Map类型返回数据
System.out.println(keyHolder.getKeyList());//[{ID=3}, {ID=4}]
}