How to Get Last Inserted ID from MySQL?

In the world of Java programming and MySQL database management systems, you might be querying your MySQL database using a JDBC connection. In this article, we will look at fetching the latest ID after an insertion. Using Java's java.sql package, we will look at some examples of how to access the last inserted ID from MySQL.

In these examples, we'll learn how to smoothly retrieve the last inserted ID in Java applications, improving data management. Using Java's java.sql package, these techniques empower developers to optimize interactions with MySQL databases efficiently.

How to get the Last Inserted ID from MySQL in Java using java.sql

When any MySQL table includes an AUTO_INCREMENT column, MySQL generates the ID automatically. Here, we want to know the value of the AUTOINCREMENT column for the last INSERT statement executed. To get the last inserted ID from MySQL using Java, you create the JDBC connection and use conn.prepareStatement() method to which you must pass an additional parameter Statement.RETURN_GENERATED_KEYS.

Syntax:

<conn_obj>.prepareStatement(<sql-query>, Statement.RETURN_GENERATED_KEYS);

Parameters:

<sql-query>: An SQL query in string datatype.

Statement.RETURN_GENERATED_KEYS: A parameter to indicate that query returns id of last inserted record.

Returns:

An object of PreparedStatement class

NOTE: We can only get the last inserted id of the record for the tables from MySQL that contain an `id` column, which is of auto-increment type. The last inserted ID is not changed even if you run the UPDATE query to any column of that table.

Java.sql and JDBC

Java Database Connectivity (JDBC) is a standard Java API for interacting with relational databases. The java.sql package, part of the Java Standard Edition (SE), provides classes and interfaces to facilitate database operations.

Example 1: The table contains an `id` column, which is of auto-increment type

Let us assume that we have a database named "GFG" in which we have a table named "users". The schema for the table "users" is as follows:

CREATE TABLE users
(
    id int AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) not null, 
    email varchar(255) not null,
    age int not null
);

Now, we will insert two records into this table. The Java code below establishes a JDBC connection using connection parameters like username, password, database URL, and database name. The code contains an `InsertRecord()` method, which takes the JDBC connection object and the values for the insert query. The `InsertRecord()` method creates a PreparedStatement object and executes the query. To get the id of the last inserted record, it extracts the generatedKeys() from the executed query and returns the id.

Code:

// importing required packages
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GFG {

    // database connection paramaters
    private static final String DATABASE_URL
        = "localhost:3306";
    private static final String DATABASE = "gfg";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123";

    // a function to insert record into MySQL database
    // it returns the last inserted id for the record
    public static int InsertRecord(Connection conn,
                                   String name,
                                   String email, int age)
        throws SQLException
    {
        int id = 0;
        String query
            = "INSERT INTO users(name, email, age) values(?,?,?)";
        // create a PreparedStatement object
        PreparedStatement ps = conn.prepareStatement(
            query, Statement.RETURN_GENERATED_KEYS);
        // set the query paramaters/values
        ps.setString(1, name);
        ps.setString(2, email);
        ps.setString(3, Integer.toString(age));
        // execute the insert query
        ps.executeUpdate();
        // get the auto-generated keys
        ResultSet rs = ps.getGeneratedKeys();
        if (rs.next()) {
            id = rs.getInt(1);
        }
        return id;
    }

    public static void main(String[] args)
    {
        try {
            // create a connection object
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://" + DATABASE_URL + "/"
                    + DATABASE,
                USERNAME, PASSWORD);

            // insert 1st record into table
            int lastInsertedId1 = InsertRecord(
                conn, "Girish", "[email protected]",
                24);
            // print the id of this 1st record inserted into
            // table
            System.out.println(
                "Last Inserted Record Id was : "
                + lastInsertedId1);

            // insert 2nd record into table
            int lastInsertedId2 = InsertRecord(
                conn, "Medha", "[email protected]",
                23);
            // print the id of this 2nd record inserted into
            // table
            System.out.println(
                "Last Inserted Record Id was : "
                + lastInsertedId2);
            conn.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Last Inserted Record Id was : 1
Last Inserted Record Id was : 2

Output:

Data inserted using JDBC:

Example 2: The table DOES NOT contain an `id` column, which is of auto-increment type

CREATE TABLE users
(
    name varchar(255) not null, 
    email varchar(255) not null PRIMARY KEY,
    age int not null
);

Again, we use the same code mentioned in the example above, but this time, we won't get the last inserted id for any record inserted into the `users` table since, now there is no AUTOINCREMENT type column in `users` table.

Last Inserted Record Id was : 0
Last Inserted Record Id was : 0

Output:

Data inserted using JDBC:

Conclusion

Mastering the retrieval of the last inserted ID in MySQL with Java's java.sql package opens doors to enhanced database management. By following the steps outlined in this article, you can use them in your applications with this efficient approach to handling and utilizing newly inserted data.

As developers continue to optimize database interactions, the seamless retrieval of last-inserted IDs becomes a cornerstone, allowing for agile and precise management of data in Java applications. In the dynamic landscape of software development, these skills contribute to creating more resilient and efficient systems, ultimately enhancing the user experience.