How to get ID of the newly inserted record in database

Details: Written by Nam Ha Minh; Last Updated on 05 June 2019 | Print Email

This post provides code example that gets the value of the newly inserted record in database using JDBC.

Some database operations, especially those involving parent-child relationships (one-to-many), you may want to insert a record into the parent table first, then into the child table. But the records in the child table must have a foreign key that refers to the parent table’s primary key. In such case, you need a technique that retrieves the value of the primary key of the last record inserted (usually auto-incremented by the database engine). JDBC provides such mechanism but implementation is slightly different for different database systems.

For MySQL and Java Derby database, use the following code:

        String sql = "YOUR INSERT STATEMENT HERE";

        PreparedStatement ps = conn.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS);

        ps.execute();

        ResultSet rs = ps.getGeneratedKeys();
        int generatedKey = 0;
        if (rs.next()) {
            generatedKey = rs.getInt(1);
        }

        System.out.println("Inserted record's ID: " + generatedKey);

For Oracle database, use the following code:

        PreparedStatement statement = conn.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS);

        statement.execute();

        PreparedStatement ps = connection
                .prepareStatement("select your_table_id_sequence.currval from dual");

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            generatedKey = (int) rs.getLong(1);
        }

        System.out.println("Inserted record's ID: " + generatedKey);

Note: for Oracle database, you should create a sequence for the table’s primary key.

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.