Tuesday, 5 March 2019

Spring + JdbcTemplate + How to retrieve auto-generated keys

🚀 Enjoyed the tutorial? Support "Ram N Java" by Subscribing to our YouTube Channel for more Java deep dives!

How to Retrieve Auto-Generated Keys using Spring JdbcTemplate

When working with relational databases, it is very common to have primary keys that are automatically generated (like AUTO_INCREMENT in MySQL or SERIAL in PostgreSQL). After performing an insert operation, you often need that specific ID to use it in subsequent logic.

The Challenge

Normally, a standard update or insert query returns only the number of rows affected. To get the actual key that the database just created, we need a more specialized approach within the Spring framework.

The Solution: GeneratedKeyHolder

Spring JDBC provides a handy interface called KeyHolder and its primary implementation, GeneratedKeyHolder. This object acts as a container for the keys returned by the database.

Step-by-Step Implementation

Create a KeyHolder: Instantiate a new GeneratedKeyHolder().
Use PreparedStatementCreator: Instead of a simple SQL string, you provide a callback that defines how the statement is created and specifies which columns are auto-generated.
Execute the Update: Pass both the PreparedStatementCreator and the KeyHolder to the jdbcTemplate.update() method.
Extract the Key: Access the ID using keyHolder.getKey().

Why is this useful?

This method is cleaner and more reliable than trying to query for the "last inserted ID" manually, which can cause issues in high-traffic environments where multiple users are inserting data at the same time.

Continue Learning

Check out these other Spring JDBC tutorials from the channel:

Click here to watch on Youtube:
https://www.youtube.com/watch?v=rbi3TkO0q5U&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge:

Spring + JdbcTemplate + How to retrieve auto-generated keys

Employee.sql

CREATE DATABASE org_db;

CREATE TABLE `employee` (

  `EMPLOYEE_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(100) NOT NULL,

  `AGE` INT(10) NOT NULL,

  `SALARY` INT(10) DEFAULT NULL,

  PRIMARY KEY (`EMPLOYEE_ID`)

) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

pom.xml

<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/maven-v4_0_0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.ram.core</groupId>

    <artifactId>SpringDemo</artifactId>

    <packaging>jar</packaging>

    <version>1.0-SNAPSHOT</version>

    <name>SpringDemo</name>

    <url>http://maven.apache.org</url>

    <properties>

        <spring.version>5.0.5.RELEASE</spring.version>

    </properties>

    <dependencies>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <scope>test</scope>

        </dependency>

        <!-- Spring 5 dependencies -->

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-core</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-context</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-jdbc</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <!-- MySQL database driver -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>8.0.11</version>

        </dependency>

    </dependencies>

</project>

EmployeeDAO.java

package com.ram.employee.dao;

public interface EmployeeDAO

{

    public void retriveAutoGeneratedKey();

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import org.springframework.jdbc.core.PreparedStatementCreator;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.jdbc.support.GeneratedKeyHolder;

import org.springframework.jdbc.support.KeyHolder;

import com.ram.employee.dao.EmployeeDAO;

public class EmployeeDAOImpl

        extends JdbcDaoSupport implements EmployeeDAO

{

    public void retriveAutoGeneratedKey()

    {

        final String INSERT_SQL = "INSERT INTO EMPLOYEE "

                + "(NAME, AGE,SALARY) VALUES (?, ?, ?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        getJdbcTemplate().update(new PreparedStatementCreator()

        {

            public PreparedStatement createPreparedStatement(

                    Connection connection) throws SQLException

            {

                PreparedStatement ps = connection.prepareStatement(

                        INSERT_SQL, new String[] { "id" });

                ps.setString(1, "Rob");

                ps.setInt(2, 12);

                ps.setInt(3, 9090);

                return ps;

            }

        }, keyHolder);

        System.out.println(

                "Auto generated Key is = " + keyHolder.getKey());

    }

}

applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">

    <bean id="dataSource"

        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="com.mysql.jdbc.Driver" />

        <property name="url" value="jdbc:mysql://localhost:3306/org_db" />

        <property name="username" value="root" />

        <property name="password" value="root" />

    </bean>

    <bean id="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">

        <property name="dataSource" ref="dataSource" />

    </bean>

</beans>

App.java

package com.ram.core;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ram.employee.dao.EmployeeDAO;

public class App

{

    public static void main(String[] args)

    {

        ApplicationContext context = new ClassPathXmlApplicationContext(

                "applicationContext.xml");

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

        employeeDAO.retriveAutoGeneratedKey();

    }

}

Output:

Feb 20, 2019 10:36:51 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@736e9adb: startup date [Wed Feb 20 10:36:51 IST 2019]; root of context hierarchy

Feb 20, 2019 10:36:52 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [applicationContext.xml]

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Feb 20, 2019 10:36:52 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Wed Feb 20 10:36:53 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Auto generated Key is = 10