JdbcTemplate 执行 insert into 操作后，获取自动递增的主键值。

转载自：Fetch Auto Generated Primary Key Value After Insert

Most of the time, the primary key is auto generated and once a row is inserted it is a common requirement to fetch auto generated primary key value after insert statement execution.So in this topic we will take a look into fetching that auto generated primary key once an insert statement is executed.We will implement this fetching of auto generated primary key while inserting through jdbctemplate and also namedparameterjdbctemplate using KeyHolder interface provided by spring.

KeyHolder Interface

KeyHolder Interface is for retrieving keys, typically used for auto-generated keys as potentially returned by JDBC insert statements.

Implementations of this interface can hold any number of keys. In the general case, the keys are returned as a List containing one Map for each row of keys.

Most applications only use on key per row and process only one row at a time in an insert statement. In these cases, just call getKey to retrieve the key. The returned value is a Number here, which is the usual type for auto-generated keys.

Fetch Auto Generated primary key with JdbcTemplate

Following is the implementation of an insert statement using jdbctemplate.

private final String INSERT_SQL = "INSERT INTO USERS(name,address,email) values(?,?,?)";

@Autowired
private JdbcTemplate jdbcTemplate;

public User create(final User user) {
    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getName());
            ps.setString(2, user.getAddress());
            ps.setString(3, user.getEmail());
            return ps;
        }
    }, holder);

    int newUserId = holder.getKey().intValue();
    user.setId(newUserId);
    return user;
}

Fetch Auto Generated primary key with NamedParameterJdbcTemplate

Following is the implementation using NamedParameterJdbcTemplate to fetch auto generated primary key value after insert statement execution.

private final String INSERT_SQL = "INSERT INTO USERS(name, address, email) values(:name,:address,:email)";

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public User create(final User user) {
    KeyHolder holder = new GeneratedKeyHolder();
        SqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("name", user.getName())
                .addValue("address", user.getAddress())
                .addValue("email", user.getEmail());
        namedParameterJdbcTemplate.update(INSERT_SQL, parameters, holder);
        user.setId(holder.getKey().intValue());
        return user;
    }