Get Auto-generated Id in Spring JDBC using SimpleJdbcInsert

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

The 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", "[email protected]", "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.

auto generated id using simplejdbcinsert in spring jdbc

Look into the console where the generated id is displayed.

Generated Id for New User: 7

Source Code

Download

Thanks for reading.

Related posts

Leave a Comment