当我通过jdbctemplate在表中插入一条记录时，如何获得自动递增id

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 ID column 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

3 个解决方案

#1

Check this reference. You can use jdbcTemplate.update as:

检查这个引用。您可以使用jdbcTemplate。更新为:

EDIT Added 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

#2

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
    }

#3

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 Derby and should work with the usual databases.

示例代码片段使用Apache Derby测试，应该与常用数据库一起使用。

Use of Spring JPA is another option - if ORM is for you.

使用Spring JPA是另一个选项——如果ORM是为您准备的。

#1