1

How would i go about creating a transaction, inserting a row, committing the transaction and getting the last inserted id. So the method should return a Uni<Integer>. I'm new to the mutiny api, I previously used the vertx.io chaining future handlers mechanism, and so it's a bit tough readjusting myself to work with the mutiny api. I have checked the documentation and think something similar to the following snippet should work, but i'm stumped on how to make it work and return Uni<Integer> from the last query instead of Uni<Void> from the tx.commit()

return this.client.begin()
   .flatMap(tx -> tx
           .preparedQuery("INSERT INTO person (firstname,lastname) VALUES ($1,$2)")
           .execute(Tuple.of(person.getFirstName(),person.getLastName()))
           .onItem().produceUni(id-> tx.query("SELECT LAST_INSERT_ID()"))
           .onItem().produceUni(res -> tx.commit())
           .onFailure().recoverWithUni(ex-> tx.rollback())
   );
Obb
  • 163
  • 4
  • 12

1 Answers1

2

Try this:

return client.begin().onItem().produceUni(tx -> tx
    .preparedQuery("INSERT INTO person (firstname,lastname) VALUES ($1,$2)").execute(Tuple.of(person.getFirstName(),person.getLastName()))
    .onItem().produceUni(id -> tx.query("SELECT LAST_INSERT_ID()").execute())
    .onItem().apply(rows -> rows.iterator().next().getInteger(0))
    .onItem().produceUni(item -> tx.commit().on().item().produceUni(v -> Uni.createFrom().item(item)))
    .on().failure().recoverWithUni(throwable -> {
        return tx.rollback().on().failure().recoverWithItem((Void) null)
            .on().item().produceUni(v -> Uni.createFrom().failure(throwable));
    })
);

A SqlClientHelper is coming to Quarkus in a future version (hopefully 1.6). You will be able to simplify to:

return SqlClientHelper.inTransactionUni(client, tx -> tx
    .preparedQuery("INSERT INTO person (firstname,lastname) VALUES ($1,$2)").execute(Tuple.of(person.getFirstName(),person.getLastName()))
    .onItem().produceUni(id -> tx.query("SELECT LAST_INSERT_ID()").execute())
    .onItem().apply(rows -> rows.iterator().next().getInteger(0))
);
tsegismont
  • 8,591
  • 1
  • 17
  • 27
  • There is a `rowset` in there, the `item` before commit, how can i manipulate it so that v will be an integer value retrieved from the rowset? – Obb Jun 16 '20 at 15:57
  • I have added `onItem().apply(RowSet::iterator).onItem().apply(iterator -> iterator.hasNext() ? iterator.next().getInteger(1) : null);` to the parent `produceUni`, but i'm only getting null even though the queries are successful and new rows are in the db – Obb Jun 16 '20 at 18:36
  • @Obby I've updated the snippets. You got `null` because the indexes in `Tuple` start at 0, not 1. – tsegismont Jun 17 '20 at 10:36
  • @Obb is this a better technique than your prior used vertx.io chaining future handlers mechanism? – Roland Beuker Apr 12 '21 at 19:54
  • 1
    @RolandBeuker, I like the mutiny approach, they have recently updated the method names and deprecated others, so it's easier to read the code. and the sqlclienthelper comes in handy when you are dealing with multiple update/insert queries in one transaction. plus they have updated the mutiny [docs](https://smallrye.io/smallrye-mutiny/) – Obb Apr 14 '21 at 08:37