Auro-generated Id
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 I will 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
Java 19, Maven 3.6.3/3.8.5, Spring Boot 3.1.0, MySQL 8.0.17/8.0.31
Project Setup
Create a maven based project in your favorite IDE or tool. The following pom.xml file can be used for your project:
<?xml version="1.0" encoding="UTF-8"?>
<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>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>19</maven.compiler.source>
<maven.compiler.target>19</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.0</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</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>8.0.31</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.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
MySQL Table
You need to create a table called, for example, user under roytuts database in MySQL server.
CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;
CREATE TABLE IF NOT EXISTS `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`phone` int unsigned NOT NULL,
`address` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `user` (`id`, `name`, `email`, `phone`, `address`) VALUES
(7, 'Soumitra', 'soumitra@email.com', 234567890, 'Earth');
Database Configuration
I am using application.properties file under src/main/resources class path folder for database properties configurations.
The content of the peroperties file is given below:
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=noneThe required configuration Java class is given below and the following class has DataSource, JdbcTemplate and SimpleJdbcInsert beans defined in it.
@Configuration
@PropertySource("classpath:application.properties")
public class Config {
@Autowired
private Environment environment;
@Bean
public DataSource dataSource() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driverClassName"));
ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
return ds;
}
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean
public SimpleJdbcInsert simpleJdbcInsert(JdbcTemplate jdbcTemplate) {
return new SimpleJdbcInsert(jdbcTemplate);
}
}Model Class
You need to create a POJO class that will map table and Java class together.
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.
@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();
}
}Spring Boot Main Class
The following spring boot main class is all to start the spring boot application.
@SpringBootApplication
public class App implements CommandLineRunner {
@Autowired
private UserDao userDao;
public static void main(String[] args) {
SpringApplication.run(App.class, args);
}
@Override
public void run(String... args) throws Exception {
System.out.println("Generated Id for New User: "
+ userDao.addUser(new User("Soumitra", "soumitra@email.com", "234567890", "Earth")));
}
}Testing the Auto Generated Id
Execute the above main class and you will see the following output in the console:
Generated Id for New User: 7You will also find a new record has been inserted into the database table.
