Insert rows (parent and children) programmatically

Question

I am using Spring and JDBCTemplate.

The scenario is a CUSTOMER table and ORDERS table - parent-child relationship.

I want to do an insert (for example 1 customer and 5 orders) - but I am unsure how you programmatically insert a row in the CUSTOMER table (some how get hold of the Oracle generated unique id), and then insert the corresponding 5 rows in the child table, ORDERS, with the unique id created by the customer insert. This unique id obviously maintains a relationship between a customer and their orders.

Any help much appreciated.

PS - Code with example SQL on how this is done in Spring Framework would be fantastic - something fairly rough just to give me the basic idea.

Answer 1

DECLARE
  newid INTEGER;
BEGIN

  INSERT
    INTO customer (name)
  VALUES ('John Doe')
  RETURNING id
  INTO newid;

  INSERT
    INTO orders (customer, order)
  VALUES (newid, 'Order1');

  INSERT
    INTO orders (customer, order)
  VALUES (newid, 'Order2');

  INSERT
    INTO orders (customer, order)
  VALUES (newid, 'Order3');

END;

Answer 2

Check the update method in JDBCTemplate whcih takes a KeyHolder object. After execution that Keyholder objects contains the generated key.

Answer 3

I don't know anything about JDBC or Spring but Quassnoi has given you one Oracle way.

But if your framework doesn't know how to use the RETURNING clause... you could use the currval property of the sequence.

SEQUENCE.Currval is a session local "variable" that contains the last value that session got from the sequence.

BEGIN

  INSERT
    INTO customer (ID, name)
  VALUES (cust_seq.nextval, 'John Doe');

  INSERT
    INTO orders (customer, order)
  VALUES (cust_seq.currval, 'Order1');

  INSERT
    INTO orders (customer, order)
  VALUES (cust_seq.currval, 'Order2');

  INSERT
    INTO orders (customer, order)
  VALUES (cust_seq.currval, 'Order3');

END;

Answer 4

Another way would be to use a sequence to generate the ID :

SELECT seq.nextval FROM DUAL -> into a variable

Then use this number as your unique ID rather then have the database auto-create it.

Similar to the above suggestion, except that if someone inserted another customer in before you had inserted all their orders then you would end up with those orders under the wrong customer.