Spring Accept support for generated keys column name array on HSQLDB and Derby as well

The HSQLDB JDBC Driver (starting at 2.0) will return true when queried for getGeneratedKeys support at

https://github.com/spring-projects/spring-framework/blob/e7402bc365acdbbbbb8c9aa6f0502cdd8f0f6f85/spring-jdbc/src/main/java/org/springframework/jdbc/core/metadata/GenericTableMetaDataProvider.java#L96-L98

However, all versions of HSQLDB are explicitly blocked from specifying column names to retrieve as per

and instead fallback to using a generic Statement.RETURN_GENERATED_KEYS flag

https://github.com/spring-projects/spring-framework/blob/e7402bc365acdbbbbb8c9aa6f0502cdd8f0f6f85/spring-jdbc/src/main/java/org/springframework/jdbc/core/simple/AbstractJdbcInsert.java#L540-L557

However, at least on current versions of HSQLDB, the support for column names is present, and even documented

getGeneratedKeys

Starting with version 2.0, HSQLDB supports this feature with single-row and multi-row insert, update and merge statements. This method returns a result set only if the executeUpdate methods that was used is one of the three methods that have the extra parameter indicating return of generated keys

If the executeUdate method did not specify the columns which represent the auto-generated keys the IDENTITY column or GENERATED column(s) of the table are returned.

The executeUpdate methods with column indexes or column names return the post-insert or post-update values of the specified columns, whether the columns are generated or not. This allows values that have been modified by execution of triggers to be returned.

If column names or indexes provided by the user in the executeUpdate() method calls do not correspond to table columns (incorrect names or indexes larger than the column count), an empty result is returned.

source

Why does it matter? Because using Statement.RETURN_GENERATED_KEYS will only return columns that are either an IDENTITY or GENERATED column; but a primary key may be generated through a different means. For instance, take this simple table:

SET DATABASE SQL SYNTAX PGS TRUE;

CREATE TABLE DEMO(
  id UUID NOT NULL DEFAULT uuid(),
  val VARCHAR(10) NOT NULL,
  PRIMARY KEY(id)
);

Given the following SimpleJdbcInsert:

SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(ds)
  .withTableName("DEMO")
  .usingColumns("val")
  .usingGeneratedKeyColumns("id");

KeyHolder keyHolder = jdbcInsert.executeAndReturnKeyHolder(Map.of("val", "foo"));
// keyHolder.getKeys() will be empty!

However, completely bypassing the check from jdbc insert (pardon the messy code to get it done), will yield the correct result:

SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(ds)
  .withTableName("DEMO")
  .usingColumns("val")
  .usingGeneratedKeyColumns("id");

// manually compile to help bypass
jdbcInsert.compile();

KeyHolder keyHolder = new GeneratedKeyHolder();

// ugly and shameless copy pasting of pieces of AbstractJdbcInsert just to prove a point
jdbcInsert.getJdbcTemplate().update(
  con -> {
    PreparedStatement ps = con.prepareStatement(jdbcInsert.getInsertString(), jdbcInsert.getGeneratedKeyNames());
    int[] insertTypes = jdbcInsert.getInsertTypes();
    StatementCreatorUtils.setParameterValue(ps, 1, insertTypes[0], "foo");
    return ps;
  },
  keyHolder);

// keyHolder.getKeys() will contain the uuid!

Other database drivers, such as PostgreSQL, will work as expected when given an equivalent table (ie: setting a uuid primary key through a DEFAULT gen_random_uuid())

Comment From: jhoeller

Good point, that hard exclusion of both HSQLDB and Derby is long outdated and therefore a bug. To be revised in 6.2.10.

Comment From: jhoeller

@jsotuyod this is available in the latest 6.2.10 snapshot now. Please give it an early try and let me know whether it works for you!

Comment From: jsotuyod

Awesome! Thanks!