[Java] 必學!! 取得當前新增資料的索引值

當在MySQL使用AUTO_INCREMENT產生索引的值，如何取得新增的索引值呢?

MySQL提供了LAST_INSERT_ID()解決這個問題!!

SELECT LAST_INSERT_ID();


如果要用JDBC實作的話只需要加入Statement.RETURN_GENERATED_KEYS，
執行getGeneratedKeys()即可輕鬆取得!!

參考以下範例程式碼：

public void create(User user) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet generatedKeys = null;

    try {
        connection = database.getConnection();
        preparedStatement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setValue(1, user.getName());
        preparedStatement.setValue(2, user.getPassword());
        preparedStatement.setValue(3, user.getEmail());
        // ...

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

        generatedKeys = preparedStatement.getGeneratedKeys();
        if (generatedKeys.next()) {
            user.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Creating user failed, no generated key obtained.");
        }
    } finally {
        close(connection, preparedStatement, generatedKeys);
    }
}

之前常用的MS-SQL則提供@@IDENTITY方法來解決!!

可參考之前用C#的範例程式 "MSSQL 取得目前新增資料的索引(PK)"

Reference:

2008-03-11 20:42在資料庫中取得自動增值索引產生的數值