How to get the primary key after inserting new row with a SQLContainer

Using a company table as an example: How do you get the new primary key(CompanyId) from the database after inserting a new company into the database using a SQLContainer?

sqlcontainer = getCompanyContainer();
sqlcontainer.getContainerProperty(itemId,”Name”).setValue(“Acme Inc”);
sqlcontainer.getContainerProperty(itemId,”City”).setValue(“New York”);

Integer CompanyId =
??? (what code goes here?)


I found the solution:

container.addRowIdChangeListener(new QueryDelegate.RowIdChangeListener() {
public void rowIdChange(RowIdChangeEvent event) {
System.err.println("Old ID: " + event.getOldRowId());
historyId = Integer.parseInt(event.getNewRowId().toString());

Unfortunately this does not work with FreeformQuery. You need a custom FreeformStatementDelegate and in the storeRow() interface method you have to retrieve the generated keys after executing the INSERT:

stmt.executeUpdate(); // the INSERT ResultSet rs = stmt.getGeneratedKeys(); if ( ) { ResultSetMetaData rsmd = rs.getMetaData(); int colCount = rsmd.getColumnCount(); if ( colCount > 0 ) { Integer genId = rs.getInt( 1 ); ColumnProperty colProp = (ColumnProperty) row.getItemProperty( "id" ); if ( colProp.isReadOnly() ) { if ( colProp.isReadOnlyChangeAllowed() ) // unlikely { colProp.setReadOnly( false ); row.getItemProperty( "id" ).setValue( genId ); colProp.setReadOnly( true ); } else { // Hack Alert! You will have to retrieve this value later. See below. row.getId().getId()[0] = genId; } } else // unlikely { row.getItemProperty( "id" ).setValue( genId ); } } } else { LOG.warn( "Unable to retrieve generated keys after: " + sql ); } The above method gets called by SQLContainer.commit().

Then you will have to do this in the caller:

Integer generatedId =
   (Integer) ((RowId) container.getIdByIndex( container.size() - 1 )).getId()[0]

This code assumes the inserted row is last. There does not appear to be any other way to do this because the RowItem passed to storeRow() has a TemporaryRowId in it which is not updateable. And you have no way to look up the row in the DB. You will likely end up where it says “Hack Alert!” because the generated id columns are READ_ONLY. No way to update the RowId with the generated id.

I could not get TableQuery and MSSQLGenerator to work with SQL Server at all because the quoting is all wrong. The SQL it generates is all screwy. And this is in Vaadin 7.3.9. This just barely works and its not a robust solution.