java 当我通过 jdbctemplate 在表中插入记录时，如何获取自动递增的 id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12882874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get the autoincremented id when I insert a record in a table via jdbctemplate
提问by birdy
private void insertIntoMyTable (Myclass m) {
String query = "INSERT INTO MYTABLE (NAME) VALUES (?)";
jdbcTemplate.update(query, m.getName());
}
When the above query inserts a record, the IDcolumn in the table autoincrements.
当上述查询插入一条记录时，ID表中的列自动递增。
Is there a way to get this auto incremented ID back at the time of the insertion. So in this example the return value of my method would be int
有没有办法在插入时取回这个自动递增的 ID。所以在这个例子中，我的方法的返回值是int
回答by Anshu
Check this reference.You can use jdbcTemplate.update as:
检查此参考。您可以将 jdbcTemplate.update 用作：
EDITAdded imports as asked
编辑按要求添加了导入
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
following is the code usage:
以下是代码用法：
final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, name);
return ps;
}
},
keyHolder);
// keyHolder.getKey() now contains the generated key
回答by m.a.tomsik
I get id generated by database (MSSQL) after insert like below, imports:
插入后，我得到由数据库（MSSQL）生成的 id，如下所示，导入：
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
and the code snippet:
和代码片段：
final String INSERT_SQL = "INSERT INTO [table]\n"
+ " ([column_1]\n"
+ " ,[column_2])\n"
+ " VALUES\n" +
" (?, ?)";
Connection connection = jdbcTemplate.getDataSource().getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_INVOICE_SQL, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, "test 1");
preparedStatement.setString(2, "test 2");
preparedStatement.executeUpdate();
ResultSet keys = preparedStatement.getGeneratedKeys();
if (keys.next()) {
Integer generatedId = keys.getInt(1); //id returned after insert execution
}
回答by coseos
JdbcTemplate is at the core of Spring. Another option is to use SimpleJdbcInsert.
JdbcTemplate 是 Spring 的核心。另一种选择是使用SimpleJdbcInsert。
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
simpleJdbcInsert
.withTableName("TABLENAME")
.usingGeneratedKeyColumns("ID");
SqlParameterSource params = new MapSqlParameterSource()
.addValue("COL1", model.getCol1())
.addValue("COL2", model.getCol2());
Number number = simpleJdbcInsert.executeAndReturnKey(params);
You can still @Autowire jdbcTemplate. To me, this is more convenient than working with jdbcTemplate.update() method and KeyHolder to get the actual id.
你仍然可以@Autowire jdbcTemplate。对我来说，这比使用 jdbcTemplate.update() 方法和 KeyHolder 获取实际 id 更方便。
Example code snippet is tested with Apache Derbyand should work with the usual databases.
示例代码片段使用Apache Derby进行了测试，应该可以与常用数据库一起使用。
Use of Spring JPAis another option - if ORM is for you.
使用Spring JPA是另一种选择 - 如果 ORM 适合您。
