Support HSQLDB generated keys column name array in SimpleJdbcInsert

[jsotuyod]

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

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/metadata/GenericTableMetaDataProvider.java

Lines 96 to 98 in e7402bc

	if (databaseMetaData.supportsGetGeneratedKeys()) {
	logger.debug("GetGeneratedKeys is supported");
	setGetGeneratedKeysSupported(true);

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

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/metadata/GenericTableMetaDataProvider.java

Lines 52 to 53 in e7402bc

	private static final List<String> productsNotSupportingGeneratedKeysColumnNameArray =
	Arrays.asList("Apache Derby", "HSQL Database Engine");

and instead fallback to using a generic Statement.RETURN_GENERATED_KEYS flag

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/simple/AbstractJdbcInsert.java

Lines 540 to 557 in e7402bc

	private PreparedStatement prepareStatementForGeneratedKeys(Connection con) throws SQLException {
	if (getGeneratedKeyNames().length < 1) {
	throw new InvalidDataAccessApiUsageException("Generated Key Name(s) not specified. " +
	"Using the generated keys features requires specifying the name(s) of the generated column(s).");
	}
	PreparedStatement ps;
	if (this.tableMetaDataContext.isGeneratedKeysColumnNameArraySupported()) {
	if (logger.isDebugEnabled()) {
	logger.debug("Using generated keys support with array of column names.");
	}
	ps = con.prepareStatement(getInsertString(), getGeneratedKeyNames());
	}
	else {
	if (logger.isDebugEnabled()) {
	logger.debug("Using generated keys support with Statement.RETURN_GENERATED_KEYS.");
	}
	ps = con.prepareStatement(getInsertString(), Statement.RETURN_GENERATED_KEYS);
	}

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())