Fetch Auto Generated Primary Key Value After Insert

By Dhiraj Ray, 04 February,2017  

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

Conclusion

I hope this article served you that you were looking for. If you have anything that you want to add or share then please share it below in the comment section.

References: Spring Boot JDBC reference doc KeyHolder Spring Doc

Suggest more topics in suggestion section or write your own article and share with your colleagues.

Is this page helpful to you? Please give us your feedback below. We would love to hear your thoughts on these articles, it will help us improve further our learning process.

Further Reading: