aspose file tools*
The moose likes JDBC and the fly likes how to get the inserted record by using resultset in java? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get the inserted record by using resultset in java?" Watch "how to get the inserted record by using resultset in java?" New topic
Author

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

arul micheal
Greenhorn

Joined: Jul 04, 2007
Posts: 9
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

Joined: Jul 31, 2007
Posts: 23
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 ;-)


Never attribute to malice that which is easily explained by incompetence.<br />SCJP (1.5)
Remko Strating
Ranch Hand

Joined: Dec 28, 2006
Posts: 893
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


Remko (My website)
SCJP 1.5, SCWCD 1.4, SCDJWS 1.4, SCBCD 1.5, ITIL(Manager), Prince2(Practitioner), Reading/ gaining experience for SCEA,
 
Don't get me started about those stupid light bulbs.
 
subject: how to get the inserted record by using resultset in java?