Java 插入行并获取生成的 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19095994/
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
insert row and get generated ID
提问by Dónal
I'm trying to use Spring's JdbcTemplateclass to insert a row into a MySQL table named transactionand get the generated ID. The relevant code is:
我正在尝试使用 Spring 的JdbcTemplate类在名为的 MySQL 表中插入一行transaction并获取生成的 ID。相关代码是：
public Transaction insertTransaction(final Transaction tran) {
// Will hold the ID of the row created by the insert
KeyHolder keyHolder = new GeneratedKeyHolder();
getJdbcTemplate().update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(INSERT_TRAN_SQL);
ps.setString(1, tran.getTransactionType().toString());
Date sqlDate = new Date(tran.getDate().getTime());
ps.setDate(2, sqlDate);
ps.setString(3, tran.getDescription());
return ps;
}
}, keyHolder);
tran.setId(keyHolder.getKey().longValue());
return tran;
}
But the following exception is thrown by the call to getJdbcTemplate().update
但是以下异常是由调用引发的 getJdbcTemplate().update
java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().
java.sql.SQLException：未请求生成的密钥。您需要将 Statement.RETURN_GENERATED_KEYS 指定为 Statement.executeUpdate() 或 Connection.prepareStatement()。
Can I insert the row and get the generated ID, without abandoning JdbcTemplate? I'm using Spring 2.5, MySQL 5.5.27 and MySQL Connector 5.1.26.
我可以在不放弃的情况下插入行并获取生成的 IDJdbcTemplate吗？我使用的是 Spring 2.5、MySQL 5.5.27 和 MySQL 连接器 5.1.26。
采纳答案by Ravi Thapliyal
Just prepare your Statementas follows
只需准备Statement如下
PreparedStatement ps = connection.prepareStatement(
INSERT_TRAN_SQL, Statement.RETURN_GENERATED_KEYS);
The underlying JDBC driver (used indirectly through the Spring's JdbcTemplatehere) requires a hint that you would like to retrieve the generated keys. This can be done either while preparing a PreparedStatementas
底层 JDBC 驱动程序（通过 Spring 在JdbcTemplate这里间接使用）需要提示您希望检索生成的键。这可以在准备PreparedStatement作为
connection.prepareStatement(strSQL, Statement.RETURN_GENERATED_KEYS);
or, at the time of executing a Statementas
或者，在执行Statementas 时
statement.executeUpdate(strSQL, Statement.RETURN_GENERATED_KEYS);
This is what your java.sql.SQLExceptionis pointing at as well.
这也是你java.sql.SQLException所指的。
回答by GerritCap
There is an easier way to get that behaviour:
有一种更简单的方法来获得这种行为：
protected JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insert;
this.jdbcTemplate = new JdbcTemplate(this.databaseSetup.getDataSource());
this.insert = new SimpleJdbcInsert(this.jdbcTemplate).withTableName(this.tableName).usingGeneratedKeyColumns(this.pkColumn);
Then you create a Map called parameters which conmtains the values for each column name in your table and insert a record like this:
然后创建一个名为 parameters 的 Map，它包含表中每个列名的值，并插入如下记录：
final Map<String, Object> parameters = new HashMap<>();
parameters.put("empName", employee.getName()); // store the String name of employee in the column empName
parameters.put("dept", employee.getDepartment()); // store the int (as Integer) of the employee in the column dept
final Number key = this.insert.executeAndReturnKey(parameters);
final long pk = key.longValue();
回答by Eddy Joseph
You can retrieve the next sequence number like in step 1, then it can passed in the insert statement as in the step 2:
您可以像在步骤 1 中那样检索下一个序列号，然后可以像在步骤 2 中那样在插入语句中传递它：
1-
1-
Integer nextSeq = (Integer) getJdbcTemplate().queryForObject(
"select SEQ_CUSTOMER_ID.nextVal from dual", new Object[] {}, Integer.class);
2-
2-
getJdbcTemplate().update(
"INSERT INTO customer "
+ "(CUST_ID, NAME, UPDATED) VALUES (?, ?, ?)",
new Object[] { nextSeq ,customer.getName(),
customer.getUpdated() });
