aspose file tools*
The moose likes JDBC and the fly likes Get last ID of INSERT statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Get last ID of INSERT statement" Watch "Get last ID of INSERT statement" New topic
Author

Get last ID of INSERT statement

shaf maff
Ranch Hand

Joined: Sep 07, 2008
Posts: 180
Hi Guys

I have the following method in my DB connection class:



In my main class I run the following query:



How do I get the ID of the above query?





.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1130

Shaf,
Lookup "auto generated keys" in the java documentation.
Start with interfaces "java.sql.Connection" and "java.sql.Statement".

Good Luck,
Avi.
arulk pillai
Author
Ranch Hand

Joined: May 31, 2007
Posts: 3219
Are you using database generated IDs (e.g. sequences, etc)? If yes, You will have to do a select query to get the id back.


Java Interview Questions and Answers Blog | Amazon.com profile | Java Interview Books
shaf maff
Ranch Hand

Joined: Sep 07, 2008
Posts: 180
Thanks. I managed to work it out. I am using the following code in my class and it works fine:

I am still pretty new to this and don't fully understand what is going on. I tried reading up on it to no avail. Is it possible if someone could break this code down for me?








.
[ September 22, 2008: Message edited by: shaf maff ]
arulk pillai
Author
Ranch Hand

Joined: May 31, 2007
Posts: 3219
Interesting, you learn something every day. If you look at the API http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html




Preparedstatements are like Statements (e.g. like cursors) that are pre-compiled and more efficient than statements. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#getGeneratedKeys();

Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys.




You can loop through the keys and retrive them one by one. In this case you only inserted one row so you are getting it out as show below.




If you have many rows inserted then you can do something like



Also have a look at this http://exampledepot.com/egs/java.sql/GetRsData.html
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I recently wrote an article on this subject.

Performing a SELECT statement to read keys is not often recommended in a multi-threaded environment since there's a chance (depending on your transactional/lock levels) you could get the wrong key. JDBC's getGeneratedKeys() command is a good option but it is not always supported by all databases. I tend to prefer generating keys yourself since it gives you complete database independence (not all databases generate keys in the same way, see Oracle), and gives you control to make decisions in your application before ever contacting the database.


My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Originally posted by shaf maff:



This line worries me alot. You should *ALWAYS* check the return value of keys.next() since you may have unexpected behavior. While the common situation is to use a while loop, such as "while(keys.next()) { ... }", if you expect a single record return (such as a "SELECT count(*) FROM") you can use "if(keys.next()) { ... }". But you should never ignore the conditional!
shaf maff
Ranch Hand

Joined: Sep 07, 2008
Posts: 180
Thanks for the replies guys!

Scott: I have a try/catch statement. Shouldnt that suffice?
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

No. It's bad practice to throw errors that are easily caught. Generally you should only throw and catch errors that are more difficult to work with at runtime such as "connection not available".
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Get last ID of INSERT statement