How to Retrieve Auto-Generated Keys in Jdbc

In this post, we will learn how to use JDBC to retrieve an auto-generated key. We use MySQL Database, it has AUTO_INCREMENT attribute generates a unique ID for new rows.

Checkout complete JDBC tutorial at Java Database Connectivity Tutorial.

1. Maven MySQL Dependency

Add the following MySQL dependency to your maven project:

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

2. Database Setup

Let's create a database and name it as "demo":

mysql> create database demo;

Use the following statement to create Students table in an above-created database:

CREATE TABLE Students
(
 Id BIGINT PRIMARY KEY AUTO_INCREMENT, 
 Name VARCHAR(100)
);

MySQL's AUTO_INCREMENT attribute generates a unique ID for new rows. The following example shows how we can use JDBC to retrieve an auto-generated key value.

3. MySQL Java Auto-generated Keys Example

In the example, we add a new student record to a table that has its primary key auto-incremented by MySQL. We retrieve the generated ID.

package net.javaguides.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JdbcAutoGenKey {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/demo?useSSL=false";
        String user = "root";
        String password = "root";

        String studentName = "Ramesh Fadatare";
        String sql = "INSERT INTO Students(Name) VALUES(?)";

        try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement preparedStatement = con.prepareStatement(sql,
            Statement.RETURN_GENERATED_KEYS)) {

            preparedStatement.setString(1, studentName);
            preparedStatement.executeUpdate();

            try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {

                if (resultSet.first()) {

                    System.out.printf("The ID of new student : %d", resultSet.getLong(1));
                }
            }

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(JdbcAutoGenKey.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
}

Output:

The ID of new student : 1

As the first step, we have to pass the Statement.RETURN_GENERATED_KEYS to the prepareStatement() method:

try (Connection con = DriverManager.getConnection(url, user, password);
        PreparedStatement pst = con.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS)) {

Then we retrieve the generated key(s) with the getGeneratedKeys() method:

try (ResultSet rs = pst.getGeneratedKeys()) {

Since we have only one insert statement, we use first() to navigate to the value:

if (rs.first()) {
    
    System.out.printf("The ID of new author: %d", rs.getLong(1));
}

Checkout complete JDBC tutorial at Java Database Connectivity Tutorial.