What is the difference between the
prepareStatement(String sql, int autoGeneratedKeys) and
prepareStatement(String sql, String columnNames) methods of the JDBC
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:
Why is that?
Best How To :
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
This can have impact on performance because:
- All those values need to be transferred from database to client,
- 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(!).
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
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.