When working with relational databases in Java, it’s common to perform insert operations and then retrieve the ID of the inserted record. This is especially important when dealing with auto-incremented primary keys in tables. Let us delve into understanding how to use JDBC to get the insert ID after adding a new record to a database.
1. Retrieving Insert IDs
After inserting a record into a PostgreSQL database, you might need to retrieve the ID of the newly inserted record. Before we delve into how to achieve this, let’s first set up PostgreSQL on Docker and create a sample employees table.
1.1 Setting up a database on Docker
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.
-- Remember to change the password – docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number – 5432 and you can connect with the Dbeaver GUI tool for connecting to the server.
2. Creating A PostgreSQL Table
To understand this article, you need to create a table. Here is an example of how to create a table in PostgreSQL:
CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(100) );
This SQL command creates a table named employees with three columns: id, which is an auto-incrementing primary key, name to store employee name, and department to store employee’s department.
2. Using the getGeneratedKeys() Method
The getGeneratedKeys() method in Java is used to retrieve the auto-generated keys (such as primary key values) created as a result of executing an SQL INSERT statement. This is particularly useful when you want to obtain the ID of a newly inserted record in a database table, such as when you’re inserting a record into a PostgreSQL database.
When you execute an INSERT statement using a PreparedStatement or Statement, the database might generate certain keys automatically (like an auto-incremented primary key). To retrieve these keys, you can call the getGeneratedKeys() method on the Statement object. This method returns a ResultSet object containing the generated keys.
Here’s how to use getGeneratedKeys().
2.1 Maven Dependencies
First, you need to add the PostgreSQL JDBC driver dependency to your Maven project:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.0</version>
</dependency>
2.2 Code Example
Below is a Java code that demonstrates how to use the getGeneratedKeys() method to retrieve the insert ID:
package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class App {
public static void main(String[] args) {
// Database connection details
String url = "jdbc:postgresql://localhost:5432/your_database";
String user = "your_user";
String password = "your_password";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// Array of employee data to be inserted
String[][] employees = {
{"John Doe", "Engineering"},
{"Jane Smith", "Marketing"},
{"Alice Johnson", "Sales"}
};
try {
// Establishing connection to the database
System.out.println("Connecting to the database...");
conn = DriverManager.getConnection(url, user, password);
System.out.println("Connection established.");
// SQL statement for inserting employee data
String sql = "INSERT INTO employees (name, department) VALUES (?, ?)";
pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
// Loop through each employee and insert their data
for (String[] employee : employees) {
pstmt.setString(1, employee[0]);
pstmt.setString(2, employee[1]);
// Execute the SQL statement
System.out.println("Executing SQL statement for " + employee[0] + "...");
int affectedRows = pstmt.executeUpdate();
System.out.println("SQL statement executed for " + employee[0] + ".");
// Retrieve and print the generated key if insertion was successful
if (affectedRows > 0) {
rs = pstmt.getGeneratedKeys();
if (rs.next()) {
int insertedId = rs.getInt(1);
System.out.println("Inserted record ID for " + employee[0] + ": " + insertedId);
}
} else {
System.out.println("No rows affected for " + employee[0] + ".");
}
}
} catch (SQLException e) {
// Handle SQL exceptions
System.out.println("SQL Exception occurred.");
e.printStackTrace();
} finally {
// Close all resources
try {
if (rs != null) {
rs.close();
System.out.println("ResultSet closed.");
}
if (pstmt != null) {
pstmt.close();
System.out.println("PreparedStatement closed.");
}
if (conn != null) {
conn.close();
System.out.println("Connection closed.");
}
} catch (SQLException e) {
// Handle exceptions during resource closing
System.out.println("Exception occurred while closing resources.");
e.printStackTrace();
}
}
}
}
2.2.1 Code Explanation
The code defines a:
- Connection: The
DriverManager.getConnection()method is used to establish a connection to the PostgreSQL database. - SQL Statement: The SQL
INSERTstatement is defined with placeholders (?) for the values to be inserted. - PreparedStatement: The
prepareStatement()method is called with theRETURN_GENERATED_KEYSflag, indicating that we want to retrieve the auto-generated keys after the insert operation. - Executing the Insert: The
executeUpdate()method executes the insert operation and returns the number of affected rows. - Retrieving Generated Keys: If the insert was successful (i.e.,
affectedRows > 0), thegetGeneratedKeys()method is called to retrieve the generated key(s). In this case, we are retrieving the generatedidof the inserted record. - ResultSet: The
ResultSetobject contains the generated keys, and we retrieve the first key using thegetInt(1)method. - Cleanup: The
finallyblock ensures that resources such asResultSet,PreparedStatement, andConnectionare properly closed.
2.3 Code Output
The following output of the above code will be logged on the IDE console:
Connecting to the database... Connection established. Executing SQL statement for John Doe... SQL statement executed for John Doe. Inserted record ID for John Doe: 1 Executing SQL statement for Jane Smith... SQL statement executed for Jane Smith. Inserted record ID for Jane Smith: 2 Executing SQL statement for Alice Johnson... SQL statement executed for Alice Johnson. Inserted record ID for Alice Johnson: 3 ResultSet closed. PreparedStatement closed. Connection closed.
3. Conclusion
The getGeneratedKeys() method is a powerful tool for obtaining auto-generated keys, like primary key values, after an INSERT operation. It simplifies the process of working with newly inserted records in a database, making it easier to manage and utilize these records in your application.
