DbClient - can we return generated id(s)?

[tomas-langer]

Environment Details

• Helidon Version: 2.0
• Helidon SE

---

Problem Description

When using autogenerated ids (or sequences), we need to return the id used from the insert statement. This is currently not possible with db client. In JDBC, this is possible using Statement.RETURN_GENERATED_KEYS and Statement::getGeneratedKeys

see https://stackoverflow.com/questions/9353167/auto-increment-id-in-h2-database

[Tomas-Kraus]

Good question. We have no statement that keeps execution info in it. Everything is in returned Future. In case of DML it's just number of modified records. Maybe we can extend this future value to contain a bit more. But it will be significant API change. Both Mongo and JDBC support this so implementing such a feature should not be a problem.

[hantsy]

Currently, I have to handle it myself.

public Single<UUID> save(Post post) {
    return this.dbClient
            .execute(exec -> exec
                    .query("INSERT INTO posts(title, content) VALUES (?, ?) RETURNING id", post.getTitle(), post.getContent())

            )
            .first()
            .map(data -> data.column("id").as(UUID.class));
}

[tomas-langer]

Investigating - for now I am trying to add a new method to the API that would return the generated ids, so I do not break backward compatibility.

[spericas]

This seems like a very useful addition to the API

[Tomas-Kraus]

I have some time to have a look at this now. But I'll do it as part of 4.x where DB Client is synchronnous.

[Tomas-Kraus]

@tomas-langer We have to extend DbStatementDml inerface methods. Currently we have single prototype there:

long execute();

Which returns value of statement.executeUpdate() which is usually the number of modified records. We also need to retrieve content of ResultSet returned by getGeneratedKeys(). JDBC ResultSet is mapped to List of DB client DbRow so I have 2 options: 1st:

List<DbRow> insert();

This will return DbRows containing generated keys.

2nd:

public record DMLResult (int result, List<DbRow> keys) {
}

DMLResult insert();

This will return both statement.executeUpdate() value and generated keys, but looks quite obscure to me.