How to Get the Insert Id in Jdbc

How to get the insert ID in JDBC?

If it is an auto generated key, then you can use Statement#getGeneratedKeys() for this. You need to call it on the same Statement as the one being used for the INSERT. You first need to create the statement using Statement.RETURN_GENERATED_KEYS to notify the JDBC driver to return the keys.

Here's a basic example:

public void create(User user) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);
    ) {
        statement.setString(1, user.getName());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getEmail());
        // ...

        int affectedRows = statement.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                user.setId(generatedKeys.getLong(1));
            }
            else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }
    }
}

Note that you're dependent on the JDBC driver as to whether it works. Currently, most of the last versions will work, but if I am correct, Oracle JDBC driver is still somewhat troublesome with this. MySQL and DB2 already supported it for ages. PostgreSQL started to support it not long ago. I can't comment about MSSQL as I've never used it.

For Oracle, you can invoke a CallableStatement with a RETURNING clause or a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) directly after the INSERT in the same transaction to obtain the last generated key. See also this answer.

How to get the Generated insert ID in JDBC?

Not all drivers support the version of getGeneratedKeys() as shown in the linked answer. But when preparing the statement, you can also pass the list of columns that should be returned instead of the "flag" Statement.RETURN_GENERATED_KEYS (and passing the column names works more reliably in my experience)

Additionally: as javaBeginner pointed out correctly, your usage of a prepared statement is wrong. The way you do it, will still leave you wide open to SQL injection.

// run the INSERT
String sql = "INSERT INTO product values(?,?,?)";
pst = cn.prepareStatement(sql, new String[] {"PRODUCT_ID"} );
pst.setString(1, name);
pst.setInt(2, quantity);
pst.setInt(3, price);
pst.executeUpdate();

// now get the ID:
ResultSet rs = pst.getGeneratedKeys();
if (rs.next()) {
   long productId = rs.getLong(1);
}

Note that the column name passed to the call is case-sensitive. For Oracle the column names are usually uppercase. If you are using e.g. Postgres you would most probably need to pass new String[] {"product_id"}

MySQL & Java - Get id of the last inserted value (JDBC)

Wouldn't you just change:

numero = stmt.executeUpdate(query);

to:

numero = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

Take a look at the documentation for the JDBC Statement interface.

Update: Apparently there is a lot of confusion about this answer, but my guess is that the people that are confused are not reading it in the context of the question that was asked. If you take the code that the OP provided in his question and replace the single line (line 6) that I am suggesting, everything will work. The numero variable is completely irrelevant and its value is never read after it is set.

How to get the last inserted id in jdbc with SQLite3?

The function last_insert_rowid() returns:

the ROWID of the last row insert from the database connection which
invoked the function

So, if you closed the connection with which you did the last insertion and run the query with another connection you will not get the rowid of the last row inserted.

Also if there are multiple tables in your database and you do insertions in more than 1 of them with the same connection, you will have to call last_insert_rowid() after the last insertion in each table to get the rowid of the last row inserted in each table.

When you use MAX(id) in a query and id is defined as INTEGER PRIMARY KEY you get the max id of the table and not necessarily the rowid of the last row inserted, because:

If you ever delete rows or if you ever create a row with the maximum
possible ROWID, then ROWIDs from previously deleted rows might be
reused when creating new rows and newly created ROWIDs might not be in
strictly ascending order.

The only way to be sure that MAX(id) will return the rowid of the last row inserted is if you have defined id with the keyword AUTOINCREMENT also:

id INTEGER PRIMARY KEY AUTOINCREMENT

because in this case:

The ROWID chosen for the new row is at least one larger than the
largest ROWID that has ever before existed in that same table. If the
table has never before contained any data, then a ROWID of 1 is used.

Finally, it is common practice to refer to columns by their names or aliases, so instead of:

SELECT MAX(id) FROM temp

you should use:

SELECT MAX(id) AS max_id FROM temp

so that you can access the result of the query by the alias max_id:

final Integer id = rs.getInt("max_id");

How to get last inserted ID as Long in java mysql

try with the following code snippet:

ResultSet rs= statement.getGeneratedKeys();
if (rs.next()) 
{
   System.out.println("Last Inserted ID = "+rs.getLong(1));
}

Here's the full code:

        Connection connection = null;
        Statement statement = null;
        try {
            String sql = "INSERT INTO testTable(name) VALUES('Anonym')";
            connection = DriverManager.getConnection(DB_URL, USER, PASS);
            statement = connection.createStatement();
            long lastInsertedID = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs= statement.getGeneratedKeys();
            if (rs.next()) 
            {
              System.out.println("Last Inserted ID = "+rs.getLong(1));
            }    

        } catch (Exception e) {
            e.printStackTrace();
        }

Retrieving the last inserted id in Oracle using JDBC

I found some documentation about this. It's for 11g, but the situation probably won't be better for 10g.

The proximal cause of your error is probably the limitation that:

You need to access the ResultSet object returned from getGeneratedKeys method by position only

It seems the Oracle driver also requires you to identify the key column in order for it to retrieve the key column instead of just the ROWID. Sample code for this is included in the linked documentation.

---

Related Topics