GeneratedKeyHolder.getKey returns NULL with postgres [SPR-7564]

[spring-projects-issues]

Adrian opened SPR-7564 and commented

On the forum there is thread that this problem cannot be solved (http://forum.springsource.org/showthread.php?t=19293).

The latest JDBC drives for postgres seam to work fine when returning the generated keys from a prepared statement but Spring Framework still seams to have issues getting this key. When calling getKey on a GeneratedKeyHolder object NULL is returned, whereas the following plain jdbc code works fine:

PreparedStatement preparedStatement = connection.prepareStatement(SQL,
		PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, entityType.getMetaData());

preparedStatement.executeUpdate();

Long entityPrimaryKey = null;

ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
if (generatedKeys.next()) 
	entityPrimaryKey = Long.valueOf(generatedKeys.getLong(1));

preparedStatement.close();

My table definition on postgres is as follows:

CREATE  TABLE  "entity_type" (
  "entity_type_id" SERIAL ,
  "meta_data" TEXT NOT NULL ,
  PRIMARY KEY ("entity_type_id") );

---

Affects: 3.0.4

Reference URL: http://forum.springsource.org/showthread.php?t=19293

Attachments:

• postgres-test.zip (5.42 kB)
• postgres-test-exception.txt (2.69 kB)

Referenced from: commits 97e047e

[spring-projects-issues]

Thomas Risberg commented

Do you have an example of the code that is using Spring JDBC where the returned key is NULL? I just ran some tests with the postgresql-8.4-702.jdbc4 driver and it seems to work fine.

-Thomas

[spring-projects-issues]

Adrian commented

Basically the code used SqlUpdate as a super class and an update method like this:

GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
update(params, generatedKeyHolder);
int returendKey = generatedKeyHolder.getKey().intValue();

I can prepare a full example over the weekend.

[spring-projects-issues]

Adrian commented

Please find attached a Maven-based test project for this issue. There is a test case which failes with an exception given in a second file.

[spring-projects-issues]

Thomas Risberg commented

I added a call to "setGeneratedKeysColumnNames" in the constructor of your EntityTypeInsert and the example works now. We could probably improve the docs or make sure that names of generated key columns have been set if they are necessary, throwing an exception if not set.

@Autowired
public EntityTypeInsert(DataSource dataSource) {
     super(dataSource, SQL, new int[] { Types.VARCHAR });
     setGeneratedKeysColumnNames(new String[] {"entity_type_id"});
}

-Thomas

[spring-projects-issues]

Thomas Risberg commented

We should add checks that necessary generated key metadata has been provided for SqlUpdate

[spring-projects-issues]

Thomas Risberg commented

I'm adding checks to throw an exception in this case to alert the programmer that this needs to be configured first.