aspose file tools*
The moose likes JDBC and the fly likes how to know the last auto-increment number using JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to know the last auto-increment number using JDBC" Watch "how to know the last auto-increment number using JDBC" New topic
Author

how to know the last auto-increment number using JDBC

Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
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


I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury
Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
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...

David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Nov 27, 2001
Posts: 1561
David, how about :


?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Nov 27, 2001
Posts: 1561
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

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Mar 06, 2001
Posts: 13459

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
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Jun 26, 2001
Posts: 403
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 ]
Ta Ri Ki Sun
Ranch Hand

Joined: Mar 26, 2002
Posts: 442
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

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Nov 27, 2001
Posts: 1561
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..
lakshmi rajagopal
Greenhorn

Joined: Jun 22, 2002
Posts: 5
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.


lakshmi
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

what product name?
Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561

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

Joined: Jul 09, 2001
Posts: 1879


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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to know the last auto-increment number using JDBC
 
Similar Threads
How to assigned auto-generated PK to its FK - Please suggest
can we preset value of auto-increment ?
Getting the last three added rows to a table HSQLDB
Modifying Auto Increment column using servlets
auto increment a number