SimpleJdbcInsert.executeAndReturnKey Fails for HSQLDB version 1.9.0 [SPR-6266] #10934
Description
Nick Williams opened SPR-6266 and commented
In previous versions of HSQL DB, generated identity primary key values could not be returned via JDBC. They can be returned in HSQL DB 1.9.0 (and possibly 1.8, but I haven't tested that).
In IntelliJ I am running a unit test with which I am testing a data access object. I am using the EmbeddedDatabaseBuilder to create, populate, and discard a database for each test executed and pass the data source it generates to my DAO. Everything works well until the DAO I am testing calls executeAndReturnKey() on a SimpleJdbcInsert object. This method throws a DataIntegrityViolationException with message "Unable to retreive[sic] the generated key for the insert".
I debugged a single insert for over an hour, going nearly 100 levels deep into the stacktrace, and determined that the HSQL DB 1.9.0 JDBC driver is, for sure, returning the generated key. For some reason, Spring isn't picking it up and thinks it failed, thus throwing the exception.
Needless to say, this makes testing inserts using HSQL DB rather difficult. I currently have an ugly workaround to make sure the insert at least succeeded. It does. I just can't get the generated key. My target database will actually be SQL Server, so I'm not concerned about this affecting my production application. It just affects testing.
OT Note: Notice that there is also a misspelling ("retreive") in the message for the exception thrown.
Affects: 3.0 RC1