java insert自增长id_执行插入操作后，如何返回自动增长的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}]

}