How to Get Auto-Generated ID in Spring JDBC

By Arvind Rai, August 03, 2022

On this page we will learn to fetch auto-generated id in Spring JDBC. In Spring JDBC, we can use JdbcTemplate methods and SimpleJdbcInsert methods to execute the SQL query and return the auto-generated key as KeyHolder.

KeyHolder

The KeyHolder 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 the JdbcTemplate.update method declaration from Spring doc.

int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException

1. The method issues an update statement using a 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

The SimpleJdbcInsert 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)

Example:
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: person

CREATE TABLE `person` (
	`id` INT(5) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`age` INT(3) NOT NULL,
	PRIMARY KEY (`id`)
)

PersonDAO.java

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

application.properties

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

Person.java

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

pom.xml

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

MyApplication.java

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 KeyHolder
Class JdbcTemplate
Class SimpleJdbcInsert

Download Source Code

how-to-get-auto-generated-id-in-spring-jdbc.zip

POSTED BY

ARVIND RAI

LEARN MORE

JAVA SPRING BOOT ANGULAR Angular Material NGRX JAVASCRIPT ANDROID THYMELEAF FREEMARKER Hibernate Struts 2 Testing MAVEN APACHE API JACKSON API JUNIT 5