How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?

Question

I got a very common question when I was using Spring JDBCTemplate, I want to get the ID value after I inserted a new data record into database, this ID value will be referred to another related table. I tried the following way to insert it, but I always return 1 rather than its real unique ID. (I use MySQL as the database)

public int insert(BasicModel entity) {
    String insertIntoSql = QueryUtil.getInsertIntoSqlStatement(entity);

    log.info("SQL Statement for inserting into: " + insertIntoSql);

    return this.jdbcTemplate.update(insertIntoSql);
}

score 15 · Accepted Answer · answered Jan 26 '13 at 14:01

JdbcTemplate.update() returns:

the number of rows affected

Which is always 1 for INSERT statement. Different databases support generated key extraction in different ways, but most JDBC drivers abstract this and JdbcTemplate supports this. Quoting 12.2.8 Retrieving auto-generated keys

An update() convenience method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard; see Chapter 13.6 of the specification for details.

Basically you need this much more verbose statement:

final String insertIntoSql = QueryUtil.getInsertIntoSqlStatement(entity);
KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(
  new PreparedStatementCreator() {
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      return connection.prepareStatement(insertIntoSql, new String[] {"id"});
    }
  }, keyHolder);

return keyHolder.getKey().intValue();

Hi Tomasz, I try your method, but didnt work in my place. I work on it on long time but cant get the solution. Kindly advise http://stackoverflow.com/questions/24567633/get-back-the-primary-key-value-after-insertion-to-db2-table-by-keyholder — Panadol Chong, Jul 04 '14 at 09:14

score 5 · Answer 2 · answered Feb 26 '21 at 14:52

@panadol-chong, a small modification was necessary for @tomasz-nurkiewicz's code to work here.

final String SQL = "INSERT INTO ... RETUNING id";

KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(SQL, 
                           Statement.RETURN_GENERATED_KEYS);

    return ps;
}, keyHolder);

return keyHolder.getKey().intValue();

The main difference is the Statement.RETURN_GENERATED_KEYS.

score 5 · Answer 3 · edited Aug 01 '23 at 16:06

You can use @Tomasz Nurkiewicz answer and it works for sure, but you don't need to : Spring offers SimpleJdbcInsert which "provides meta-data processing to simplify the code needed to construct a basic insert statement".

@Repository
public class UserDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertIntoUser;

    @Autowired
    public UserDao(DataSource datasource) {
        jdbcTemplate = new JdbcTemplate(datasource);
        insertIntoUser = new SimpleJdbcInsert(jdbcTemplate).withTableName("user").usingGeneratedKeyColumns("id_user");
    }

    public Number insertUser(User u) {
        final Map<String, Object> parameters = new HashMap<>();
        parameters.put("name", u.getName());

        return insertIntoUser.executeAndReturnKey(parameters);
    }
}

The "Number" returned by insertUser is the id_user generated for the user insertion.

Documentation: [Simplifying JDBC Operations with the SimpleJdbc Classes](https://docs.spring.io/spring-framework/reference/data-access/jdbc/simple.html) — David Tonhofer, Aug 01 '23 at 16:02

How to get generated ID after I inserted into a new data record in database using Spring JDBCTemplate?

3 Answers3

Linked