Why does getGeneratedKeys() return "GENERATED_KEY" as column name?

Question

I'm playing with the JDBC/MySQL 5.1. I created an insert query to insert some data into a table and want to return the generated key from the newly created row. However, when I go to reference the column by "id" which is my PK and auto-increment column.

PreparedStatement ps = St0rm.getInstance().getDatabase("main")
        .prepare("INSERT INTO quests (name,minlevel,start_npc,end_npc) VALUES(?,?,?,?)", true); // creates a prepared statement with flag RETURN_GENERATED_KEYS

// ...

int affected = ps.executeUpdate();
ResultSet keys = ps.getGeneratedKeys();
if (affected > 0 && keys.next()) {
   St0rm.getInstance().getLogger().warning(String.format("ID Column Name: %s", keys.getMetaData().getColumnName(1))); // says the column name is: GENERATED_KEY

   q = new Quest(keys.getInt(1)); // column index from the generated key, no error thrown.

   q = new Quest(keys.getInt("id")); // actual column name, line throws a SQLException
   // ...
}

So, my question: Why does ResultSet.getGeneratedKeys use GENERATED_KEY as the column name?

Answer 1

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().

Currently the MySQL server doesn't return information directly that would make the ability to retrieve these columns by name in an efficient manner possible, which is why I'm marking this as "to be fixed later", since we can, once the server returns the information in a way that the driver can use.

From here (in 2006!).