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....
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)