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

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.