Insert rows (parent and child) programmatically

Question

I am using Spring and JDBCTemplate.

The script is the CUSTOMER table and the ORDERS table are the parent-child relationships.

I want to do an insert (for example, 1 customer and 5 orders), but I'm not sure how you programmatically insert a row into the CUSTOMER table (some of them access the unique identifier of Oracle), and then insert the corresponding 5 rows in the child table, ORDERS , with a unique identifier created by the client insert. This unique identifier obviously supports the relationship between the customer and their orders.

Any help is greatly appreciated.

PS - The code with an example of SQL on how this is done in the Spring Framework will be fantastic - something pretty crude to give me the basic idea.

Answer 1

Check the update method in the JDBCTemplate that accepts the KeyHolder object. After executing this object, the Keyholder contains the generated key.

The Spring documentation provides an example usage here .

Answer 2

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 3

JDBC Spring, Quassnoi Oracle.

, RETURNING... currval .

SEQUENCE.Currval - "" , , .

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

:

SELECT seq.nextval FROM DUAL →

, .

, , - , , .