Add a common way to get generated values for all databases

[DavideD]

Insert and select is a common scenario when the id is generated by the database. Different databases have different way to implement this and, at the moment, there is not a common way to do it for all clients.

In MySQL, for example, this is how you would get the last generated id from an insert query:

RowSet<Row> rows = ...
Long id = rows.property( MySQLClient.LAST_INSERTED_ID );

while in Oracle, it would look something like:

final Row oracleKeys = rows.property( OracleClient.GENERATED_KEYS );
if ( oracleKeys != null ) {
   Long id = oracleKeys.getLong( idColumnName );
}

In Hibernate Reactive this is a problem because we need to run the same code for all the databases and, at runtime, we might not have a dependency to Oracle or MySQL. To make it work we need some glue code that checks if a class is on the classpath.

The purpose of this issue is to propose an alternative solution to make it easier to get generated values. It would be nice to have a method in SqlResult to get generated values:

interface SqlResult {
   ...
   Object generatedValue(int index);
   Object generatedValue(String columnName);
   Object generatedValue(); // For when there is only one generated value, probably the most common case
}

This way we could write:

RowSet<Row> rows = ...
Long id1 = (Long) rows.generatedValue("ID");

That said, I'm not sure how feasable this solution is. This is mainly to open a discussion about it and I'm listening if somebody has a better idea or a reason why this wouldn't work.

We already had a quick conversation about it but it was getting hard to follow it and we weren't very precise about what we would like to have in the API.

[tsegismont]

@DavideD thanks for sharing this