插入行并获取生成的ID

Question

我正在尝试使用Spring的JdbcTemplate类将一行数据插入到名为transaction的MySQL表中，并获取生成的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;
}

但是，在调用getJdbcTemplate().update时，会抛出以下异常：

java.sql.SQLException: 未请求生成的键。您需要在Statement.executeUpdate()或Connection.prepareStatement()中指定Statement.RETURN_GENERATED_KEYS。

我可以在不放弃JdbcTemplate的情况下插入行并获取生成的ID吗？ 我正在使用Spring 2.5，MySQL 5.5.27和MySQL Connector 5.1.26。

- Dónal

---

使用MySQL，我认为您需要调用last_insert_id()。请参见这里。 - Sotirios Delimanolis

Answer 1

有一种更简单的方法可以实现这种行为：

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);

然后您创建一个名为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();

- GerritCap

---

如果你在回答中提供映射值的示例会更好。从当前回答来看，如果没有进一步查看外部文档并确定键和值所代表的内容，就不太容易理解使用Map<String, Object>。如果能提供这些信息，那么这个回答将值得点赞。 - Hazok

我认为很明显，如果我提到一个包含每个列名值的Map参数，并且我提到了列名，那么键应该是字符串，而值可以是任何映射到SQL类型的对象。顺便说一句：查看文档从来不会有坏处，不要相信任何特定的答案并且不要忘记再次核对它。 - GerritCap

很明显，检查文档并不相信特定的答案是很重要的。同样明显的是，在Stackoverflow上提出一个好问题需要提供所有必要的细节，不留任何模糊之处。对于第一次使用Java JDBC堆栈的人来说，这些东西显然不是那么明显的 ;) - Hazok

Answer 2

只需按以下方式准备您的语句即可。

PreparedStatement ps = connection.prepareStatement(
                           INSERT_TRAN_SQL, Statement.RETURN_GENERATED_KEYS);

在这里间接使用的基础JDBC驱动程序（通过Spring的JdbcTemplate）需要一个提示，表明您想要检索生成的键。这可以在准备PreparedStatement时完成。

connection.prepareStatement(strSQL, Statement.RETURN_GENERATED_KEYS);

执行Statement时，或者在执行期间

statement.executeUpdate(strSQL, Statement.RETURN_GENERATED_KEYS);

这也是你的 java.sql.SQLException 指向的。

- Ravi K Thapliyal

---

你好，我有一个问题，如果查询成功，它会返回生成的ID，但是如果查询无法执行，它会返回什么，以便我可以在我的代码中优雅地处理错误？ - Sajib Acharya

Answer 3

您可以像步骤1一样获取下一个序列号，然后将其传递到步骤2中的插入语句中：

1-

Integer nextSeq = (Integer) getJdbcTemplate().queryForObject(
        "select SEQ_CUSTOMER_ID.nextVal from dual", new Object[] {}, Integer.class);

2-

getJdbcTemplate().update(
        "INSERT INTO customer "
        + "(CUST_ID, NAME, UPDATED) VALUES (?, ?, ?)",
        new Object[] { nextSeq ,customer.getName(),
                customer.getUpdated() });

- Eddy Joseph