0

I have two MySQL tables, Persons and Addresses. In Persons table there is column id_address which is foreign key for Addresses column with same name. Column id_address in Addresses has AUTO_INCREMENTed value. So when I want to insert new person, I insert address which sets LAST_INSERT_ID() to new value, then this last id I use for Persons.id_address.

So my question is, is there any option to set batch insert for these two tables, and then insert more rows in one step?

I'm using Java with MySQL driver and I need this without ORM framework.

EDIT: I found it's not possible, related answer is here: Two different prepared statements in one single batch

Community
  • 1
  • 1
Masi
  • 95
  • 3
  • 9

1 Answers1

1

If one of the SQL query is SIMPLE statement then both the queries can be executed in a batch.

Here is an example:

In col1 user specified value will be inserted and in col2 value returned by last_insert_id will be stored.

tbl2 has only one column which is auto-increment but you can have any no. of columns. The only restriction is this query has to be simple query (i.e. it CAN NOT be prepared statement).

PreparedStatement pStmt = conn.prepareStatement("insert into tbl1 (col1, col2) (select ?, LAST_INSERT_ID());");
int i=0;
while(i < 2) {
   pStmt.setInt(1, 20);
   pStmt.addBatch("INSERT INTO tbl2 values();");
   pStmt.addBatch();
   i++;
}
pStmt.executeBatch();

This will first insert into tbl2 followed by insert into tbl1.

Prashant
  • 11
  • 1