CODEWITHSUNDEEP
javasqloracle-databasespring
Vidar
Vidar

Reputation: 6673

Insert rows (parent and children) programmatically

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.

Upvotes: 5

Views: 3954

Answers (4)

Bhushan Bhangale
Bhushan Bhangale

Reputation: 10987

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

The Spring documentation has an example of usage here.

Upvotes: 5

Gandalf
Gandalf

Reputation: 9855

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.

Upvotes: 0

Mark Brady
Mark Brady

Reputation:

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;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425713

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;

Upvotes: 1

Related Questions