Get Auto-generated Id in Spring JDBC using SimpleJdbcInsert

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

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

You will also find a new record has been inserted into the database table.

auto generated id using simplejdbcinsert

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *