aspose file tools*
The moose likes JDBC and the fly likes MySQL auto increment Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL auto increment" Watch "MySQL auto increment" New topic
Author

MySQL auto increment

Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

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?


Cheers, Martijn - Blog,
Twitter, PCGen, Ikasan, My The Well-Grounded Java Developer book!,
My start-up.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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 ]

My Blog: Down Home Country Coding with Scott Selikoff
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
Sheriff

Joined: Oct 13, 2005
Posts: 39415
    
  28
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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
I am not too familiar on using getGeneratedKeys so can someone help me ? The code is below:



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
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
One other thing, this is the DB create table sql:

Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
Sheriff

Joined: Oct 13, 2005
Posts: 39415
    
  28
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
Greenhorn

Joined: Sep 03, 2008
Posts: 2
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

dee, please check your private messages.
Tay Thotheolh
Ranch Hand

Joined: Aug 07, 2008
Posts: 84
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
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

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
Sheriff

Joined: Oct 13, 2005
Posts: 39415
    
  28
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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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
Sheriff

Joined: Oct 13, 2005
Posts: 39415
    
  28
I appear to be mistaken; sorry.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL auto increment