• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL auto increment

 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 50176
79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One other thing, this is the DB create table sql:

 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 50176
79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
dee, please check your private messages.
 
Tay Thotheolh
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 50176
79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 50176
79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I appear to be mistaken; sorry.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic