This week's book giveaway is in the Performance forum.
We're giving away four copies of The Java Performance Companion and have Charlie Hunt, Monica Beckwith, Poonam Parhar, & Bengt Rutisson on-line!
See this thread for details.
Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to get the inserted record by using resultset in java?

 
arul micheal
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi friends,

I have one doubt in wirting database code in java..

table a( id , name, description);
table b(id, salary);
I have two table( a & b).. Whenever i insert a record into table a, i need to insert a record in table b also by using primary key of these two tables. we can do it in java. But my problem is that



i am using mysql. i have created a table "A" and make auto increment for column "id". According to mysql, auto increment will generate the unique number automatically.. when we insert a record for table "A",we don't want
to insert a value for column "id" because it will be inserted automatically.

Once a record for table "A" is inserted, i need to get that id from table "A". becos that id value need to be inserted into table "B".

how to do it in java?

There is one way to do it. But i wont like to do that way.

the way is that we should lock the table A and make the select query
and get the nth record and then get the id from n th record.
After that, insert a record into table B using that id getting from select query.

My concern is that i don't want to make select query. Instead of it, get the inserted a record result set and get id from resultset.. ,,, then insert that value into table B....

how can i do this?

can you please tell about it?

what are all ways we can do it
 
S J Martin
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Firstly, it looks like you've two tables with a 1:1 mapping, so I question if that is the case and why 1 table isn't more appropriate.

If you have a 1:Many (e.g. salary over time) then I'd have two transactions:
1. Create a new "Person" (done once)
2. Add that persons salary details (done many times, but after you've selected "the person" and hence your app knows the "id" of the person (and further "salaries" can be added as required).

If (for some other reasons) you definitely need two tables, and the insert into table b is required when you insert into table a I'd look a further options, e.g.
1. An updatable view (so it will look like only one table - don't know if your DB supports this concept).
2. A stored procedure that takes care of both tables.
However, these feel *much* less satisfactory than the first two and I'd only consider if there were strict reasons on not changing the schema (nor even allowing a new view definition).
3. Something else I've not yet thought of ;-)
 
Remko Strating
Ranch Hand
Posts: 893
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
With MySQL you can retrieve the autoid by the following statements



See the following link for more information

statement.getGeneratedKeys

This is how I've solved your problem in the past
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic