In this post I will show you how to get auto generated id of a newly inserted row in table. Spring provides an easy way to get this auto-generated key using KeyHolder which is supported Spring JDBC 3 onward. I am going to use PreparedStatement to get the auto generated id value.
For most of the records we use auto generated Id value on database side to avoid inconsistency while inserting value for the primary key Id field. So here I am not only going to show how to insert the auto generated Id value for the primary field, but also I am going to show you how to return it immediately after the record gets inserted into database table.
Related Posts:
Prerequisites
Eclipse 2020-06, At least JDK 1.8, Gradle 6.5.1, Maven 3.6.3, Spring Core/Context/Jdbc 5.2.8, MySQL 8.0.17
Project Setup
Create either maven or gradle based project in Eclipse. The name of the project is spring-jdbc-simplejdbcinsert-auto-generated-id.
If you are creating gradle based project then use below build.gradle script:
plugins {
id 'java-library'
}
repositories {
jcenter()
}
dependencies {
implementation 'org.springframework:spring-core:5.2.8.RELEASE'
implementation 'org.springframework:spring-context:5.2.8.RELEASE'
implementation 'org.springframework:spring-jdbc:5.2.8.RELEASE'
implementation 'mysql:mysql-connector-java:8.0.17'
//required for JDK 9 or above
implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}If you are creating maven based project then you can use below pom.xml file:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>spring-jdbc-preparedstatement-auto-generated-id</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>at least 1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<!--required only if jdk 9 or higher version is used-->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.4.0-b180830.0359</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>${java.version}</source>
<target>${java.version}</target>
</configuration>
</plugin>
</plugins>
</build>
</project>MySQL Table
We need to create a table called user under roytuts database in MySQL server.
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`phone` int unsigned NOT NULL,
`address` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Database Configuration
I will use annotation based configuration and we need to create appropriate beans for working with database.
Create a jdbc.properties file under classpath folder src/main/resources to put database settings.
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/roytuts
jdbc.username=root
jdbc.password=rootThe required configuration Java class is given below:
package com.roytuts.spring.jdbc.preparedstatement.auto.generated.id.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
@PropertySource("classpath:jdbc.properties")
@ComponentScan(basePackages = "com.roytuts.spring.jdbc.preparedstatement.auto.generated.id")
public class Config {
@Autowired
private Environment environment;
@Bean
public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(environment.getRequiredProperty("jdbc.driverClassName"));
dataSource.setUrl(environment.getRequiredProperty("jdbc.url"));
dataSource.setUsername(environment.getRequiredProperty("jdbc.username"));
dataSource.setPassword(environment.getRequiredProperty("jdbc.password"));
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate;
}
}Model Class
We need to create a POJO class that will map table and Java class together.
package com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id.model;
public class User {
private Integer id;
private String name;
private String email;
private String phone;
private String address;
public User() {
}
public User(String name, String email, String phone, String address) {
this.name = name;
this.email = email;
this.phone = phone;
this.address = address;
}
//getters and setters
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", email=" + email + ", phone=" + phone + ", address=" + address
+ "]";
}
}DAO Class
DAO class is where perform database operations. For my example I am going to insert a user record and return the auto generated id value.
JdbcTemplate.update() method needs the object of PreparedStatementCreator and KeyHolder. Here KeyHolder object has been created by GeneratedKeyHolder. And finally keyHolder.getKey() is returning the required id.
package com.roytuts.spring.jdbc.preparedstatement.auto.generated.id.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import com.roytuts.spring.jdbc.preparedstatement.auto.generated.id.model.User;
@Component
public class UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private final String SQL_INSERT_USER = "insert into `user`(`name`,`email`,`phone`,`address`) values(?, ?, ?, ?)";
public long addUser(final User user) {
final PreparedStatementCreator psc = new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
final PreparedStatement ps = connection.prepareStatement(SQL_INSERT_USER,
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getName());
ps.setString(2, user.getEmail());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
return ps;
}
};
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(psc, keyHolder);
long insertedId = keyHolder.getKey().longValue();
return insertedId;
}
}Testing the Application
I am going to use annotation based application context for testing the application.
package com.roytuts.spring.jdbc.preparedstatement.auto.generated.id;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.roytuts.spring.jdbc.preparedstatement.auto.generated.id.config.Config;
import com.roytuts.spring.jdbc.preparedstatement.auto.generated.id.dao.UserDao;
import com.roytuts.spring.jdbc.preparedstatement.auto.generated.id.model.User;
public class SpringPreparedStatementAutoGeneratedIdApp {
public static void main(String[] args) {
ApplicationContext context = new AnnotationConfigApplicationContext(Config.class);
UserDao dao = context.getBean(UserDao.class);
System.out.println("Generated Id for New User: "
+ dao.addUser(new User("Soumitra", "soumitra@email.com", "234567890", "Earth")));
((ConfigurableApplicationContext) context).close();
}
}Execute the above class and verify the database table for new record which just got inserted. You will see a new row is inserted.
Look into the console where the generated id is displayed.
Generated Id for New User: 33That’s all about how to get auto generated id in Spring using PreparedStatement and KeyHolder.
Source Code
Thanks for reading.