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; }
Other Interesting Posts Spring 5 Features and Enhancements Insert Record in DB with Spring Boot JdbcTemplate Insert Record in DB with Spring Boot Namedparameter Jdbctemplate Working with Spring Boot Jdbctemplate Working with Spring Boot NamedParameter Jdbctemplate Execute Stored Procedure in Spring Jdbc Spring Security Hibernate Example with complete JavaConfig Securing REST API with Spring Security Basic Authentication Websocket spring Boot Integration without STOMP with complete JavaConfig Spring Boot Spring MVC Example Spring Boot Thymeleaf Example Spring Boot MVC with Jsp Example
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.