• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Know auto incremented pimary key

 
ramnna jain
Ranch Hand
Posts: 129
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3913
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 167
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 129
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using MySQL.

This entire discussion is very helpful.

Thank you very much Vijitha, Sagar for your kind help.
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic