Get primary keys of updated rows when doing an update with jdbi

Question

I'm using jdbi (but would prepared to use raw jdbc if needed). My DB is currently Oracle. I have an update that updates the first row matching certain criteria. I want to get the primary key of the updated row from the same statement. Is this possible?

I tried

Integer rowNum = handle
                    .createUpdate(sqlFindUpdate)
                    .bind("some var", myVal)
                    .executeAndReturnGeneratedKeys("id")
                    .mapTo(Integer.class)
                    .findOnly();

but I guess this isn't a generated key, as it doesn't find it (illegal state exception, but the update succeeds).

Basically, I have a list of items in the DB that I need to process. So, I want to get the next and mark it as "in progress" at the same time. I'd like to be able to support multiple worker threads, so it needs to be a single statement - I can't do the select after (the status has changed so it won't match anymore) and doing it before introduces a race condition.

I guess I could do a stored procedure that uses returning into but can I do it directly from java?

Have you tried a `RETURNING id INTO ?` clause? The jdbi3-oracle12 module has support for this: http://jdbi.org/apidocs/org/jdbi/v3/oracle12/OracleReturning.html — qualidafial, Feb 14 '18 at 01:36

Adam · Accepted Answer · 2018-02-26T10:37:23.480

I'm answering my own question, but I don't think it's a good answer :) What I'm doing is kind of a hybrid. It is possible to dynamically run PL/SQL blocks from jdbi. Technically, this is from Java as I had asked, not via a stored procedure. However, it's kind of a hack, in my opinion - in this case why not just create the stored procedure (as I probably will, if I don't find a better solution). But, for info, instead of:

String sql = "update foo set status = 1 where rownr in (select rownr from (select rownr from foo where runid = :runid and status = 0 order by rownr) where rownum = 1)";
return jdbi.withHandle((handle) -> {
  handle
     .createUpdate(sql)
     .bind("runid", runId)
     .executeAndReturnGeneratedKeys("rownr")
     .mapTo(Integer.class)
     .findOnly();
});

you can do

String sql = "declare\n" + 
     "vRownr foo.rownr%type;\n" + 
     "begin\n" + 
     "update foo set status = 1 where rownr in (select rownr from (select rownr from foo where runid = :runid and status = 0 order by rownr) where rownum = 1) returning rownr into vRownr;\n" + 
     ":rownr := vRownr;\n" + 
     "end;";
return jdbi.withHandle((handle) -> {
  OutParameters params = handle
     .createCall(sql)
     .bind("runid", runId)
     .registerOutParameter("rownr", Types.INTEGER)
     .invoke();
  return params.getInt("rownr");
});

Like I said, it's probably better to just create the procedure in this case, but it does give you the option to still build the SQL dynamically in java if you need to I guess.

Based on this question, as linked by @APC in the comments, it is possible to use the OracleReturning class without the declare/begin/end.

String sql = "update foo set status = 1 where rownr in (select rownr from (select rownr from foo where runid = ? and status = 0 order by rownr) where rownum = 1) returning rownr into ?";
return jdbi.withHandle((handle) -> {
  handle
     .createUpdate(sql)
     .bind(0, runId)
     .addCustomizer(OracleReturning.returnParameters().register(1, OracleTypes.INTEGER))
     .execute(OracleReturning.returningDml())
     .mapTo(Integer.class)
     .findOnly();
});

However, OracleReturning doesn't support named parameters, so you have to use positionals. Since my main reason for using JDBI over plain JDBC is to get named parameter support, that's important to me, so I'm not sure which way I'll go

Pretty hard dependency on it being an Oracle DB you're calling...

Update: enhancement for named parameters in OracleReturning was merged to master, and will be included in 3.1.0 release. Kudos to @qualidafial for the patch

It certainly seems possible to retrieve generated keys using JDBC. Check out [this StackOverflow thread](https://stackoverflow.com/questions/17320205/oracles-returning-into-usage-in-java-jdbc-prepared-statement) . So it then becomes a question of how to do it in JDBI. The documentation suggests [this can be done](http://jdbi.org/#_generated_keys). — APC, Jan 21 '18 at 08:21
The thing is, with an update (as opposed to insert - JDBI uses type "Update" for both) it's not really a _generated_ key, it's a matched key. But thanks for the link @APC, it helped. See my update — Adam, Jan 21 '18 at 13:59
Please open an issue with Jdbi to support named parameters in `OracleReturning`. Shouldn't be too difficult. — qualidafial, Feb 19 '18 at 17:19
Good idea @qualidafial. https://github.com/jdbi/jdbi/issues/1022 I did end up going the `OracleReturning` route despite lack of named parameters — Adam, Feb 19 '18 at 20:19
Follow up: enhancement for named parameters in OracleReturning was merged to master, and will be included in 3.1.0 release — qualidafial, Feb 26 '18 at 03:44

Get primary keys of updated rows when doing an update with jdbi

1 Answers1