aspose file tools*
The moose likes JDBC and the fly likes getting Primary key when inserting Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting Primary key when inserting" Watch "getting Primary key when inserting" New topic
Author

getting Primary key when inserting

Srinivas Rao
Greenhorn

Joined: Aug 05, 2004
Posts: 14
When I am inserting a row which contain primary key (generated by sequence), I want to get the sequence back after inserting the row in table. That key value I need to insert into child table. I am using Oracle 9i. I heard that there is some thing for this with Sybase. But don't know about the availability for Oracle. So far we are firing a query to get the sequence number and inserting with that value both in parent & child tables. This is making another DB trip (to get sequence).

Is is a DB/drivers feature or JDBC mandates to provides similar to that?
Sripathi Krishnamurthy
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
Originally posted by Srinivas Aluri:
When I am inserting a row which contain primary key (generated by sequence), I want to get the sequence back after inserting the row in table. That key value I need to insert into child table. I am using Oracle 9i. I heard that there is some thing for this with Sybase. But don't know about the availability for Oracle. So far we are firing a query to get the sequence number and inserting with that value both in parent & child tables. This is making another DB trip (to get sequence).

Is is a DB/drivers feature or JDBC mandates to provides similar to that?



Assuming the key is a int,
after the insert is success, use
select max(primaryleycolumn) from table

This should fetch you the last primary key updates from a sequence.
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
insert into maintable(Primary_Key)
values(seq.nextval)

insert into childtable(Foreign_Key)
values(seq.currentval)


Make sure these are part of same transaction, for you could have another insert in between these two doing seq.nextval, which will change current value of the sequence.

Padma
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61206
    
  66

Assuming the key is a int,
after the insert is success, use
select max(primaryleycolumn) from table


Not a good idea. This sets up a race condition in which it is possible that another record can be added to the DB in between the time that you add yours and the time that you try to fetch the new value.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Srinivas Rao
Greenhorn

Joined: Aug 05, 2004
Posts: 14
Thanks for replies.

What Padma Lalwani looks, it will solve my problem. Any still better solutions Post here.
Sripathi Krishnamurthy
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
Originally posted by Bear Bibeault:


Not a good idea. This sets up a race condition in which it is possible that another record can be added to the DB in between the time that you add yours and the time that you try to fetch the new value.


Agreed that my solution will faulter. But I was looking at a generic solution. Sequence limits to a oracle DB. How do we make a generic solution that will work for all Databases?
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

If your database supports it (and this is not always the case) you can use the method Statement.getGeneratedKeys(). You have to add an additional integer to the execute command to enable this. Consult the Statement API.

If the command works, use it, although in my own personal experience I've had trouble getting the DBMS to support it. The select max record solution works depending on your transaction management and optimistic locking setup.

Fully manual approach: One general solution I've seen in practice is not to let databases control sequence generation. This is usually a pretty heavy solution, though, since you then have to implement your own singleton sequence generator object and/or service.

Fully automated approach: Finally, you can use java database tools in J2EE environments such as Enterprise EJBs and JDO that, if I'm not mistakenly, can be set to insert sets of data, ie parents and children together, and insert the parent keys as its created.


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting Primary key when inserting