This week's book giveaway is in the Artificial Intelligence and Machine Learning forum.
We're giving away four copies of Transfer Learning for Natural Language Processing (MEAP) and have Paul Azunre on-line!
See this thread for details.
Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

Retrieve last insert id after insert

 
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, all!

I have ran into some difficulties when it comes to retrieving the last insert id after an insert query. Normally to obtain the last insert id directly after an insert statement, I write something like the following



In MySQL there is a function called LAST_INSERT_ID() that you can use to retrieve the last insert id after an insert operation, in a select query. I am using the embedded apache derby database so I am wondering what's the proper way to obtain the last insert id from a table in a select query or do you have to use another approach like creating a last-insert-id-table where you store the last inserted id's? What am I missing here? Very puzzled about this.

Many wishes to all!
Robert.
 
 
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
https://db.apache.org/derby//docs/10.0/manuals/reference/sqlj229.html
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Holloway!

Thank you very much for your reply. I have read the document on apache derby labeled Autogenerated Keys. My problem is that I wan't to retrive the last inserted id from a table in a method way after an insert query like in an actionPerformed-method something like this for example



Best regards,
Robert.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The reason that getGeneratedKeys() returns a ResultSet is that an INSERT can insert more than one record at a time.

If you only insert one record, then only one key will be returned.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, again!

Thanks for the replies.

Ok, I got it. But it's not clear to me if or how - in oracle - I can retrieve the last insert id from one class that handles the insert procedures, in another that process form data, just as an example. Am I missing something big here or what is the general procedure to first save the last insert id in oracle and then retrieve it since there is no function for that like MySQL's LAST_INSERT_ID() function. I'm sorry if I am confusing someone but I have given this a lot of energy and thought. Where am I wrong? Or maybe you can only work with the last insert id during insert-procedures?

Best regards,
Robert!

 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
getGeneratedKeys() is a JDBC Version 3 method and Oracle supports it just like MySQL does, despite the fact that the internal operations of the two databases are very different.

If you're looking to do an INSERT in one section of code and retrieve the generated ID in an unrelated section of code or task, that's not going to work. In a busy database, other tasks could have inserted any number of rows between the INSERT you're interested in and the time you ask about it. Only by asking when the INSERT itself is done can you get a reliable answer.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:If you're looking to do an INSERT in one section of code and retrieve the generated ID in an unrelated section of code or task, that's not going to work.



I think I understand and by the way, this is exactly what I am trying to do. I have a class called FormDataHandler that extends Thread and store the form data in my table, tbl_mediatitle. I could post it here but I think the description tells it all what it does. In it's run method I collect the form data, strings, ints, booleans and so on and bind them to my PreparedStatement object using the set methods like setString, setInt and stuff like that. I also save list and table data in PreparedStatements.

Then for example I have another class called PersonRecordManager that save information about actors and that information comes from forms in a JDialog and it is here that I need to use the last insert id of the table tbl_mediatitle to store this information in tables like tbl_castrole and tbl_crewrole and etcetera that is binded to the table tbl_mediatitle via foregin keys.
I could post the code for theese classes but then that wouldn't be sscce-friendly. So my question is how an approach like this is done in the real world? Where would the insert take place between to sections of code out here?

Best regards to all!
//Robert.

 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about something like this



Wouldn't that return the last insert id from the table the variable tableName points at? Any comments are greatly much appreciated. Criticism is a teacher.

//Robert.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can't really tell based on that, by why are you making the form processing be an independent Thread? Especially in Swing, where the UI itself runs in its own independent thread?

Desktop apps often work by opening/creating "Projects" or "Documents", which serve as the central data storage and co-ordination point for the work being done. So if you had one of those, it would be easy for the inserting class to store the returned keys as a Collection in the project object and for the downstream class object to retrieve it from the project object.
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, and yes since you posted your example while I was still typing the previous response, your example is a good option.

Never mind. No, it's not. Like I said, other threads might have added their own keys. You can only trust the keys returned at the time you did the SQL insert. I thought that's what you were doing, then realized that your example wasn't the INSERT at all.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr.Holloway

Thank you so much for your replies. I have winded up in a really tough situation when its come to certain parts of my project. In my project I have two JDialogs, one - MediaTitleDialog - is for adding information about media titles and the other - PersonIndexDialog - is for adding information about film persons. How would something like that be done in the real world? In my tables I have foreign keys that link my tables together. It's a complicated project and I am really stuck but I am so stubborn and don't won't to give up. What are your advices?  

Best regards,
Robert!  
 
Tim Holloway
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, as I said, there's usually a central data container class, which the "document" or "project" (the name isn't really important, as long as you understand its use). In a really complex app like Microsoft Word where you can be editing multiple documents at the same time, the central data container might literally be just a container of documents/projects. but for a simple app, the project object would be a singleton.

Everything important involving data or state is stored in the project object or one of its dependent objects. When I want to run a series of Dialogs, I generally create a GUI Model object (MVC Model) and initialize it to what the dialog should initially display. That may include copying data values from the project object. Then I construct a View and bind the View and Model together with Controllers and present (make visible) the View (Dialog).

Most Dialogs have something like OK and Cancel options. If the user dismisses the View with OK, then data values are copied from the GUI Model up into the project object and then the GUI Model can be disposed of. If the user selects a Cancel option, then nothing gets copied up into the project and the GUI Model is disposed of without further action. Sometimes there will be a Revert/Undo option that resets the dialog control values to their previous values. This can be done by simply re-copying them from the project object into the GUI Model object.

I do this usually for each Dialog in my app. However, if I'm doing a workflow series of dialogs (such as a "Wizard"), I may use a common GUI Model that attaches to each Dialog in the series in turn, since Wizards often define their future dialog selections and/or values based on the previous steps.

The main thing is simply to have a common, shared location where one actor can put information for another actor to pick up and use.
 
It's exactly the same and completely different as this tiny ad:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic