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

• Get Auto-generated Id using SimpleJdbcInsert in Spring JDBC

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-preparedstatement-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 TABLE `user` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Database Configuration

Create an application.properties file under class path folder src/main/resources to write database configurations.

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 for the bean configuration is given below:

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

}

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.

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.

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

}

Spring Boot Main Class

The following spring boot main class will deploy the application into embedded Tomcat server.

@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 Auto Generated Id

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

That’s all about how to get auto generated id in Spring using PreparedStatement and KeyHolder.

Source Code

Download