How to Get the Insert ID in JDBC?
Last Updated :
05 Apr, 2024
In Java, for getting the insert ID in JDBC, we have built-in methods like getGeneratedKeys(). This method can be able to get the inserted ID in JDBC. Here, in this article, we have used PreparedStatement for insertion purposes and the SQL Injection attack free also. After performing an insertion operation on the database, we can retrieve the ID which is automatically generated. This ID is known as insert ID and often it is used as the primary key in the database.
In this article, we will learn about how to get the inserted ID from the table in the JDBC. Below we have provided the related example with the output image for reference.
Steps to Get the Inserted ID
- Establish Database Connection
- Prepare SQL Statement
- Set Parameter Values
- Execute Insert Operation
- Retrieve Auto-generated Keys
- Retrieve Insert ID
Get the Insert ID in JDBC
- We have getGeneratedKeys() method in the PreparedStatement interface.
- This method can be able to get the generated keys while inserting data into a table and the ID is often used as the primary key in the table.
- For getting insert ID in JDBC, first, we need to connect with the database by using JDBC connection properties like domain name, username, password and database name we want to connect.
- Once the connection is successful, then we take sample data and insert by using PreparedStatement.
- Then, we have used ResultSet. It is used to run the SQL queries.
- Then we hold the insert ID and finally, it prints that ID as output.
We have created a table with a name book with four columns id, author, name, and price. Below is the Table for reference.
Table:
Java Program to get the insert ID in JDBC
Below is the code implementation to get the generated keys in JDBC.
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class InsertedIdExample {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");
// Create a PreparedStatement with the SQL statement and RETURN_GENERATED_KEYS option
String sql = "INSERT INTO book (author, name, price) VALUES (?, ?, ?)";
PreparedStatement stmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
// Set values for the parameters
stmt.setString(1, "Gupta");
stmt.setString(2, "My Book");
stmt.setDouble(3, 29.99);
// Execute the insert operation
int rowsAffected = stmt.executeUpdate();
if (rowsAffected > 0) {
// Retrieve the auto-generated keys (insert ID)
ResultSet generatedKeys = stmt.getGeneratedKeys();
if (generatedKeys.next()) {
int insertId = generatedKeys.getInt(1);
System.out.println("Record inserted successfully with ID: " + insertId);
} else {
System.out.println("Failed to retrieve insert ID.");
}
} else {
System.out.println("No records inserted.");
}
con.close();
} catch (ClassNotFoundException | SQLException e)
{
e.printStackTrace();
}
}
}
Output
Below we can see the console output:
Database Table Output:
Explanation of the above Program:
- First, we establish a connection to the MySQL database by using DriverManager.getConnection.
- Then we prepare an insert SQL statement with placeholders for parameters using the PreparedStatement interface.
- After this, we set values for the parameters using set method with related data type.
- Then we execute the insert operation using executeUpdate.
- After this, we retrieve the auto-generated keys using getGeneratedKeys() method of the PreparedStatement.
- Finally, we retrieve the insert ID from ResultSet and print it out.
Share your thoughts in the comments
Please Login to comment...