Using Prepared Statement, How I Return the Id of the Inserted Row

Using Prepared Statement, how I return the id of the inserted row?

After calling the execute() method on the Prepared Statement, the id of the insert row will be in the insert_id attribute.

$pstm->execute();
$pstm->insert_id;

How to get the id of last inserted row using preparedstatement?

ps.executeUpdate(ps.RETURN_GENERATED_KEYS)

You invented that. It doesn't exist.

ps = con.prepareStatement(Insert_Credential);

That doesn't tell the PreparedStatement to return generated keys either. You need this:

ps = con.prepareStatement(Insert_Credential, Statement.RETURN_GENERATED_KEYS);

Get last inserted ID in prepared statement

You are using lastInsertId() correctly according to the PDO:lastInsertId() documentation

        $statement = $conn->prepare($query);
        $statement->execute($rowdata);
        $id = $conn->lastInsertId();

Some potential reasons why it is not working:

1. Is this code within a TRANSACTION? If so, you need to COMMIT the transaction after the execute and before the lastInsertId()
2. Since you INSERT IGNORE there is the potential that the INSERT statement is generating an error and not inserting a row so lastInsertId() could potentially be empty.

Hope this helps!

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.

---

Related Topics