Why does MySQL Connector/J throw java.sql.SQLException: Generated keys not requested?

Question

Why does the following code snippet:

PreparedStatement ps = con.prepareStatement("CREATE TABLE foo (name VARCHAR(30))");
ps.executeUpdate(); // or ps.execute();
ps.getGeneratedKeys(); // <-- throws an exception

throw this exception:

java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate(), Statement.executeLargeUpdate() or Connection.prepareStatement().

---

Correct me if I am wrong, but neither java.sql API Specification nor JDBC 4.3 Specification (specifically, section 13.6) specifies such behaviour. Where does it explicitly say in the specification documents what the exception message commands the user to do?

All I could find was this:

If this Statement object did not generate any keys, an empty ResultSet object is returned. [Source: getGeneratedKeys() Javadoc]

I was expecting an empty ResultSet instead of an exception.

Answer 1

Correct me if I am wrong, but the Java SQL api docs do not specify such behaviour. Where does it explicitly say in the Javadocs what the error message commands the user to do?

It doesn't say it explicitly, but it does say it implicitly.

There are 6 overloads to prepareStatement​, the first two are:

• prepareStatement​(String sql): Creates a PreparedStatement object for sending parameterized SQL statements to the database.

• prepareStatement​(String sql, int autoGeneratedKeys): Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys.

Implied in the description of the second one, is that if you don't ask for it, the PreparedStatement will not have the capability to retrieve auto-generated keys.

Whether the driver enforces this is entirely up to the driver and how the given database implements retrieval of auto-generated keys.

Some databases requires modifying the INSERT statement, e.g. to add a RETURNING clause for returning the generated key values. I believe Oracle Database works like that, in order to obtain the key value that was assigned by a TRIGGER. If you don't tell the PreparedStatement to modify the INSERT statement before the statement is executed, the values won't be available for getGeneratedKeys to retrieve. Actually, for Oracle Database to work, I believe you need to call the prepareStatement​(String sql, String[] columnNames) method, naming the columns you need returned, so the driver can build the RETURNING clause.

Other databases, e.g. SQL Server, can retrieve the auto-generated key by executing a statement like SELECT @@IDENTITY after the INSERT statement. As such, they don't need to modify the INSERT statement, and can therefore do it on-demand when the getGeneratedKeys method is called. Might still be better for performance to do it up-front, to save a round-trip to the database.

From comment: I claim that the exception violates the javadoc specification.

It does not. Just because the javadoc of one of the overloads don't explicitly say that generated keys will not be available, it doesn't mean that you shouldn't consider the overloads and why they are there, i.e. what features they enable and that those features are not enabled if not explicitly requested by you by calling the correct overload.

It's not a violation, though I agree that it would be nice for the javadoc to be more explicit.

---

UPDATE: More on the exception being perceived as a violation of the API.

The exception is not the driver violating the API, since getGeneratedKeys is only expected to work if it has been enabled. Calling getGeneratedKeys without enabling it is you violating the API, so it is appropriate for the driver to reject that call (but not required that it does).

There is a difference between having getGeneratedKeys enabled and having the SQL statement actually generating keys. E.g. following are examples of SQL statements that don't generate a key:

• UPDATE and DELETE statements.

• INSERT INTO x (...) SELECT ... FROM ... statement can generate keys, but if the SELECT doesn't return any rows, then the INSERT doesn't generate any keys.

If getGeneratedKeys has been enabled, the API spec says that it must return an empty ResultSet object for those kinds of statements.

Answer 2

You are right, the behaviour exhibited by MySQL Connector/J violates the requirements of the JDBC API and specification to return an empty result set if a Statement does not generate keys.

A Statement will generate keys if two conditions are met:

1. The appropriate prepare or execute method was called to allow generated keys (ie passing Statement.RETURN_GENERATED_KEYS, or an array with column names or column indexes).

2. The statement text prepared or executed supports retrieval of generated keys (usually insert, sometimes also update, delete, merge, etc).

   Be aware that using the methods accepting column names or column indexes with a statement text that doesn't generate keys could result in an exception anyway, as those names or indexes likely won't be valid for that statement.

In all other situations, a Statement does not generate keys, and so getGeneratedKeys() should return an empty result set, as indicated by the Statement.getGeneratedKeys() documentation:

Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.

And specifically, it only allows an exception under the following conditions:

SQLException - if a database access error occurs or this method is called on a closed Statement
SQLFeatureNotSupportedException - if the JDBC driver does not support this method

If the JDBC specification would have required an exception to be thrown when the Statement wasn't prepared or executed using the appropriate methods (see point 1), that condition would have been listed in the reasons for throwing an exception.

This also matches behaviour in other parts of JDBC. For example in DatabaseMetaData, an implementation that doesn't support a certain type of metadata should return an empty result set instead of throwing an exception.