ConcretePage.comHow to Get Auto-Generated ID in Spring JDBC
August 03, 2022
JdbcTemplate methods and SimpleJdbcInsert methods to execute the SQL query and return the auto-generated key as KeyHolder.
KeyHolder
TheKeyHolder is an interface for retrieving auto-generated keys. The KeyHolder is returned by JDBC insert statements. Generally keys are returned as a List containing one Map for each row of keys.
The
KeyHolder has following methods.
getKey() : Retrieve the first item from the first map.
getKeyAs(Class<T> keyType) : Retrieve the first item from the first map of given key type.
getKeyList() : Return a reference to the list that contains the keys.
getKeys() : Retrieve the first map of keys.
Using JdbcTemplate.update
Find theJdbcTemplate.update method declaration from Spring doc.
int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException
PreparedStatementCreator.
2. Generated keys will be put into the given
KeyHolder.
3. The method returns number of affected rows.
Example :
String sql = "insert into person (name, age) values (?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pst.setString(1, p.getName());
pst.setInt(2, p.getAge());
return pst;
}, keyHolder);
int id = keyHolder.getKey().intValue();
Using SimpleJdbcInsert
TheSimpleJdbcInsert provides easy insert capabilities for a table. All we need to provide is the name of the table and a Map containing the column names and the column values. Find the SimpleJdbcInsert methods that execute the query and return the auto-generated key.
Number executeAndReturnKey(Map<String,?> args) Number executeAndReturnKey(SqlParameterSource parameterSource) KeyHolder executeAndReturnKeyHolder(Map<String,?> args) KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource)
Here we are retrieving auto-generated key using
JdbcTemplate.update and KeyHolder.
Map<String, Object> params = new HashMap<String, Object>();
params.put("name", p.getName());
params.put("age", p.getAge());
KeyHolder keyHolder = simpleJdbcInsert
.withTableName("person")
.usingColumns("name", "age")
.usingGeneratedKeyColumns("id")
.withoutTableColumnMetaDataAccess()
.executeAndReturnKeyHolder(params);
int id = keyHolder.getKey().intValue();
Complete Example
Table: personCREATE TABLE `person` ( `id` INT(5) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `age` INT(3) NOT NULL, PRIMARY KEY (`id`) )
package com.concretepage;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
@Repository
public class PersonDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert simpleJdbcInsert;
@Autowired
private void setSimpleJdbcInsert(JdbcTemplate jdbcTemplate) {
simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
}
public void addPersonUsingJdbcTemplate(Person p) {
String sql = "insert into person (name, age) values (?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pst.setString(1, p.getName());
pst.setInt(2, p.getAge());
return pst;
}, keyHolder);
int id = keyHolder.getKey().intValue();
p.setId(id);
System.out.println(id);
}
public void addPersonUsingSimpleJdbcInsert(Person p) {
Map<String, Object> params = new HashMap<String, Object>();
params.put("name", p.getName());
params.put("age", p.getAge());
KeyHolder keyHolder = simpleJdbcInsert
.withTableName("person")
.usingColumns("name", "age")
.usingGeneratedKeyColumns("id")
.withoutTableColumnMetaDataAccess()
.executeAndReturnKeyHolder(params);
int id = keyHolder.getKey().intValue();
p.setId(id);
System.out.println(id);
}
}
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage spring.datasource.username=root spring.datasource.password=cp spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
package com.concretepage;
public class Person {
private int id;
private String name;
private int age;
public Person(String name, int age) {
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public int getAge() {
return age;
}
@Override
public String toString() {
return id + " - " + name + " - " + age;
}
}
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.5</version> </dependency> </dependencies>
package com.concretepage;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
@SpringBootApplication
public class MyApplication {
public static void main(String[] args) {
ApplicationContext ctx = SpringApplication.run(MyApplication.class, args);
PersonDAO personDAO = ctx.getBean(PersonDAO.class);
Person p1 = new Person("Mohan", 25);
personDAO.addPersonUsingJdbcTemplate(p1);
System.out.println(p1);
Person p2 = new Person("Shiva", 30);
personDAO.addPersonUsingSimpleJdbcInsert(p2);
System.out.println(p2);
}
}
References
Interface KeyHolderClass JdbcTemplate
Class SimpleJdbcInsert
