How to use LAST_INSERT_ID() Equivalent of @@IDENTITY in MySql

Question

In MySql, I want to use equivalent of SQL @@IDENTITY and I found this LAST_INSERT_ID(). I found in search that it work fines even if there are different sessions exist. I want to find last ID,in current session. I am using following code.

public static void main(String[] args) {

    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = null;
        conn = DriverManager.getConnection("jdbc:mysql://localhost/fuel","root", "");
        System.out.print("Database is connected !\n");

        Statement st = conn.createStatement();
        String response = "<food>";
        String insertQuery = "INSERT INTO transactions(user_input,fuel_response,execution_time)"+
                             "VALUES(?,?,?)";
        PreparedStatement prepareStatementObj = conn.prepareStatement(insertQuery);         
        prepareStatementObj.setString(1, "I drank a babyfood juice apple.");
        prepareStatementObj.setString(2, response);
        prepareStatementObj.setInt(3, 4500);            

        prepareStatementObj.execute();

        String queryForId = "SELECT LAST_INSERT_ID() FROM transactions";
        ResultSet rs2 = st.executeQuery(queryForId);
        if (!rs2.isBeforeFirst() ) {    
             System.out.println("No data"); 
        } else {
            while ( rs2.next() ) {
                int id = rs2.getInt("id");          
                System.out.println("Last id of this session was..."+id);
            }
        }

        conn.close();
    } catch(Exception e) {
        System.out.print("Do not connect to DB - Error:"+e);
    }
}

Code is working for Insertion but I am not getting last inserted id. It gives following exception.
Column 'id' not found. I will be thankful to you if you can help.

You should use `rs2.getInt(1);`. It's handier to use the column index instead of the column name. Especially when you don't know the column name. — Kayaman, Aug 12 '15 at 07:50
@Kayaman Can you also tell that LAST_INSER_ID() will work well if there are multiple users at a same time? — Hammad Hassan, Aug 12 '15 at 07:54

score 2 · Answer 1 · answered Aug 12 '15 at 07:53

2

Modify your select to use alias as id:-

SELECT LAST_INSERT_ID() as id FROM transactions

answered Aug 12 '15 at 07:53

Rahul Yadav

1,503
8
11

score 1 · Answer 2 · answered Aug 12 '15 at 07:54

You're trying to grab a column called id, but there's no such column. When you grab the last inserted ID, you get a column called last_insert_id() instead:

MySQL> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.02 sec)

Easier to grab it by index (use .getInt(int)). This is perfectly safe because you know there will only be one column returned.

score 1 · Answer 3 · answered Aug 12 '15 at 07:56

1

You can use below code to get last insert id.

          ResultSet rs = prepareStatementObj.getGeneratedKeys();
            if(rs.next())
            {

                last_inserted_id=Integer.toString(rs.getInt(1));

            }

answered Aug 12 '15 at 07:56

Rafiq

740
1
5
17

How to use LAST_INSERT_ID() Equivalent of @@IDENTITY in MySql

3 Answers3