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

Know auto incremented pimary key

ramnna jain
Ranch Hand

Joined: Mar 16, 2009
Posts: 129

When I insert a new record into a table then is it possible to know the newly generated primary key for this record if the primary key is auto increment.Is there any efficient way to know he same while firing insert query.

please help..

Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3829

You can fire an select query to find out. MySQL you can do "SELECT LAST_INSERT_ID()" to get the last inserted (in auto generated column) by that specific connection. This is connection specific (other insertions from different connections won't affect the result). Which DB are you using?

SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

ramnna jain wrote:When I insert a new record into a table then is it possible to know the newly generated primary key for this record if the primary key is auto increment.


Do you mean, an auto genreted key value, then Use Statementl#getGeneratedKeys() after update, insert statement.
ramnna jain wrote:
Is there any efficient way to know he same while firing insert query.


No, You need to know the schema, If not then try using DatbaseMetadata
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Vijitha Kumara wrote:You can fire an select query to find out. MySQL you can do "SELECT LAST_INSERT_ID()" to get the last inserted (in auto generated column)

Isn't that take one more database dip, just to find out the currently inserted row id?
Vijitha Kumara wrote:
by that specific connection. This is connection specific (other insertions from different connections won't affect the result).

No such requirements, if you used the previous Java API URL I posted, Its full of your need

Jhakda Velu
Ranch Hand

Joined: Feb 26, 2008
Posts: 166
Hi
Correct me if I am wrong, isn't there a scope for error in Vijitha's response, say, if another process has inserted a row into the table? Even if we set auto commit to false?

Jhakda


If I become filthy rich, I'll sponsor research for painless dental treatment at Harvard Medical School. Thats why,I'm learning Java.I have 32 teeth, 22 are man made.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

That's why I quote in my second reply, though the chances are rare, but there may be the case when multiple user inserting into same table. One way we can avoid this by using, Store Procedure or function which takes the insert values as parameter and return an auto incremented id,


Please check out the syntax for SP, its not correct.
ramnna jain
Ranch Hand

Joined: Mar 16, 2009
Posts: 129

I am using MySQL.

This entire discussion is very helpful.

Thank you very much Vijitha, Sagar for your kind help.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

You're Welcome
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Know auto incremented pimary key