Re: Return key from query

On 11/02/2010 02:43 PM, Jonathan Tripathy wrote:
> Hi everyone,
>
> When adding a new record, we run an insert query which auto-increments
> the primary key for the table. However the method (in java) which calls
> this query must return the newly created key.
>
> Any ideas on how to do this, preferably using a single transaction?
>
> Thanks
>

Ah yes have your cake and eat it too.

If you app code (java) is making new instances and wants to have an id,
then the table cannot have an auto-id.

I'm big on java/class id generation (usually use UUID) but you do have
to take complete responsibility for it. The hibernate folks frown on it
but their retort is imho woeful. If you're not using uuid's you will
need a generator. You can back it up with a persistent store so you
know where you left off, but you will want to (auto-)increment that id
table with a large value (say 5000) and have you generate dole them out
as needed at the app level. When it has spent 5000 ids, it will go to
the server are ask for another 5000 (separate tx). Please don't get
hung up on loosing some portion of the 5000 id when you restart or whatever.