CODEWITHSUNDEEP
mysql
user2531590
user2531590

Reputation:

Getting last auto increment value in database

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?

Upvotes: 7

Views: 36823

Answers (6)

riswan
riswan

Reputation: 91

How about this?

SHOW TABLE STATUS FROM Database_Name LIKE 'TableName' ;

Upvotes: 1

Resad Indipa
Resad Indipa

Reputation: 161

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

Upvotes: 0

Martin Brož
Martin Brož

Reputation: 9

I use this:

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

Upvotes: 0

Aldi Unanto
Aldi Unanto

Reputation: 3682

How about this?

SELECT your_id FROM your_table ORDER BY your_id DESC LIMIT 1

Upvotes: 3

Bimalesh Jha
Bimalesh Jha

Reputation: 1504

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

Upvotes: 1

RiaD
RiaD

Reputation: 47620

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.

Upvotes: 12

Related Questions