Thank you very much for your answer. So I understand that this feature is
not oficially supported, although they are some workarounds (e.g., the
*;SCOPE_GENERATED_KEYS=TRUE*). And it is not considered to be a bug. Would
it be possible to implement such support?
About JDBC usage - We use query DSL which at the end calls INSERT on
org.h2.jdbc.JdbcPreparedStatement, connection.session.autocommit is false
and generatedKeysRequest is Boolean.TRUE. So I (the query DSL) call H2
according to specification.
I think there can be a way how to make it work automatically althouh a
manuall support (if you implemented it) would be enouhgh. Although the
INSTEAD OF TRIGGER can really do what ever, the caller always call some
view and request generated keys of that view. You always call the before
trigger and pass there newRow params. So if the trigger changes the
incoming newRow, I think it is OK to assume that it is the generated key
and pass the changed values out. You do all this anyway except you forget
the values at the end when GeneratedKeys.getKeys(session) is called.
I mentioned I implemented a small hack to make it work in my code. It
proves that this approach can work (as it is just a unit test support and I
need not solve all possible scenarios, I do not handle more keys and the
code is not perfect). So maybe it may not be so difficult to implement this
feature as it almost works and TriggerObject (and other H2 code) can be
musch simplier as there is no need to use reflection and objects are
available (contrary to Trigger implementation where I have just some
objects).
What I do:
0) Extend TriggerAdapter and override the Trigger original method to keep
link to Object[] parameters:
@Override
public void fire(final Connection conn, final Object[] oldRow, final
Object[] newRow) throws SQLException { // NOPMD signature is inherited
this.newRowAsObject = newRow;
1) get the link to original Table representing the view before I do
anything else in the trigger (subclass of TriggerAdapter)
final org.h2.jdbc.JdbcConnection typedConnection =
getTypedH2Connection(conn);
final org.h2.engine.Session session =
getTypedH2Session(typedConnection);
final CommandContainer commandContainer =
(CommandContainer)session.getCurrentCommand();
final Insert perapred =
(Insert)ReflectionUtils.getField(PREPARED_FIELD, commandContainer);
final Table originalTable =
(Table)ReflectionUtils.getField(TABLE_FILED, perapred);
2) Execute my insert to real table (creating a new PreparedStatement with
either generateKeys = TRUE (or String[] column names))
3) Finding the generated key column in newRow parameters I stored in step 0
and updating it with a new value
4) Prepare GeneratedKeys in session so the H2 returns them to my original
PreparedStatement with code like this:
/**
* Updates {@code session} so generated keys are propagated outside of
JDBC statement.
*
* @param h2Connection H2 connection
* @param h2Session H2 session
* @param originalTable original table with which the trigger was called
* @param insertPreparedStatement the insert prepared statement
* @param generatedKeyColumnName name of column to propagate
* @throws SQLException if any SQL problem occurs
*/
protected void propagateGeneratedKey(
final Connection h2Connection,
final org.h2.engine.Session h2Session,
final Table originalTable,
final PreparedStatement insertPreparedStatement,
final String generatedKeyColumnName) throws SQLException {
int generatedKeyIndex = -1;
final ResultSet columnResultSet =
h2Connection.getMetaData().getColumns(null, schemaName, tableName, null);
int i = 0;
while (columnResultSet.next()) {
final String columnName =
columnResultSet.getString(METADATA_COLUMN_NAME);
if (columnName.equalsIgnoreCase(generatedKeyColumnName)) {
generatedKeyIndex = i;
logger.debug("Column {} found in table metadata with index:
{}", generatedKeyColumnName, generatedKeyIndex);
break;
}
i++;
}
if (generatedKeyIndex > -1) {
final ResultSet generatedKeys =
insertPreparedStatement.getGeneratedKeys();
if (generatedKeys.next()) {
this.newRowAsObject[generatedKeyIndex] =
generatedKeys.getLong(1);
}
h2Session.getGeneratedKeys().clear(new
String[]{generatedKeyColumnName});
h2Session.getGeneratedKeys().initialize(originalTable);
final Value value = DataType.convertToValue(h2Session,
this.newRowAsObject[generatedKeyIndex], Value.UNKNOWN);
@SuppressWarnings("unchecked")
final List<Map<Column, Value>> data =
(List<Map<Column, Value>>)
ReflectionUtils.getField(DATA_FILED, h2Session.getGeneratedKeys());
data.add(Collections.singletonMap(originalTable.getColumn(generatedKeyIndex),
value));
logger.debug("Record inserted with keys {}.",
this.newRowAsObject[generatedKeyIndex]);
} else {
logger.debug("Column {} not found in table metadata",
generatedKeyColumnName);
}
}
The most important steps in fact are:
h2Session.getGeneratedKeys().clear(new String[]{generatedKeyColumnName});
h2Session.getGeneratedKeys().initialize(originalTable);
final Value value = DataType.convertToValue(h2Session, this.
newRowAsObject[generatedKeyIndex], Value.UNKNOWN);
@SuppressWarnings("unchecked")
final List<Map<Column, Value>> data =
(List<Map<Column, Value>>) ReflectionUtils.getField(
DATA_FILED, h2Session.getGeneratedKeys());
data.add(Collections.singletonMap(originalTable.getColumn(
generatedKeyIndex), value));
But this all can easily be done by TriggerObject in case that it is instead
of trigger and it is clear that real insert will be skipped (so confirmRow
is not invoked).
TriggerObject has all the data (e.g., original Table) so it need not to use
a hack with reflection.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
