Why do we add new String[]{"ID"} when fetching keyholder value in NamedParameterJDBCTemplate

Question

Why do we add new String[]{"ID"} when fetching keyholder value in NamedParameterJDBCTemplate?

My question is based on the answer of this question: Is there a way to extract primary key(or ROWID) using NamedParameterJdbcTemplate and GeneratedKeyHolder?

My question may sound stupid but I do need a clarification on this. I know that, the solution for the exception when trying to fetch the PK from DB using Keyholder: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]

My question why do we cast ID to a string array and then fetch its long value? Can't we directly get a long value?

For the code snippet refer to the answer in the above link. My question is based on the answer.

Answer 1

In the default situation, Spring's JdbcTemplate will use JDBC's Statement.RETURN_GENERATED_KEYS option. The problem is that the Oracle JDBC driver will not return the value of an id column for that option, but instead return a 'row id' (basically an internal pointer to the row, not the id value in that row). This is not suitable for most use cases, as it would require you to explicitly query the row using the row id to obtain the actual id value, and specifically in the implementation of Spring's KeyHolder it results in the mentioned error, because a java.sql.RowId implementation is not a long (nor a subclass of java.lang.Number).

Fortunately, JDBC offers additional methods to obtain generated columns: by explicitly specifying the column names (or column indexes) of the column(s) you want to have. That is what new String[] {"ID"} does. It instructs JdbcTemplate to pass this array of column names to the JDBC driver, and the driver - assuming it supports this feature - will return the specified column(s), that is if your table actually has a column with the name ID.

In other words, this does not "cast ID to a string array and then fetch its long value", it will retrieve the value of the column ID instead of the Oracle JDBC driver returning the 'row id' of the inserted row.

The question you link to is specifically about Oracle (which defaults to returning a row id). The reason the Oracle JDBC driver returns a row id is because until recently Oracle did not have identity columns, and the only way to have identity-like behaviour was to use custom triggers to generate IDs. This makes it impossible to determine if a table has generated columns, and if so which column(s). Instead the implementation decision was to return the row id unless explicit columns were specified.

Although this problem is specific to Oracle, other drivers may have different problems that may need to be solved using the same solution: for example some drivers (e.g. PostgreSQL and Firebird (Jaybird, the Firebird JDBC driver I maintain)) will default to returning all columns, and if your ID column is not the first column, this may result in similar errors because the Spring GeneratedKeyHolder.getKey() method assumes the generated id is held in the first column of the generated keys result set.