Help: Example of returnGeneratedKeys within a transaction

[titusgahissy]

Hello,

I'm trying to chain two queries, an insert followed by a select using the generated id of the first query as a parameter of the second query.
Can you provide an example to achieve that ? Thank you.

Here is my code so far :

Observable<Boolean> begin = db.beginTransaction();
Observable<Long> query = db.update("insert into users...").dependsOn(begin).parameters(...).returnGeneratedKeys().getAs(Long.class).first();
Observable<Boolean> commit = db.commit(query);
db.select("select from users where id = ?").dependsOn(commit).getAs(String.class);

[thomasnield]

Putting the transactions aside, I simply use the good 'ol flatMap()

Observable<Long> insertIds = db.update("insert into users...")
.parameters(...)
.returnGeneratedKeys()
.getAs(Long.class)
.first();

Observable<User> newUsers = insertIds.flatMap(id -> 
    db.select("select from users where id = ?")
    .parameter(id)
    .getAs(String.class)
)

You can also pass the insertIds Observable into parameters()

Observable<User> newUsers = db.select("select from users where id = ?")
    .parameters(insertIds)
    .getAs(String.class)

[thomasnield]

You can then compose the appropriate transaction calls into those two Observable queries.

[titusgahissy]

Thank you.

[titusgahissy]

I tried a few variations but I still don't know where to put the commit statement. When I use compose the insert query is executed twice. (I'm a bit new to reactive coding)

[thomasnield]

Sorry, I didn't mean compose as in the compose() operator on an Observable. I meant calling the transactional methods on the builder.

I don't use the transactions as much, but I think you might do it like this...

Observable<Long> insertIds = db.update("insert into users...")
.parameters(...)
.dependsOn(db.beginTransaction())
.returnGeneratedKeys()
.getAs(Long.class)
.first();

Observable<User> newUsers = db.select("select from users where id = ?")
    .parameters(insertIds)
    .getAs(String.class)

db.commit(newUsers).subscribe(v -> System.out.println("Received item " + v));

[titusgahissy]

If I do it that way, with how can I get the result of newUsers then ? db.commit(newUsers); returns an Observable<Boolean>

[thomasnield]

Darn, you're right. Forgive my pseudo-code. One moment...

[thomasnield]

I'll show an example later when I get to a computer, but I would use the cache() operator to capture and replay the inserted ids so they are not inserted twice, but can be called for the commit() as well as the select().

[titusgahissy]

This is working for me but I'm not sure if it's the right way to do it.

return db.update(queryInsert).dependsOn(db.beginTransaction())
      .parameters(model.getValues())
      .returnGeneratedKeys()
      .getAs(Long.class)
      .first()
      .cache()
      .compose(idObs -> db.commit(idObs).flatMap(aBoolean -> idObs))
      .flatMap(id ->  db.select(querySelect)
                        .dependsOnLastTransaction()
                        .parameter(id)
                        .getAs(String.class));

without cache()

return db.update(queryInsert)
            .dependsOn(db.beginTransaction())
            .parameters(model.getValues())
            .returnGeneratedKeys()
            .getAs(Long.class)
            .first()
            .flatMap(id ->
                db.select(querySelect).parameter(id).getAs(String.class)
                    .compose(result -> db.commit(result).flatMap(ok -> result))
            );

[thomasnield]

I think this should work possibly too...

Observable<Integer> insertIds = db.update(queryInsert).dependsOn(db.beginTransaction())
      .parameters(model.getValues())
      .returnGeneratedKeys()
      .getAs(Long.class)
      .first()
      .cache();

Observable<Boolean> commit = db.commit(insertIds);

db.select(querySelect)
     .parameters(insertIds)
     .getAs(String.class);

[ghost]

@gahissy If it works for you, then it is the right way to do it.