How to Retrieve Auto-Generated Keys in JDBC

When you insert a new row into a table with an auto-increment primary key, the database generates the key value for the new row. JDBC provides convenient methods for retrieving these auto-generated keys.

Table of Contents

1. Introduction
2. Setting Up the MySQL Database
3. JDBC Driver and Dependencies
4. Retrieving Auto-Generated Keys
5. Example Code
6. Conclusion

1. Introduction

When working with relational databases, it's common to have tables with auto-incremented primary keys. After inserting a row into such a table, you may need to retrieve the generated key for further processing. JDBC provides mechanisms to retrieve these auto-generated keys.

2. Setting Up the MySQL Database

First, ensure that MySQL is installed and running. Create a new database and table for this example.

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL
);

3. JDBC Driver and Dependencies

To connect to MySQL from Java, you need the MySQL JDBC driver. Download it from the MySQL website.

Add the downloaded JAR file to your project's build path. 

If you are using Maven, add the following dependency to your pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

4. Retrieving Auto-Generated Keys

Key Methods

• Statement.RETURN_GENERATED_KEYS: Used to indicate that auto-generated keys should be returned.
• PreparedStatement.getGeneratedKeys(): Retrieves the auto-generated keys.

Steps to Retrieve Auto-Generated Keys

1. Establish a connection to the database.
2. Create a PreparedStatement with the RETURN_GENERATED_KEYS option.
3. Execute the insert statement.
4. Retrieve the generated keys using getGeneratedKeys() method.
5. Process the generated keys.

5. Example Code

Retrieving Auto-Generated Keys Example

package com.example.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class RetrieveGeneratedKeysExample {

    private static final String URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    private static final String INSERT_EMPLOYEE_SQL = "INSERT INTO employees (name, email, department) VALUES (?, ?, ?);";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {

            // Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);

            preparedStatement.setString(1, "Ravi Kumar");
            preparedStatement.setString(2, "ravi.kumar@example.com");
            preparedStatement.setString(3, "IT");

            int affectedRows = preparedStatement.executeUpdate();

            if (affectedRows > 0) {
                try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        long id = generatedKeys.getLong(1);
                        System.out.println("Inserted record's ID: " + id);
                    }
                }
            }

        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Explanation

1. PreparedStatement with RETURN_GENERATED_KEYS: The PreparedStatement is created with the RETURN_GENERATED_KEYS option to indicate that auto-generated keys should be returned.
2. Execute Update: The executeUpdate() method is called to insert the record.
3. Retrieve Generated Keys: The getGeneratedKeys() method retrieves the generated keys as a ResultSet.
4. Process the Generated Keys: The ResultSet is used to retrieve the generated key value.

6. Conclusion

Retrieving auto-generated keys in JDBC is straightforward with the RETURN_GENERATED_KEYS option and the getGeneratedKeys() method. This allows you to obtain the primary key values generated by the database after inserting new rows. This tutorial covered the steps to set up the MySQL database, write Java code to insert a record, and retrieve the auto-generated key. Using these techniques, you can manage database operations efficiently in your Java applications.