Search code examples
javasqldatabaseoracle-databasejdbi

Oracle, JDBI @SqlBatch - Get all rows touched by INSERT

I have an Oracle 12c database. There's a table named Artifacts in it. The table looks like this:

ID | BONETYPE | AGE

ID is a NUMBER(12,0) GENERATED ALWAYS AS IDENTITY so the database assigns those when artifacts are inserted. BONETYPE & AGE are just strings. There's a unique constraint over BONETYPE & AGE.

I have a database query to insert missing records that looks like this:

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Artifacts, U_ARTIFACTS) */
INTO Artifacts ("BONETYPE", "AGE")
VALUES ('dinosaur', '800000');

The weird hint IGNORE_ROW_ON_DUPKEY_INDEX, is because some of these artifacts may already have been inserted.

This database query is actually issued through a JDBI query:

@SqlBatch("INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(Artifacts, U_ARTIFACTS) */\n" +
          "INTO Artifacts ( \"BONETYPE\", \"AGE\" )\n" +
          "VALUES ( :BoneType, :Age )")
@GetGeneratedKeys(columnName = "ID", value = OracleGeneratedKeyMapper.class)
int[] putArtifacts(@Bind("BoneType") List<String> boneTypes, @Bind("Age") List<String> ages);

It's a @SqlBatch for performance reasons.

The problem I have now is that I want to know the IDs of all the affected rows, including the ones that were ignored due to IGNORE_ROW_ON_DUPKEY_INDEX hint. How do I do that?

@GetGeneratedKeys only gets me the IDs of the rows of the missing records.

Solution

  • The short answer is that you cannot do this. You have to write a stored procedure to get all the IDs