How to Get The Inserted ID (Generated ID) in JDBC

In this tutorial we’ll see how to get the ID of the newly inserted record in the DB using JDBC. Getting the ID of the inserted record is useful in the scenario when you are using auto-generated ID in the table (Auto_increment, Sequence, Serial) and you want to insert records in the table having Primary Key – Foreign key relationship.

For example suppose there are two tables-

1. user_master with fields as id (PK), name
2. accounts with fields as id (PK), acct_number and user_id (FK referencing id of user_master)

You want to insert user record and then using that generated id of the user you want to make an entry in accounts where the same id is passed for the user_id column. In this scenario after inserting the user record you will want to get hold of the generated user id.

Getting inserted record’s ID JDBC

In order to get the ID of the inserted records you need to do the following-

1. While creating statement you need to pass the static field RETURN_GENERATED_KEYS which is defined in the Statement interface. This constant indicates that generated keys should be made available for retrieval.

     	connection.prepareStatement(insertUserSQL, Statement.RETURN_GENERATED_KEYS);
     

2. Call the getGeneratedKeys() method on the Statement object to retrieve any auto-generated keys created as a result of executing this Statement object. A ResultSet is returned when getGeneratedKeys() method is executed.

Getting inserted record’s ID Java Program

Here is an example showing the same scenario of inserting User record and then getting the generated ID and insert an account record passing the user_id as the retrieved ID.

private void insertUserAndAccountRecord(Connection connection, String name, int acctNum) throws SQLException{
  String insertUserSQL = "Insert into user_master (name) values (?)";
  String insertAccountSQL = "Insert into accounts (acct_number, user_id) values (?,?)";
  connection.setAutoCommit(false);
  int userId;
  PreparedStatement prepStmt = null;
  try {
    prepStmt = connection.prepareStatement(insertUserSQL, Statement.RETURN_GENERATED_KEYS);
    prepStmt.setString(1, name);
    prepStmt.executeUpdate();
    try (ResultSet generatedKeys = prepStmt.getGeneratedKeys()) {
      if (generatedKeys.next()) {
        userId = generatedKeys.getInt(1);
        System.out.println("UserId is- " + userId);
      }
      else {
        connection.rollback();
        throw new SQLException("User insertion has problema. No ID returned.");
      }
    }
    prepStmt.close();
    prepStmt = connection.prepareStatement(insertAccountSQL);
    prepStmt.setInt(1, acctNum);
    prepStmt.setInt(2, userId);
    prepStmt.executeUpdate();
    connection.commit();
  }finally{
    if(prepStmt != null){
      prepStmt.close();
    }
  }
}

That's all for this topic How to Get The Inserted ID (Generated ID) in JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

---

Related Topics

1. How to Install PostgreSQL on Windows
2. Types of JDBC Drivers
3. ResultSet Interface in Java-JDBC
4. PreparedStatement Interface in Java-JDBC
5. Spring NamedParameterJdbcTemplate Insert, Update And Delete Example

You may also like-

1. Java Reflection API Tutorial
2. Lambda Expressions in Java 8
3. Java Program to Get Current Date and Time
4. Difference Between Abstract Class And Interface in Java
5. Spring Transaction Management Example - @Transactional Annotation and JDBC
6. Spring Batch Processing Using JDBCTemplate batchUpdate() Method
7. Connection Pooling Using C3P0 Spring Example
8. Getting Substring in Python String