CODEWITHSUNDEEP
javasqlms-accessjdbc
Katherine
Katherine

Reputation: 573

How can I insert new values into two related tables?

I have a store program in java and a database made in access. I already have 2 tables in my database which are the customers table and the products table.

I want to add an orders table wherein it's primary key is an autonumber and an order_line table to complete this app. I want to have tables like this..

customer(cust_id, name, ....)
orders(order_no, cust_id, date_purchased,...)
order_line(order_no, product_id, ...)
products(product_id, product_name, price,....)

When the customer purchased the products, i could insert new values to the orders table. The thing that is not clear to me is how could i insert also in the order_line table, because the order_no I created in access is of type autonumber.

Would I make a select statement first to get the order_no value to put it to the order_no in order_line's table? Or I need to put this in one query only.

Anyone with experience to this? Any advice is appreciated.

Upvotes: 1

Views: 576

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108939

The JDBC-way (if you like database-independence), is to use the getGeneratedKeys() method of statement.

Use setAutoCommit(false), then execute the first query with the option Statement.RETURN_GENERATED_KEYS (eg for PreparedStatement).

Then use the getGeneratedKeys() method to retrieve the key (note: reference by column name, as the exact implementation and number of returned columns depends on the driver implementation.

And execute the second statement with that retrieved key.

Finally, commit().

Upvotes: 2

Vikdor
Vikdor

Reputation: 24124

The insertion into orders and order_line table should happen in a single transaction. While doing so, if you are using plain JDBC to insert record into orders table, you can register the order_no as an OUT parameter in your CallableStatement and get the value after the statement is executed and use to set the order_no attribute on the order_line records.

 // begin transaction
 connection.setAutoCommit(false);

 CallableStatement cs = connection.prepareCall(INSERT_STMT_INTO_ORDERS_TABLE);
 cs.registerOutParameter(1, Types.INT);
 int updateCount = cs.execute();
 // Check the update count.
 long orderNo = cs.getInt(1);

 // CallableStatement csLine for inserting into order_line table
 // for (OrderLine line: orderLines) {
 //     Set the orderNo in line.
 //     set paramters on csLine.
 //     csLine.addBatch();
 // }
 // run the batch and verify update counts
 connection.commit();

 // connection.rollback() on error.

Upvotes: 2

Related Questions