Getting last auto increment value in database

Question

I am trying to retrieve the auto increment value of last inserted data in mySQL. Here is my code:

public int getAutoIncrementProductID() {
    ResultSet rs = null;
    DBController db = new DBController();
    db.getConnection();
    int autoIncKeyFromFunc = -1;
    rs = db.readRequest("SELECT LAST_INSERT_ID()");
    try {
        if (rs.next()) {
            autoIncKeyFromFunc = rs.getInt(1);
            System.out.println("AUTO ID IS " + autoIncKeyFromFunc);
            rs.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    db.terminate();
    return autoIncKeyFromFunc;
}

However, these codes keep returning me 0 value although the auto increment column in database is keep increasing. It just wont get the auto increment value of last inserted data. Anybody could help?

Answer 1

You should use LAST_INSERT_ID() after you insert something.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0).

Source

You may also try

SELECT max(id) FROM tableName

But it will not suppose deleted rows.

Answer 2

How about this?

SELECT your_id FROM your_table ORDER BY your_id DESC LIMIT 1

Answer 3

I think since you are using Jdbc there is another way to get generated key is to use API connection. createStatement (Statement.RETURN_GENERATED_KEYS); Look at this thread PreparedStatement with Statement.RETURN_GENERATED_KEYS

Answer 4

How about this?

SHOW TABLE STATUS FROM Database_Name LIKE 'TableName' ;

Answer 5

I use this:

SELECT auto_increment + 1 AS NEXT_ID
FROM   `information_schema`.`tables`
WHERE  table_name = "table_name"
       AND table_schema = "database_name"

Answer 6

Be Careful when using mysql_insert_id() specially if you have multiple connections to the Database. Because It doesn't get the value of the query you've inserted. it gets the latest id of the table. It may be a row another query has inserted. Only use this function if you access Database in one connection.

If you want to get the id of the query you've inserted, try to select the row with an unique value you've inserted with that query. Ex : finding the user id of a user with his email address.

SELECT id from users where emailaddress='[email protected]' LIMIT 1

More details here : https://www.php.net/manual/en/function.mysql-insert-id.php