Introduction
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 executeAndReturnKey() method of SimpleJdbcInsert which is supported Spring JDBC 3 onwards.
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-simplejdbcinsert-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.
I am using jdbc.properties file which is kept under src/main/resources classpath folder.
The content of the peroperties file is given below:
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.simplejdbcinsert.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.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
@PropertySource("classpath:jdbc.properties")
@ComponentScan(basePackages = "com.roytuts.spring.jdbc.simplejdbcinsert.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;
}
@Bean
public SimpleJdbcInsert simpleJdbcInsert(JdbcTemplate jdbcTemplate) {
return new SimpleJdbcInsert(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.
package com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id.dao;
import java.util.HashMap;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Component;
import com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id.model.User;
@Component
public class UserDao {
@Autowired
private SimpleJdbcInsert simpleJdbcInsert;
public long addUser(final User user) {
simpleJdbcInsert.withTableName("user").usingGeneratedKeyColumns("id");
Map<String, Object> parameters = new HashMap<String, Object>(4);
parameters.put("name", user.getName());
parameters.put("email", user.getEmail());
parameters.put("phone", user.getPhone());
parameters.put("address", user.getAddress());
Number insertedId = simpleJdbcInsert.executeAndReturnKey(parameters);
return insertedId.longValue();
}
}Testing the Application
We are going to use annotation based application context for testing the application.
package com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id.config.Config;
import com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id.dao.UserDao;
import com.roytuts.spring.jdbc.simplejdbcinsert.auto.generated.id.model.User;
public class SpringAutoGeneratedIdApp {
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: 7Source Code
Thanks for reading.