I have a Java application and am trying to do a series of batch inserts to a MySQL database. However, many of my tables are linked by foreign keys. So I don't know the value of the foreign keys to use because they are generated in a previous batch.
For example take these two tables:
parent
id
name
child
id
parent_id (required foreign key to parent.id)
name
I know how to import these without using batches:
//already initialized variables: Connection connection, List<ParentObject> parentObjects
ResultSet rs = null;
PreparedStatement psParent = null;
PreparedStatement psChild = null;
for(ParentObject parent: parentObjects){
psParent = connection.prepareStatement("INSERT INTO product (name) VALUES (?)", PreparedStatement.RETURN_GENERATED_KEYS);
psParent.setString(1, parent.getName());
psParent.executeUpdate();
int parentId = 0;
rs = psParent.getGeneratedKeys();
if (rs.next())
parentId = rs.getInt(1);
rs.close();
psParent.close();
for(ChildObject child : parent.getChildren()){
psChild = connection.prepareStatement("INSERT INTO child (parent_id, name) VALUES (?,?)");
psChild.setInt(1, parentId);
psChild.setString(2, child.getName());
psChild.executeUpdate();
psChild.close();
}
}
Now I am trying to use batches:
PreparedStatement psParent = connection.prepareStatement("INSERT INTO product (name) VALUES (?)");
PreparedStatement psChild = connection.prepareStatement("INSERT INTO child (parent_id, name) VALUES (?,?)");
for(ParentObject parent: parentObjects){
psParent.setString(1, parent.getName());
psParent.addBatch();
for(ChildObject child : parent.getChildren()){
psChild.setInt(1, I_DONT_KNOW_HOW_TO_GET_THIS_ID_WHICH_HASNT_BEEN_INSERTED_YET);
psChild.setString(2, parent.getName());
psChild.addBatch();
}
}
psParent.executeBatch();
psParent.close();
psChild.executeBatch();
psChild.close();
The actual data structure I am using is much more complicated than what I have above, but it illustrates the basic issue. How would I do the above using batches? The issue I am running into is the child objects cannot be inserted without first knowing the parent_id foreign key to use.
I've searched for answers elsewhere and I cannot find anything to resolve this. I read something about using a stored procedure (which I would like to avoid if possible). Efficiency is important here since I am dealing with potentially millions of records. I appreciate any insight anyone might have.
