Java JDBC: Get ID of Inserted Record in Database

In many database operations, it's common to insert a record and then need to know the ID that was automatically generated by the database. This tutorial will demonstrate how to retrieve the ID of an inserted record using JDBC with a MySQL database.

Prerequisites

• MySQL database installed.
• MySQL JDBC driver added to your project.
• Basic understanding of Java and JDBC.

Steps

1. Set Up Dependencies
2. Establish a Database Connection
3. Create a Table
4. Insert a Record and Retrieve the ID

1. Set Up Dependencies

Ensure you have the MySQL JDBC driver in your classpath. 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>

2. Establish a Database Connection

First, we need to establish a connection to the MySQL server using the DriverManager class.

3. Create a Table

We will create a simple table for our example. Here is the SQL statement to create a books table:

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL
);

4. Insert a Record and Retrieve the ID

We'll use the PreparedStatement to insert a record into the books table and retrieve the auto-generated ID.

Example Code

Below is the complete example code demonstrating how to insert a record into a books table and retrieve the ID of the inserted record.

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

public class InsertRecordExample {

    // JDBC URL, username, and password of MySQL server
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/your_database?useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            System.out.println("Connected to the database");

            // Insert a record and get the ID
            int insertedId = insertRecord(connection, "The Great Gatsby", "F. Scott Fitzgerald");
            System.out.println("Inserted record ID: " + insertedId);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int insertRecord(Connection connection, String title, String author) throws SQLException {
        String insertSQL = "INSERT INTO books (title, author) VALUES (?, ?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertSQL, PreparedStatement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, title);
            pstmt.setString(2, author);

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Inserting record failed, no rows affected.");
            }

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return generatedKeys.getInt(1);
                } else {
                    throw new SQLException("Inserting record failed, no ID obtained.");
                }
            }
        }
    }
}

Explanation

1. MySQL Connection:

   • DriverManager.getConnection(JDBC_URL, USER, PASSWORD) is used to establish a connection to the MySQL server.

2. Inserting a Record:

   • The insertRecord() method inserts a record into the books table using a PreparedStatement.
   • PreparedStatement.RETURN_GENERATED_KEYS is used to tell the JDBC driver to return the auto-generated keys.

3. Retrieving the Generated Key:

   • After executing the executeUpdate() method, getGeneratedKeys() is called to retrieve the generated key.
   • The key is retrieved from the ResultSet and returned by the insertRecord() method.

Output

Running the code will produce output similar to the following:

Connected to the database
Inserted record ID: 1

Conclusion

Retrieving the ID of an inserted record in a MySQL database using JDBC is straightforward. By using the RETURN_GENERATED_KEYS flag with a PreparedStatement, you can easily obtain the auto-generated key after executing an insert statement. This approach is essential for operations where the generated key is needed for further processing.