Get last ID of INSERT statement

[shaf maff]

Hi Guys

I have the following method in my DB connection class:

public void exUpdate(String q) {
try {
stmt.executeUpdate( q );
} catch(SQLException e) { System.out.println(e.Message()); }
}

In my main class I run the following query:

DB.exUpdate("INSERT INTO table values(DEFAULT,'2','test');

How do I get the ID of the above query?





.

[Avi Abrami]

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

Good Luck,
Avi.

[arulk pillai]

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]

Thanks. I managed to work it out. I am using the following code in my class and it works fine:

public int performKeys(String Query) {
PreparedStatement pstmt;
int key = 0;
try {
pstmt = conn.prepareStatement(Query, Statement.RETURN_GENERATED_KEYS);
 
pstmt.executeUpdate();
ResultSet keys = pstmt.getGeneratedKeys();

keys.next();
key = keys.getInt(1);
keys.close();
pstmt.close();
conn.close();
} catch (Exception e) { e.printStackTrace(); }
return key;
}

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]

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

PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) 

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.

 pstmt.executeUpdate();ResultSet keys = pstmt.getGeneratedKeys();   

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.

 keys.next();
key = keys.getInt(1);   

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

  while(keys.next){
           key = rs.getInt(1); //get the first index
        } 
  

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

[Scott Selikoff]

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]

Originally posted by shaf maff:

keys.next();

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]

Thanks for the replies guys!

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

[Scott Selikoff]

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".