MySQL auto increment

[Tay Thotheolh]

Hi. I have an 'ID' column for my database (MySQL) which is set to auto_increment. Everytime I add a row, the 'id' auto_increments. How do I get the auto_incremented 'ID' value ?

I doubt if I get the most recently added row would work because what happen if there maybe a chance someone did an insert after you very quickly ?

[Martijn Verburg]

Originally posted by Tay Thotheolh:
Hi. I have an 'ID' column for my database (MySQL) which is set to auto_increment. Everytime I add a row, the 'id' auto_increments. How do I get the auto_incremented 'ID' value ?

I doubt if I get the most recently added row would work because what happen if there maybe a chance someone did an insert after you very quickly ?

Hi Tay,

What are you trying to get the latest ID for and which language are you using?

[Scott Selikoff]

Read the JDBC API on getGeneratedKeys() command. Also, most large systems do not use built-in database auto-increment features since they are difficult to code around (as you've discovered).
[ September 02, 2008: Message edited by: Scott Selikoff ]

[Paul Sturrock]

I doubt if I get the most recently added row would work because what happen if there maybe a chance someone did an insert after you very quickly ?

If you do your insert and select inside one transaction it should. Depending on your transaction isolation, you should not see any data inserted by another process while in your transaction.

[Tay Thotheolh]

I am trying to get the latest id to do another insert into another table.

Yes, I just read of using the getGeneratedKeys() for MySQL while waiting for replies here. Maybe I should try the getGeneratedKeys().

[Scott Selikoff]

Originally posted by Tay Thotheolh:
I am trying to get the latest id to do another insert into another table.

This is part of why its better to avoid using generated keys and create your own. For example, lets say you have a service that create 2 records, the second using the id of the first. By relying on the database to generate the keys, you have to stop in the middle of the service, connect to the database, and process the results. If you generate your own keys you can perform both inserts at the same time, one right after the other.

Originally posted by Tay Thotheolh:
Maybe I should try the getGeneratedKeys().

Keep in mind getGeneratedKeys() is a driver dependent feature, some support it well, some do not.

[Campbell Ritchie]

Isn't there a MySQL function called last_insert_id()? I think that is what you are looking for. Try here and use ctrl-F "last".

[Scott Selikoff]

Originally posted by Campbell Ritchie:
Isn't there a MySQL function called last_insert_id()? I think that is what you are looking for. Try here and use ctrl-F "last".

Doesn't work well in multi-threaded environment because I believe you could get the value someone else inserted.

[Tay Thotheolh]

I am not too familiar on using getGeneratedKeys so can someone help me ? The code is below:

public int createOrder(order order) {
        int result = -1;
        boolean ok = false;
        try {
            PreparedStatement stmt = con.prepareStatement("insert into ktphoto.orderinfo (username,ordercomment,orderdatetime) values (?,?,?)");
            stmt.setString(1, order.getUsername());
            stmt.setString(2, order.getOrdercomment());
            stmt.setTimestamp(3, order.getOrderdatetime());            
            int num = stmt.executeUpdate();
            if (num == 1) {
                ResultSet rs = stmt.getGeneratedKeys();
                System.out.println("ResultSet: "+rs.toString());
                if(rs!=null){
                    while(rs.next()){
                        System.out.println("Row: "+rs.getRow());
                        System.out.println("Order ID: "+rs.getInt("orderid"));
                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }

It returns row: 1 but it throws something that orderid column is not found ?

Did I miss anything out ? How should I modify it ?

[Tay Thotheolh]

One other thing, this is the DB create table sql:

create table if not exists orderinfo(
orderid int(10) not null auto_increment,
username varchar(100) not null,
ordercomment varchar(10000) null,
orderdatetime timestamp,
primary key(orderid)
);

[Scott Selikoff]

First off, rs from getGeneratedKeys() will never be null, so no need to check for it (it will be en empty result set object if none present). I tend to use rs.getInt(1) to read the generated key, since its usually a result set of 1 column. Also, I'm not sure what the call to getRow() does, seems unnecessary to me.
[ September 02, 2008: Message edited by: Scott Selikoff ]

[Scott Selikoff]

Oh and lastly, you can use "if(rs.next())" instead of "while(rs.next())" since you would never expect your INSERT SQL statement to return more than 1 row of keys. In situations like this, I prefer to use if(rs.next()) to read the data, then provide a second if(rs.next()) that throws an error if a second row is found.

[Avi Abrami]

Tay,
Oracle database has an INSERT..RETURNING sql statement just for this purpose.
Oracle also provides a free to use database that can also be shipped with your commercial software applications for no charge.
Do an Internet search for "Oracle database express edition (XE)".

Good Luck,
Avi.

[Scott Selikoff]

What can I say, this post inspired me to write an article on the subject: Database Key Generation in Java Applications
[ September 03, 2008: Message edited by: Scott Selikoff ]

[Campbell Ritchie]

Originally posted by Scott Selikoff:
[last_insert_id()]

Doesn't work well in multi-threaded environment because I believe you could get the value someone else inserted.

Agree; it is probably only safe to use last_insert_id() in real life inside a transaction.

[deirdre lee]

Hi,

I was just wondering if getGeneratedKeys is safe to use in multi-threaded environment. If you insert a row into a table and then call the getGeneratedKeys method immediately (as in Scott's example at http://www.selikoff.net/blog/2008/09/03/database-key-generation-in-java-applications/), does this count as one transaction?

Thanks,
Dee

[Paul Sturrock]

dee, please check your private messages.

[Tay Thotheolh]

Cool .. I didn't know my topic could spark off some blog. Yup.. I think the getInt(1) is working. I don't know the deep ends of JDBC except for the normal database access stuff. I might want to explore deeper next time.

About the Oracle, my environment is MySQL since most hosting uses Apache / Tomcat / MySQL , but thanks for the information on Oracle's XE and stuff.

I wonder why not much things have been done to address this issue of key generation since I noticed that this topic is rather common in Java forums as I was looking for a solution and most database makers prefer to just leave this auto generate key thing there and ignore it. Hopefully someone could come up with a clever way to tackle this issue and make it easier.

[Martijn Verburg]

Originally posted by Scott Selikoff:
What can I say, this post inspired me to write an article on the subject: Database Key Generation in Java Applications

[ September 03, 2008: Message edited by: Scott Selikoff ]

Good stuff Scott, I've got a group of Grads devouring it as I type

[Campbell Ritchie]

Originally posted by dee:
Hi,

I was just wondering if getGeneratedKeys is safe to use in multi-threaded environment. If you insert a row into a table and then call the getGeneratedKeys method immediately (as in Scott's example at http://www.selikoff.net/blog/2008/09/03/database-key-generation-in-java-applications/), does this count as one transaction?

Thanks,
Dee

No.

Not unless you have START TRANSACTION; before it and COMMIT; afterwards.

[Scott Selikoff]

Originally posted by Campbell Ritchie:
No.

Not unless you have START TRANSACTION; before it and COMMIT; afterwards.

Um, I'm going to disagree on this one, sorry Campbell. This may be a driver dependent issue (as I mentioned in my post getGeneratedKeys() is very driver dependent) but I would think as long as you did not reuse the PreparedStatement object, the generated keys would be available regardless of transaction status since they are tied to that specific command. I could be wrong though ;)

[Scott Selikoff]

Originally posted by Martijn Verburg:
Good stuff Scott, I've got a group of Grads devouring it as I type

Awesome, get them hooked on The Daily WTF at the same time, it'll give them perspective.

[Martijn Verburg]

Originally posted by Scott Selikoff:
Awesome, get them hooked on The Daily WTF at the same time, it'll give them perspective.

Already done , they already hate the cartoons and were suitably shocked at how unprofessional the real world is, you're totally right about setting them straight on that as early as possible.

[Scott Selikoff]

Originally posted by Martijn Verburg:


Already done , they already hate the cartoons and were suitably shocked at how unprofessional the real world is, you're totally right about setting them straight on that as early as possible.

No one likes the cartoons. Ok I've officially hijacked this thread at this point, my bad. Hopefully the original issue has long since been resolved.

[Campbell Ritchie]

I appear to be mistaken; sorry.

[Scott Selikoff]

Originally posted by Campbell Ritchie:
I appear to be mistaken; sorry.

Its ok, you could have just have easily been right. In fact I'm sure there's some driver out there that does do it that way.