Search code examples
javasqlprepared-statementfirebirdjaybird

Generate several IDs at once in Firebird for Prepared Statement batches


I'm using SELECT GEN_ID(TABLE,1) FROM MON$DATABASE from a PreparedStatement to generate an ID that will be used in several tables.

I'm going to do a great number of INSERTs with PreparedStatements batches and I'm looking for a way to fetch a lot of new IDs at once from Firebird.

Doing a trigger seems to be out of the question, since I have to INSERT on other tables at another time with this ID in the Java code. Also, getGeneratedKeys() for batches seem to not have been implemented yet in (my?) Firebird JDBCdriver.


Solution

  • As jrodenhi says, you can reserve a range of values using

    SELECT GEN_ID(<generator>, <count>) FROM RDB$DATABASE
    

    This will return a value of <count> higher than the previously generated key, so you can use all values from (value - count, value] (where ( signifies exclusive, ] inclusive). Say generator currently has value 10, calling GEN_ID(generator, 10) will return 20, you can then use 11...20 for ids.

    This does assume that you normally use generators to generated ids for your table, and that no application makes up its own ids without using the generator.

    As you noticed, getGeneratedKeys() has not been implemented for batches in Jaybird 2.2.x. Support for this option will be available in Jaybird 3.0.0, see JDBC-452.

    Unless you are also targeting other databases, there is no real performance advantage to use batched updates (in Jaybird) when using Firebird 3.0 or older. Firebird 3.0 and older do not support update batches, so the internal implementation in Jaybird does essentially the same as preparing a statement and executing it yourself repeatedly.

    To benefit from Firebird 4.0 and higher batch updates, you will need to use Jaybird 5 or higher (see Firebird 4.0 server-side batch updates in the Jaybird 5 release notes).

    Disclosure: I am one of the Jaybird developers