How do you retrieve auto-generated keys using JDBC?

Auto-generated keys are unique values generated by the database automatically whenever a new record is inserted into a table. Retrieving auto-generated keys is a common requirement in many database applications. The JDBC API provides a way to retrieve auto-generated keys using the getGeneratedKeys() method.

Here’s an example code snippet that demonstrates how to retrieve auto-generated keys in JDBC:

    // create a connection to the database
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "user", "password");
    
    // create a prepared statement with the SQL statement that inserts a new row into the table
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO mytable(name, age) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
    
    // set the values to be inserted
    stmt.setString(1, "John");
    stmt.setInt(2, 25);
    
    // execute the statement
    stmt.executeUpdate();
    
    // retrieve the auto-generated keys
    ResultSet rs = stmt.getGeneratedKeys();
    
    // iterate over the result set and print the keys
    while (rs.next()) {
        int id = rs.getInt(1);
        System.out.println("Generated key: " + id);
    }
    
    // close the result set, statement and connection
    rs.close();
    stmt.close();
    conn.close();

In this example, we first create a connection to the database using the DriverManager class. Then, we create a prepared statement that inserts a new row into the table "mytable" and specifies that we want to retrieve the auto-generated keys using the Statement.RETURN_GENERATED_KEYS constant.

Next, we set the values of the parameters in the prepared statement and execute the update using the executeUpdate() method. After that, we retrieve the auto-generated keys using the getGeneratedKeys() method, which returns a ResultSet containing the generated keys.

Finally, we iterate over the result set using the next() method and retrieve the generated keys using the getInt() method, printing each key to the console. Finally, we close the result set, the statement and the connection to the database.

It’s worth noting that the specific syntax for retrieving auto-generated keys may vary depending on the database and JDBC driver you’re using. However, the general approach outlined in this example should work in most cases.