• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inserting records to mysql

 
Harish Shivaraj
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all.

Not sure if this is the right forum to ask a general practice question or how its done in the industry. But anyway here its goes.
I have a database with the User, Address and Vehicle tables. And tables are associated. The User could have 0 or more vehicles and the user could have one address.
Given the database design, I need have the following sequence of actions in my java code

1. insert User record to user table
2. To a select to find the userid
3. insert vehicle record to Vehicle table with the userid (FK)
4. insert address record to Address table with userid (FK)

As it is, i use preparestatment to insert the record and then I do select to identify the recently added record to fetch the userid and two further subsequent insert to Vehicle and Address table.
I find this rather convoluted approach or is this how its done?

Or is there a better way when it comes to inserting records to RDBMS?

I appreciate you help in this matter.

Thanks
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some databases let you call getGeneratedKeys() to get the primary key without having to do step 2.

(We have a JDBC forum that is better for database questions. I've added this post to that forum as well)
 
Harish Shivaraj
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks very much generatedkey does provide me with the last generated key.

However hows does this work in multiple simultaneous insertion. Or does the mysql database engine serialize the process somehow?

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harish,
Not sure what you mean by multiple simultaneous insertion. You only insert one row in step #1 of your example. And in general, it returns a ResultSet so can include multiple keys.
 
Harish Shivaraj
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think its my poor explanation. But thanks for the clarifications. I really appreciate your help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic