Difference between RETURN_GENERATED_KEYS and specifying the generated column names

Question

What is the difference between the prepareStatement(String sql, int autoGeneratedKeys) and prepareStatement(String sql, String[] columnNames) methods of the JDBC Connection class?

The Javadoc for both indicates that the returned PreparedStatement object is capable of returning auto-generated keys if the SQL statement is an INSERT statement. In the case of the first API, Statement.RETURN_GENERATED_KEYS needs to be passed for the autoGeneratedKeys parameter. In the case of the second API, the names of generated columns are passed as a string array.

What are the reasons for using one over the other?

I noticed that Spring's SimpleJdbcInsert class prefers the variant where the column names are specified: AbstractJdbcInsert.prepareStatementForGeneratedKeys

Why is that?

Answer 1

The reasons are convenience, flexibility, performance and compatibility. For example, some database cannot know which columns are auto-generated or not, so by default their drivers return all columns when using Statement.RETURN_GENERATED_KEYS.

This can have impact on performance because:

1. All those values need to be transferred from database to client,
2. On some databases this requires a query on the metadata to know which columns to fetch.

For example, the driver for PostgreSQL will append RETURNING * (so it only has point 1 to worry about), while the Firebird driver (which I maintain) also has to query the metadata.

Some database drivers by default return a column that is not directly useful (eg Oracle - used to? - return the ROWID, which means you have to query the actual field yourself), and some databases return only the primary key, while there might also be other generated fields, and I believe that some database drivers return the last generated key, even if the table doesn't use an identity field(!).

The methods prepareStatement(String sql, String[] columnNames) and prepareStatement(String sql, int[] columnIndexes) give more control (if supported), about what is returned. If you know exactly which fields you need or want, you can specify them and get exactly those fields, without having to worry about the differences in behavior that you get with RETURN_GENERATED_KEYS.

Depending on the implementation, the one taking String[] columnNames is probably most efficient as the names can simply be put in verbatim, while the int[] columnIndexes might still require a metadata query the get the actual names.