Get the id of the inserted row

Question

I am trying to get the id of the inserted row. Currenly I am not getting the id back and I am getting the exception java.sql.SQLException: Column 'route_id' not found. but when I query it as in the commented code I am getting it but it is of all rows in the routes table. How can I fix it?

I appreciate any help.

Code:

if (routesTables.next()) {

    PreparedStatement prepRoutesInsert = con.prepareStatement(
            "INSERT INTO routes(direction, route)"
                    + "VALUES( ?, ?)",
            Statement.RETURN_GENERATED_KEYS);

    prepRoutesInsert.setString(1, direction);
    prepRoutesInsert.setInt(2, route);

    prepRoutesInsert.executeUpdate();

    // PreparedStatement prepRoutesInsert2 =
    // con.prepareStatement(
    // "SELECT route_id from routes");
    // ResultSet rs = prepRoutesInsert2.executeQuery();
    // while(rs.next()){
    // int route_id2 = rs.getInt("route_id");
    // System.out.println(route_id2);
    // }

    try (ResultSet generatedKeys = prepRoutesInsert
            .getGeneratedKeys()) {
        if (generatedKeys.next()) {
            int id = generatedKeys.getInt("route_id");
            System.out.println("The id is: " + id);
        }

    }
}

routes table:

stt.execute("CREATE TABLE IF NOT EXISTS routes ("
                + "route_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                + "direction VARCHAR(30) NOT NULL, "
                + "route INT(11) NOT NULL )");

`generatedKeys.getInt("route_id")` ==> `generatedKeys.getInt(1)` or `generatedKeys.getInt("GENERATED_KEY")`will fix this problem. — Sergey Kalinichenko, Jun 18 '15 at 17:42

score 1 · Accepted Answer · edited May 23 '17 at 11:51

This might help you -

public static int insertInDB(String sqlQuery) {
        Connection conn = getDBConnection();
        ResultSet rs = null;
        int id = 0;
        try {
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            stmt.executeUpdate(sqlQuery);
            Statement stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt1.executeQuery("select last_insert_id()");
            while(rs.next()){
                id = rs.getInt(1);
            }
        } catch (Exception e) {
            closeDBConnection();
        }
        return id;
    }

Please keep in mind, that last_insert_id() returns that last inserted id in the any of the tables in your whole schema. Please make sure that this method is atomic, and no one else inserts while this method fully executes.

Please read this - https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

This previous discussion might help - LAST_INSERT_ID() MySQL

Get the id of the inserted row

1 Answers1