Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Get last ID of INSERT statement

 
shaf maff
Ranch Hand
Posts: 180
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3380
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
shaf maff
Ranch Hand
Posts: 180
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3380
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 3896
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 3896
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 180
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the replies guys!

Scott: I have a try/catch statement. Shouldnt that suffice?
 
Scott Selikoff
author
Saloon Keeper
Posts: 3896
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic