Home Spring Boot Spring JdbcTemplate - get Auto-generated Keys

Spring JdbcTemplate – get Auto-generated Keys

Examples on how to get auto-generated primary key ids using JdbcTemplate and SimpleJdbcInsert in Spring Jdbc.

1. Getting Auto-Generated Key using JdbcTemplate

1.1. User Table

CREATE TABLE `USER` (
		`ID` bigint(11) NOT NULL AUTO_INCREMENT,
		`USERNAME` varchar(45) NOT NULL,
		`PASSWORD` varchar(45) NOT NULL,
		`CREATEDTIME` datetime NOT NULL,
		`UPDATEDTIME` datetime DEFAULT NULL,
		`USERTYPE` varchar(45) NOT NULL,
		`DATEOFBIRTH` date NOT NULL,
		PRIMARY KEY (`ID`),
		UNIQUE KEY `USER_NAME_UNIQUE` (`USERNAME`)
	);

1.2. To retrieve auto-generated primery keys generated by database using JdbcTemplate, update() with PreparedStatementCreator is a convenient method. Provide PreparedStatementCreator as first argument and KeyHolder as second argument. KeyHolder holds the generated primary key, getKey() returns the value.

public Long save(User user) {
		String insertSql = "INSERT INTO `USER` (USERNAME, PASSWORD, CREATEDTIME, UPDATEDTIME, USERTYPE, DATEOFBIRTH)"
				+ " VALUES(?,?,?,?,?,?)";

		KeyHolder keyHolder = new GeneratedKeyHolder();

		jdbcTemplate.update(connection -> {

			PreparedStatement ps = connection.prepareStatement(insertSql, new String[] { "ID" });
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getPassword());
			ps.setDate(3, new java.sql.Date(user.getCreatedTime().getTime()));
			ps.setDate(4, user.getUpdatedTime() == null ? null : new java.sql.Date(user.getUpdatedTime().getTime()));
			ps.setString(5, user.getUserType().toString());
			ps.setDate(6, new java.sql.Date(user.getDateofBirth().getTime()));

			return ps;

		}, keyHolder);

		return keyHolder.getKey().longValue();
	}

2. Getting Auto-Generated Key using SimpleJdbcInsert

If you are working with SimpleJdbcInsert to insert the records into database, it also provides support to get auto-generated keys like JdbcTemplate.

2.1. Initialize SimpleJdbcInsert : To initialize, need to provide DataSource or JdbcTemplate instance to the SimpleJdbcInsert.

@Repository
public class UserSimpleJdbcInsert {

	private JdbcTemplate jdbcTemplate;
	private SimpleJdbcInsert simpleJdbcInsert;

	@Autowired
	public UserSimpleJdbcInsert(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		this.simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
	}
      
      // .....
}

2.2. SimpleJdbcInsert example. In addition to the table name provide primary key column name which you wants to return as auto-generated key and then call executeAndReturnKey(). If the all of column names and corresponding java class fields has the exactly same names BeanPropertySqlParameterSource can be used to provide parameters, otherwise has to provide parameters individually for each column.

	public Long saveWithSimpleJdbcInsert(User user) {
		
		simpleJdbcInsert.withTableName("USER").usingGeneratedKeyColumns("ID");

		Number id = simpleJdbcInsert.executeAndReturnKey(new BeanPropertySqlParameterSource(user));
		
		return id.longValue();
	}

2.3. SimpleJdbcInsert example with parameters.

	public Long saveWithSimpleJdbcInsert(User user) {
		
		simpleJdbcInsert.withTableName("USER").usingGeneratedKeyColumns("ID");

		MapSqlParameterSource params = new MapSqlParameterSource()
				.addValue("USERNAME", user.getUserName())
				.addValue("PASSWORD", user.getPassword())
				.addValue("CREATEDTIME", user.getCreatedTime())
				.addValue("UPDATEDTIME", user.getUpdatedTime())
				.addValue("USERTYPE", user.getUserType())
				.addValue("DATEOFBIRTH", user.getDateofBirth());
		
		Number id = simpleJdbcInsert.executeAndReturnKey(params);
		
		return id.longValue();
	}

3. Conclusion

We have covered couple of examples on how to get auto-generated keys by database using JdbcTemplate and SimpleJdbcinsert when insert records into database in Spring Jdbc.

Checkout source code at GitHub.

Other Spring JDBC Examples :

  1. Spring JdbcTemplate – Stored Procedure
  2. Spring JdbcTemplate – Query
  3. Spring JdbcTemplate – Pagination
  4. Spring JdbcTemplate – Batch Update
  5. Spring JdbcTemplate – IN Clause
  6. Spring Boot – Loading Initial Data

4. References

  1. Spring Document
  2. PreparedStatementCreator

Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories