• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to know the last auto-increment number using JDBC

 
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi.
I have a table in my database with an auto-increment field, which is the primary key. What I'd like to know is how I can get this number after I insert a record into the table. I'm using MySQL 3.23.49. any ideas??
thanks
 
Andres Gonzalez
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Or... can I do
select count(*) from myTable; ???
before inserting the next record. In this way I know how many records i have.
how can I do this with JDBC...

 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Generally not. It's never quite that simple.
When using sequences supplied by the databse, I tend to select the next value before doing the insert rather than doing both at the same time.
I can't remember the exact way do do this in a dummy select, but I can have a look if you want.
 
Andres Gonzalez
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
David, how about :


?
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
the problem is that when the database is maintaining the sequence, the sequence doesn't necessarily have anything to do with the number of rows.
Your original question was:

I have a table in my database with an auto-increment field, which is the primary key. What I'd like to know is how I can get this number after I insert a record into the table.


I'm suggesting you pull the primary key value BEFORE you do the insert.
 
Andres Gonzalez
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes, you are right....
the problem is the following. My application enables a user to upload an image to the server. My upload class (jspsmartUpload) always overwrites the file in the server, so if a guy posts an image called computer.jpeg and another guy chooses the same name for his picture (could be a computer, but different image), now I get into trouble. the file is replaced and the first image uploaded is lost.
So I was thinking about renaming the image with the secuence number. In this way I'm curing myself. There's not going to be the same number.
Now, i insert the product information WITHOUT the image (inserting null in the image path). Then I do some sort of select to retrieve the information I have just inserted, rename the file I have uploaded with the secuence number i get and update the record with the full image path.
Sometimes I think I'm complicated all the things but I can't think of any other way to do it. If I have a category called computers, I know people will have their image called computer.jpeg and will want to upload it, replacing other people's files.
uff... so this is my problem. maybe you can give me any suggestions or ideas...
cheers
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You're still doing the insert first. Don't. Make a SQL call that ONLY returns the sequence number.
Then use the sequence number.
I'm not sure on the exact sytax, but usually something like "SELECT nextVal() as seq FROM <dummy table>"
All you are doing here is pretending to read a value from the database, where what you really want is the output from a database function (no idea what the function is called though)
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Of course if all you really want is a unique value so that images don't collide, don't use the database at all. You can build your own in several different ways, System.currentTimeMillis() being one (evil) way.
Dave
 
Ranch Hand
Posts: 1170
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why dont you do your own auto-increment. Of course you know you cant use count because the second you delete a record your whole numbering scheme is toileted.
I do this which is essentially an imitation of the internal operation of auto increment

Note that you cant include the +1 in the select statement because if no records are found the +1 will not happen. Now with the max ID I can safely insert new elements into the table on my own. Of course this is an exclusive access DB
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Most database vendors have there own flavour of supporting this( or workaround ). What database are you using? How many different users will be running the program simultaneously?
Oracle's sequences are easy to use as David pointed out.
Jamie
 
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have done this in mysql but can't find the code at the moment.
Basically what you do is after the insert issue the command "SELECT LAST_INSERT_ID()" from the same Statement object.
Something like:

Try it from the mysql cmd line.
Issue an insert statement, then run "select last_insert_id()".
As for the jdbc code above, the reason you need to use the "same" Statement is that it returns the last id you have just inserted, other solutions listed here (such as selecting the max(id)) will return the last id inserted into the table, but that might NOT have been issued by you, if there is a multi-user situation going on.
James.
[ June 25, 2002: Message edited by: James Swan ]
 
Ranch Hand
Posts: 442
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
if you want the last number, all you do is select the field holding the primary key , but set max rows to 1, and order by descending, should be lots less stress than counting

 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Starting to look a bit dangerous to me...

This is not thread safe and would more than likely break uniqueness.

This may be safer but I'm still dubious. LAST_INSERT_ID() wasn't designed to be a sequence generator but can be used a sequence generator...
It's more important to make sure that the keys are unique than the keys be sequential. Don't be fooled by the word 'sequence', I don't believe there is any promise of actual sequantial values.
Generally sequences are managed on a per-connection basis rather than per table. When you create a connection, it gets a bunch of unique numbers it is allowed to use. No other connection gets these numbers, so uniqueness is guuaranteed. If the connection closes there is no promise that unused numbers are returned hence continuous ranges are unlikely.
If we're still talking about a MySQL-specific solution, have a look at the docs for LAST_INSERT_ID at http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
Dave
are we getting any closer to a solution?
its only a sequence damnit
 
Andres Gonzalez
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hhmmm..... tricky question, wasn't it???


Of course if all you really want is a unique value so that images don't collide, don't use the database at all. You can build your own in several different ways, System.currentTimeMillis() being one (evil) way.


I'm starting to like evil solutions....
thanks guys..
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If your only requirement is to give unique name to the uploaded image and you are not very particular about the sequence numer then the solution is very simple. Your product name is going to be a unique one. So rename the image file like productname_image.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
what product name?
 
Andres Gonzalez
Ranch Hand
Posts: 1561
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


If your only requirement is to give unique name to the uploaded image and you are not very particular about the sequence numer then the solution is very simple. Your product name is going to be a unique one. So rename the image file like productname_image.


hhmmm.. i don't think so mate. What happens if you upload an image named computer.jpeg, but another user has already upload his computer.jpeg file? the file will be overwritten, and I do not want that to happen.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

This is just some code I whipped together and is not portable as the error codes are platform specific...heck they might even be mySQL version specific. It probably won't work efficiently for a high traffic site either.
Jamie
[ June 27, 2002: Message edited by: Jamie Robertson ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic