The "route_id" not found

Question

I am trying to get the id of the inserted raw in the routes table. Currently I am getting the error Column 'route_id' not found but the route and direction are being inserted in the table before Why am I getting this error?

Code:

DatabaseMetaData dbm = con.getMetaData();

        ResultSet routesTables = dbm.getTables(null, null, "routes",
                null);
        int route_id;
        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();

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

            }

routes table structure:

   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 )");

Screenshot of geratedKeys variable:

enter image description here

Edit: When I query it like this I am getting the route_id but of the all raws in the routes table:

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

could you try to put a debug point on `prepRoutesInsert.executeUpdate();` and see what is saved in the db? — Jayant Varshney, Jun 18 '15 at 16:04
I have debugged it and as I said before a new route and direction are being inseted. I can see it in the db. Is that what did you ask? — Mr Asker, Jun 18 '15 at 17:02
See if this is applicable to you http://stackoverflow.com/questions/4224228/preparedstatement-with-statement-return-generated-keys — 6ton, Jun 18 '15 at 17:06

score 0 · Answer 1 · edited May 23 '17 at 11:43

Use

int id = generatedKeys.getInt(1);

instead.

The comment posted in response to this MySQL bug says that:

You shouldn't retrieve these columns by name. Only by index, since there can only ever be one column with MySQL and auto_increments that returns value(s) that can be exposed by Statement.getGeneratedKeys().

If you really want to, you can use

int id = generatedKeys.getInt("GENERATED_KEY");

instead, and it will work, but I wouldn't recommend it. A similar question asks why the name GENERATED_KEY is used, and its only answer quotes from the same bug I linked to above.

The "route_id" not found

1 Answers1