Batch insert into multiple table with sequence

2
votes

We are facing a problem while implementing a batch insert code in our java application. Below is our table structure -

  • We have three tables : Table1, Table2 & Table3.
  • Primary key of Table1 (SeqId, which is essentially sequence) is foregn key for rest two tables (Table2 & Table3).

Table Structure

Current Implementation :

As part of current batch operation, for each iteration we are inserting sequentially in Table1 first followed by Table2 & Table3.

[Pseudo Code]

INSERT INTO Table1(SeqId, OtherField1) VALUES (ID_SEQ.nextval, 'Some Val');
INSERT INTO Table2(SeqId, OtherField1) VALUES (someId2, ID_SEQ.currval, 'Some Val');
INSERT INTO Table3(SeqId, OtherField1) VALUES (someId3, ID_SEQ.currval, 'Some Val');

What we are trying :

Now we are trying to implement batch insert. We have created three seperate PreparedStatement and for each iteration we are doing statement.addBatch(); and at the very end we are executing statement.executeBatch() sequentially.

Now the problem is ID_SEQ.currval will always take the current sequence value which is not what we want. Agaist each value of SeqId in Table1, we want correponding row in Table2 and Table3.

Is it possible to implement this using batch insert (not a procedure or anonymous block)?

We are using Oracle 11g and Java8

2
javaoracle11gbatch-insert

2 Answers

1
votes

I think you can use a variable to store the sequence value

DECLARE
   ID_SEQ PLS_INTEGER;
BEGIN
   ID_SEQ  := ID_SEQ.nextval;
   INSERT INTO Table1(SeqId, OtherField1) VALUES (ID_SEQ  , 'Some Val');
   INSERT INTO Table2(SeqId, OtherField1) VALUES (someId2, ID_SEQ  , 'Some Val');
   INSERT INTO Table3(SeqId, OtherField1) VALUES (someId3, ID_SEQ  , 'Some Val');
END;
0
votes

You can retrieve the next value of the sequence and populate your tables with this value.

int seq_val = 0;
ResultSet rs = stmt.execute("SELECT ID_SEQ.NEXTVAL FROM DUAL");
if ( rs.next() ) {
  seq_val = rs.getInt(1);
}

Statement statement = connection.createStatement();
statement.addBatch("INSERT INTO Table1(SeqId, OtherField1)" + "VALUES ("+seq_val+",'some_val')");

statement.addBatch("INSERT INTO Table2(ID,SeqId, OtherField1) " + "VALUES ('some_id',"+seq_val+",'some val')");

statement.addBatch("INSERT INTO Table3(ID,SeqId, OtherField1) " + "VALUES ('some_id',"+seq_val+",'some val')");

statement.executeBatch();

//pardon any mistakes in syntax as I didn't use any code editor to validate. cheers