执行插入操作后，如何返回自动增长的ID(Java)

问题(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}]
}