How to get a value from the last inserted row? [duplicate]

Solution 1:

With PostgreSQL you can do it via the RETURNING keyword:

PostgresSQL - RETURNING

INSERT INTO mytable( field_1, field_2,... )
VALUES ( value_1, value_2 ) RETURNING anyfield

It will return the value of "anyfield". "anyfield" may be a sequence or not.

To use it with JDBC, do:

ResultSet rs = statement.executeQuery("INSERT ... RETURNING ID");
rs.next();
rs.getInt(1);

Solution 2:

See the API docs for java.sql.Statement.

Basically, when you call executeUpdate() or executeQuery(), use the Statement.RETURN_GENERATED_KEYS constant. You can then call getGeneratedKeys to get the auto-generated keys of all rows created by that execution. (Assuming your JDBC driver provides it.)

It goes something along the lines of this:

Statement stmt = conn.createStatement();
stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet keyset = stmt.getGeneratedKeys();

Solution 3:

If you're using JDBC 3.0, then you can get the value of the PK as soon as you inserted it.

Here's an article that talks about how : https://www.ibm.com/developerworks/java/library/j-jdbcnew/

Statement stmt = conn.createStatement();
// Obtain the generated key that results from the query.
stmt.executeUpdate("INSERT INTO authors " +
                   "(first_name, last_name) " +
                   "VALUES ('George', 'Orwell')",
                   Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if ( rs.next() ) {
    // Retrieve the auto generated key(s).
    int key = rs.getInt(1);
}

Solution 4:

Since PostgreSQL JDBC driver version 8.4-701 the PreparedStatement#getGeneratedKeys() is finally fully functional. We use it here almost one year in production to our full satisfaction.

In "plain JDBC" the PreparedStatement needs to be created as follows to make it to return the keys:

statement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);

You can download the current JDBC driver version here (which is at the moment still 8.4-701).